Can I average cells only if they have value greater than 0?

Question

Level 1

8 points

Can I average cells only if they have value greater than 0?

Help, I cannot figure out how to express a formula to get the average of a bunch of cells but count them only if they have a value greater than 0.

Context: I have several rows that are auto adding values corresponding to data for each day of the week. I have them collecting to a monthly total, and I want to get a monthly average. So when I have only two months of data, I want the average to sum the two cells and divide by 2, but when I have 4 months, want it to sum and divide by 4.

Is this making any sense? If you can help, I'd be very grateful.

PowerBook, Mac OS X (10.5.2)

Posted on Mar 31, 2008 10:00 PM

Reply

Answer 1

Top-ranking reply

KOENIG Yvan

Level 8

41,819 points

Apr 1, 2008 2:22 AM in response to senatorw

Hello

Assuming that your values are in column C,
in the footer row type:

=SUMIF(C,">0")/COUNTIF(C,">0")

Yvan KOENIG (from FRANCE mardi 1 avril 2008 11:22:40)

Reply

Answer 2

Apr 3, 2008 12:02 PM in response to senatorw

senatorw wrote:
Not sure what the footer row is...

senatorw,

I think Yvan meant to say: See Help: "Using a Table Header Row or Column" in Numbers.

Table Header Rows, Header Columns and Footer Rows have special properties. These are the rows and columns that are shaded in all the Table formats except Plain. It's best not to use these special rows and columns before you know how they work, or you may have unexpected results.

Regards,

Jerry

Reply

Answer 3

senatorw Author

Level 1

8 points

Apr 2, 2008 8:17 PM in response to KOENIG Yvan

Fantastic. Thanks. Not sure what the footer row is, but I will keep playing. Thank you so much.

Reply

Answer 4

KOENIG Yvan

Level 8

41,819 points

Apr 3, 2008 1:24 AM in response to senatorw

Hello

Search for:

*Using Headers and Footers*

in the Pages's Help.

Yvan KOENIG (from FRANCE jeudi 3 avril 2008 10:24:26)

Reply

Answer 5

WWJD

Level 6

15,830 points

Apr 3, 2008 3:22 PM in response to senatorw

You have a "dangerous" or "high maintenance" equation especially if the number of items change. What does it matter if you sum all of the values? 500+0 is still 500 for example.

Regards,

Reply

Answer 6

senatorw Author

Level 1

8 points

Apr 2, 2008 8:47 PM in response to senatorw

With your help, I figured out a work around. Since I have 12 column entries to sum and divide, I couldn't figure out how to stack multiple cells to add. So, I couldn't do something like, SUMIF(AR9,AR15,AR21,AR27,">0")/COUNTIF(AR9,AR15,AR21,AR27,">0")

Instead, what I did was in a new column, use the =COUNTIF(AR9,">0") and repeated for each of the other 11 entries in that column. Then I summed up the column to get the denominator for my average equation.

I then simply inserted the following command in the footer cell, =SUM(AR9 AR15+AR21AR27)/AU64 (the sum of the count column)

This works for me. Thanks for helping me get there.

Reply