Apple Numbers - average function

Discussion in 'Mac Apps and Mac App Store' started by comatory, Oct 10, 2013.

  1. comatory macrumors 6502a


    Apr 10, 2012
    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:
    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.
  2. onekerato macrumors regular

    Jun 6, 2011
    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

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

    This works in both MS Excel and Apple Numbers, BTW.
  3. mrichmon macrumors 6502a

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

    This will average the sales within that quarter.
  4. comatory thread starter macrumors 6502a


    Apr 10, 2012
    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:
  5. onekerato macrumors regular

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

    See also the documentation for COUNTIF() function:

Share This Page