Solved AVERAGE function evaluates en zero

Microsoft Excel 2013 32/64-bit - license...
March 22, 2017 at 10:53:14
Specs: Windows 64
Excel 2013, the AVERAGE function evaluates in ZERO with all the values different than zero. The funny thing is that if I select all the cells I want to evaluate, in the Excel bottom info bar, I can see the SUM and AVERAGE but the function in the cell, evaluates in zero. , solved it with AVERAGEVISIBLE, but it recalculates every time, sometimes results in "DIV0" error but with a refresh editing the cell and just hitting enter, does it.

message edited by erolmacc


See More: AVERAGE function evaluates en zero

Report •

✔ Best Answer
March 28, 2017 at 09:35:58
No problem. The Circular Reference error popped up as soon as I tried to open the file, so I figured I might as well go look in that direction.

The following was stolen without permission from:

https://support.office.com/en-us/ar...

Learn about the circular reference warning message

The first time Excel finds a circular reference, it displays a warning message. Click OK or close the message window. If you are on Excel for Windows, click Help to go to this help topic.

When you close the message, Excel displays either a zero or the last calculated value in the cell. And now you're probably saying, "Hang on, a last calculated value?" Yes. In some cases, a formula can run successfully before it tries to calculate itself. For example, a formula that uses the IF function may work until a user enters an argument (a piece of data the formula needs to run properly) that causes the formula to calculate itself. When that happens, Excel retains the value from the last successful calculation.

If you suspect you have a circular reference in a cell that isn't showing a zero, try this:

- Click the formula in the formula bar, and then press Enter.

Important: In many cases, if you create additional formulas that contain circular references, Excel won't display the warning message again. The following list shows some, but not all, the scenarios in which the warning message will appear:

- You create the first instance of a circular reference in any open workbook

- You remove all circular references in all open workbooks, and then create a new circular reference

- You close all workbooks, create a new workbook, and then enter a formula that contains a circular reference

- You open a workbook that contains a circular reference

- While no other workbooks are open, you open a workbook and then create a circular reference

message edited by DerbyDad03



#1
March 23, 2017 at 20:25:04
Not quite sure about your question, but when using zeros in an average, the easiest way to ignore them is with the AVERAGEIF() function, something like:

=AVERAGEIF(A1:A6,"<>0")

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
March 24, 2017 at 12:34:19
Thanks but
I do not have zeros on the numbers
Example, I use:

AVERAGE(2,3,4)=0 is what I'm getting as a result when clearly de average is 3, no 0
I want to know, why Excel is evaluating the formulas with final zero as a result.


Report •

#3
March 24, 2017 at 14:46:53
If your getting a zero, then I have no idea why.
Even if all the numbers were TEXT you should have received a DIV/0 error
If one or more were TEXT it would still evaluate to something other than zero.

Is this data being imported from anther program?
Might be that Excel is not properly recognizing the numbers as numeric.

Have you tried any of the other similar functions, AVERAGEIF(), MEDIAN(), MODE()
and see what they return?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 24, 2017 at 15:03:19
I've tried with SUM, and the same, haven't tried other functions, I've got stuck on this.
The one that worked in a strange way was AVERAGEVISIBLE.
Later I'll try other functions and post the result.
Thanks

Report •

#5
March 24, 2017 at 15:25:16
When you say you tried AVERAGE(2,3,4) is that exactly what you tried or were the values in cells, e.g AVERAGE(A1,A2,A3)?

If the values were in cells, try this:

=AVERAGE(A1*1,A2*1,A3*1)

If that formula returns the correct average, then Excel is not recognizing the values as numbers until you force them to be numbers by performing a mathematical operation on them.

Another question: does this happen in all workbooks or just one? Have you tried the AVERAGE function in a brand new, clean workbook?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 27, 2017 at 10:19:00
Interesting results:.
1) It is happening in a "new workbook" as well.
2) Multiplying the cells A1*1 do not worked out.
3) functions AVERAGE, MEDIAN, MODE, SUM, also results in a zero.
4) the numbers I am trying to AVERAGE, are the result of arithmetic in cells D3/I3, D4/I4, and so on to row 9 (I'm getting proportions % here)
Then I want to average those proportions, and is when I get ZERO as a result, and also any other functions evaluates in zero.
BUT, when I copy-paste values of the division, then the AVERAGE and other functions, it worked.
**Note: I don't have any original or any calculated 0 value; all real positive numbers different than zero.

message edited by erolmacc


Report •

#7
March 27, 2017 at 10:52:27
re: "4) the numbers I am trying to AVERAGE, are the result of arithmetic in cells D3/I3, D4/I4, and so on to row 9 (I'm getting proportions % here)"

Have you checked the formatting of the cells that contain the functions?

If I have the following data and B1:B4 are formatted to show a single digit with no decimal places, I will get a 0 for AVERAGE, MEDIAN, MODE and SUM because the results are all less than .5:

       A                B
1     10%        =AVERAGE(A1:A4)     
2     20%        =MODE(A1:A4)
3     10%        etc.
4      5%

Once I "expand" the decimal places or change the formatting to %, I start seeing the real results.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
March 27, 2017 at 11:00:52
Actually, the cells are formatted as "General" so a lot of decimals are displayed.
The funny thing is that I can still view the average of those number if I select all the cells, and take a look at the bottom bar, and there I can observe the AVERAGE, COUNT, and SUM.
But the problem remains using functions in the cells.
It is driving me crazy because of course, I'm not able to continue with my calculations.

Report •

#9
March 27, 2017 at 11:44:46
Well, without your actual workbook to play with, I can't recreate your issue. If you want to post an example "failing" workbook at zippyshare.com, and then post the link back here, I can take a look at and see if it does the same thing for me.

If it shows zeros for you and not for me, then there may be something funky with your Excel application itself. Letting someone else open a "failing" workbook on a different system would at least point us somewhere.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
March 27, 2017 at 14:13:25
Thanks
I place 2 links

Link 1: The Excel file: http://www7.zippyshare.com/v/uZal0Y...

Link 2: an image of how it looks in my PC http://www89.zippyshare.com/v/QUd3I...


Report •

#11
March 27, 2017 at 15:57:37
Try removing the Circular Reference in I9.

That solved the problem for me.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#12
March 28, 2017 at 08:59:30
OMG
How can I miss that little error?
Thanks a lot, the issue is solved.

Report •

#13
March 28, 2017 at 09:35:58
✔ Best Answer
No problem. The Circular Reference error popped up as soon as I tried to open the file, so I figured I might as well go look in that direction.

The following was stolen without permission from:

https://support.office.com/en-us/ar...

Learn about the circular reference warning message

The first time Excel finds a circular reference, it displays a warning message. Click OK or close the message window. If you are on Excel for Windows, click Help to go to this help topic.

When you close the message, Excel displays either a zero or the last calculated value in the cell. And now you're probably saying, "Hang on, a last calculated value?" Yes. In some cases, a formula can run successfully before it tries to calculate itself. For example, a formula that uses the IF function may work until a user enters an argument (a piece of data the formula needs to run properly) that causes the formula to calculate itself. When that happens, Excel retains the value from the last successful calculation.

If you suspect you have a circular reference in a cell that isn't showing a zero, try this:

- Click the formula in the formula bar, and then press Enter.

Important: In many cases, if you create additional formulas that contain circular references, Excel won't display the warning message again. The following list shows some, but not all, the scenarios in which the warning message will appear:

- You create the first instance of a circular reference in any open workbook

- You remove all circular references in all open workbooks, and then create a new circular reference

- You close all workbooks, create a new workbook, and then enter a formula that contains a circular reference

- You open a workbook that contains a circular reference

- While no other workbooks are open, you open a workbook and then create a circular reference

message edited by DerbyDad03


Report •

#14
March 28, 2017 at 11:13:28
Really useful, I now I know why I didn't get notice of the circular reference.
Again, thanks a lot!

Report •

Ask Question