User profile for user: brassandzin
brassandzin Author
User level: Level 1
13 points

Average Function not working properly

I have a two sheet numbers doc to calculate the average of values placed in the second sheet. The second sheet consists of 31 (or Days of the month).

http://www.brassandzin.com/misc_images/Sheet2.tiff

In each cell, in column B, I enter a value. I want to reference the average of column B from each (day), in a cell on sheet 1. the result is a daily average that is then expressed in a graph.

http://www.brassandzin.com/misc_images/Sheet1.tiff

As you can see the only day that doesn't work is "Day 1." The formula is the same as the formula used for all the other "days." (You can see the formula in the TIF of sheet1) Any ideas?

Note: the "days" 29-31 show the warning sign because they have no values in the corresponding cells yet. The error message is also weird. "Numbers can't be divided by zero." Any number divided by zero is zero, so yes, they can be divided by zero. But there aren't any numbers in days 29-31 yet, and +in no formula do I divide by any number+, much less, zero! So what gives?

Message was edited by: brassandzin

Dual 2GB PowerPC G5, Mac OS X (10.5.5), 3.5 Gig Ram

Posted on Dec 28, 2008 8:39 PM

Reply
10 replies
User profile for user: KOENIG Yvan
KOENIG Yvan
User level: Level 8
41,819 points

Dec 29, 2008 3:39 AM in response to brassandzin

brassandzin wrote:
The error message is also weird. "Numbers can't be divided by zero." Any number divided by zero is zero, so yes, they can be divided by zero. But there aren't any numbers in days 29-31 yet, and +in no formula do I divide by any number+, much less, zero! So what gives?


It's your comment which is weird !
Divide zero by something is always zero but _nobody is allowed to divide by zero._

The message is perfectly correct, every attempt to divide by zero is an oddity.
It's _your duty_ to take care of the situation where the divider is zero.

Use a correct formula like:

=IF(COUNT(B2:B7)>0,AVERAGE(B2:B7),"")

and you will get rid of the perfectly logical error message.

Yvan KOENIG (from FRANCE lundi 29 décembre 2008 12:33:15)
Reply
User profile for user: jaxjason
jaxjason
User level: Level 5
5,063 points

Dec 29, 2008 5:54 AM in response to brassandzin

no spreadsheet will allow you to divide by zero. which you may not be doing directly, ** are doing indirectly because you are using the average function. Wich is the equivalent of performing a sum/count. Even if sum=0, if count=0, the number of items in your list, you will still get divide by zero errors.

You will not have this problem if you do one of two things.
1)Make sure you have zeros in every no-value cell (sum=0, count>0)
or
2)use an iserror statement inside an if statement. Like so:
=if(iserror(average(a1:a10)),0,average(a1:a10))


Jason

Message was edited by: jaxjason
Reply
User profile for user: brassandzin
brassandzin Author
User level: Level 1
13 points

Dec 29, 2008 10:57 AM in response to jaxjason

OK, yes, you both are right of course. But the problem is not dividing by zero, as I haven't asked to divide by zero in any of the formulas! The formula's are ALL the same

=AVERAGE(Day 1 :: B3:B12)

where "day 1" is the group of cells on the second sheet, and column B has all numeric values - OR NO VALUE.
As soon as I put a value into any of the other Day groups {=AVERAGE(Day 2 :: B3:B12) for instance} the error message goes away.
The only time the formula doesn't work is with the "Day 1" cells.
http://www.brassandzin.com/miscimages/sheet1day1.tiff

As you can see here, all of the other days averages return an answer, even if the column B only has a few values in it. AND the "Day 1" column actually HAS values in it! Frustrating!

http://www.brassandzin.com/miscimages/sheet2groups.tiff


Why is it only on the "Day 1" average that it returns the error when there are values actually in the specified columns?!?
I tried adding an extra column next to the problem cells and entering by hand, the formula. But the results are the same! And if I put the formula from the "Day 2" cell into the "Day 1" cell it works perfectly! Why is it only Day 1?

I don't know how to put images into these posts, so to download the whole spreadsheet (called BSL_Dec2008.numbers.zip) go to:
http://www.brassandzin.com/misc_images

Message was edited by: brassandzin
Reply
User profile for user: brassandzin
brassandzin Author
User level: Level 1
13 points

Dec 29, 2008 11:16 AM in response to jaxjason

Sorry forgot to put this in the last message.....


I tried jaxjasons tip =IF(ISERROR(AVERAGE(Day 1 :: B3:B12)),0,AVERAGE(Day 1 :: B3:B12))
and it returned a value of 0. No good, the actual average should be 101.25 (I did it manually)

I tried KOENIG Yvan's formula =IF(COUNT(Day 1 :: B3:B12)>0,AVERAGE(Day 1 :: B3:B12),"")
and it got rid of the error message, but left the cell blank. Also not right.
Reply
User profile for user: jaxjason
jaxjason
User level: Level 5
5,063 points

Dec 29, 2008 11:53 AM in response to brassandzin

I cannot look at your spreadsheet right now, but if day 1 has numbers that average out to 101.25. then there is somehting else in your list that is wrong and creating an error.

I was able to view the TIFF files in your folder. And I noticed that some of those cells are referencing other tables. It is possible that is where your problem resides.

Try adding a footer/calculation/summary row at the foot of that one table and perform an average on it.

I will be home late tonight, but will try to find the time to look at your spreadsheet and post if I find the source of the error.

Jason
Reply
User profile for user: KOENIG Yvan
KOENIG Yvan
User level: Level 8
41,819 points

Dec 29, 2008 12:42 PM in response to brassandzin

brassandzin wrote:


(a)

calculating an average is a division.

it does : sum(values) divided by (number of values).

(b)
I tried KOENIG Yvan's formula =IF(COUNT(Day 1 :: B3:B12)>0,AVERAGE(Day 1 :: B3:B12),"")
and it got rid of the error message, but left the cell blank. Also not right.


I apologize, but if there is no value in the range B3:B12 there is nothing to average so I choose to return an empty string. I feel that it is neater than returning a zero.

It would be interesting to explain us how the average of nothing may be 101.25

I have a possible explanation but I can't imagine that you really missed that.

Are you sure that the cells B3 thru B12 are containing numbers? It seems that they are containing strings 😉

Yvan KOENIG (from FRANCE lundi 29 décembre 2008 21:35:19)
Reply
User profile for user: brassandzin
brassandzin Author
User level: Level 1
13 points

Dec 29, 2008 12:58 PM in response to KOENIG Yvan

Thanks for all this, but yes, they contain numbers (66, 97, 52, and 190). There are also 6 cells with no value in them, but that is also the case with all of the other AVERAGE calculations that I'm performing. So in the range of {Day 1 :: B3:B12} there are 4 numbers and 6 cells empty. But it comes back with an error, and all of the other's do not.

To clarify:
I have on Sheet two 31 tables (representing days of the month). Each Table has 10 cells in column B {B3:B12} that may contain a number or no value. I want to average all of the values in that row in a single cell on Sheet one.

While all of the other "days" work just fine, the average for "Day 1" always returns the error. Actually they all return the error (Number's can't divide by zero) UNTIL I put a number into one of the cells, then everything works fine...... except for "Day 1."

As I can't figure out how to put images into the thread (maybe I'm daft?) you can see all these TIFF's or download the Numbers sheet here. It's a very small and simple file:
http://www.brassandzin.com/misc_images
Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Average Function not working properly

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up