Apple Numbers - average function

[comatory]

Hello
I am pretty inexperienced with iWork apps but thanks to my experience with Office suite I usually figure out things pretty quickly. I am trying to figure out how to average the sums in this table:

Header row represents time as in months. I want to put numbers under each month. The sum row (green) will simply add these numbers up.

So far everything works good, I highlighted each column and selected "Sum function" which adds up neatly in Sum row.

Now if I use "Average function" on the sum row it does not work as I want it to work. It does not ignore zero, see this example:

My "Average" cell has this formula:

=AVERAGE(B10:G10)

You can see that the Average row differs from what is on the calculator. I put the three numbers in calculator and divided them by three. Formula in Average row seems to constantly divide everything by six (there are six "Sum cells").

What I want to do basically is for the formula to ignore the zero because calculations are not correct. I know I could probably define Average for each column but I want it to be more flexible because I might be adding columns in the future. That is why I think averaging Sum row is easier.

Maybe I'm doing everything wrong, I've always been really bad at math so apologies.

 

[onekerato]

Maybe I'm doing everything wrong, I've always been really bad at math so apologies.

Yes, there is a conceptual error here.

The calculator is showing average sale per item in October 2013. This is usually irrelevant. Think about it - apples sold $1000, oranges sold $500, bananas sold $0, so the average sale per fruit was $500. Average per FRUIT? How's that useful to know?

The SUM you've calculated shows total sales per month. The AVERAGE formula you've written shows average sales per MONTH, which is very useful information. But, as you noted, some of the monthly sales are not available yet and shown as 0, so the AVERAGE formula goes wrong because it thinks you actually made $0 sales in those months.

Instead, write this formula in cell B11
=SUM(B10:G10)/COUNTIF(B10:G10,">0")

This calculates average, by excluding the entries which are zero.

This works in both MS Excel and Apple Numbers, BTW.

 

[mrichmon]

In cell B11 you should be using the formula: =AVERAGE(B2:B9)

This will average the sales within that quarter.

 

[comatory]

Yes, there is a conceptual error here.

The calculator is showing average sale per item in October 2013. This is usually irrelevant. Think about it - apples sold $1000, oranges sold $500, bananas sold $0, so the average sale per fruit was $500. Average per FRUIT? How's that useful to know?

The SUM you've calculated shows total sales per month. The AVERAGE formula you've written shows average sales per MONTH, which is very useful information. But, as you noted, some of the monthly sales are not available yet and shown as 0, so the AVERAGE formula goes wrong because it thinks you actually made $0 sales in those months.

Instead, write this formula in cell B11
=SUM(B10:G10)/COUNTIF(B10:G10,">0")

This calculates average, by excluding the entries which are zero.

This works in both MS Excel and Apple Numbers, BTW.

Yes you were right, I was indeed looking for something else. I know it might look weird to you but this is exactly what I am looking for - I want to have average number calculated as I go. Anyway I want to thank you because it works for me. You only have small syntax error there, I had to put semicolon in there like this:

=SUM(B10:G10)/COUNTIF(B10:G10;">0")

 

[onekerato]

You only have small syntax error there, I had to put semicolon in there like this:

That's weird... the formula works for me with comma, but throws an error with semicolon! I'm using Numbers.app purchased from the Mac App Store.

See also the documentation for COUNTIF() function:
http://support.apple.com/kb/PH844?viewlocale=en_US