How to Average Only Cells Greater Than Zero

[flasher40]

Hi,

Below is a simplified replica of part of a spreadsheet I’m developing.

A B C D <-- Column
Average January February March
__________________________________________
| | | | |
1 | | $0| $0| $0|
|__________|__________|__________|__________|
^
R
o
w

I would like the value in the “Average” cell (A1) to cover only those months that have values greater than zero. So, for example, if the values for January and February are each 100, the average would be 100 divided by 2 instead of 100 divided by 3.

I know what formula to use to achieve that result if the month cells are empty. But in this case, the value in each month cell has been copied from a sum cell elsewhere on the worksheet. So, for example, the formula for the January month cell B1 is “=D20.” Therefore, the cell isn’t blank but instead reflects the sum value of zero from cell D20.

What formula would I use for the “Average” cell (A1) so that, of cells B1, C1, and D1, only those that have a value greater than zero will be averaged?

Thanks,
Bill

 

[SkipVought]

Hi,

Use the SUMIF and COUNTIF funtions.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Hi Bill,

You could use:
=AVERAGE(IF(A11>0,A11))
as an array formula, or:
=SUMIF(A11,">0")/COUNTIF(A11,">0")
as an ordianry formula.

Array formulae are confirmed with <Ctrl-Shift-Enter> instead of <Enter>

Cheers

[MS MVP - Word]

 

[Lilliabeth]

If you're using Excel 2007 you can use

=AVERAGEIF(A11,">0")


--Lilliabeth