Averageif Calcs

[Help]

I'm using windows XP (older version) which does not recognize the averageif
formula. I need a formula that calcs the average of four different data
points some of which contain zeros. Example:

A B C D avg
1 5 0 10 15 10

 

[Pete_UK]

Use this array* formula:

=AVERAGE(IF(A11<>0,A11))

*An array formula has to be committed using the key combination of
Ctrl-Shift-Enter (CSE) instead of the usual Enter. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
amend the formula you will need to use CSE again.

Hope this helps.

Pete

 

[Don Guillett]

Use SUMIF/COUNTIF

 

[Don Guillett]

Or sumproduct/countif

 

[Eduardo]

Hi,

=SUM(A11)/COUNTIF(A11,">0")

if this helps please click yes, thanks

 

[Help]

The formula still gives me an error
=(Percentages!D24+Percentages!J24+Percentages!P24)/countif(Percentages!D24,Percentages!J24,Percentages!P24,">0")

 

[Bernie Deitrick]

Countif requires a contiguous range.

=(Percentages!D24+Percentages!J24+Percentages!P24)/SUM(Percentages!D24>0,Percentages!J24>0,Percentages!P24>0)

Or, if there are only strings or blanks between the cells with the numbers:

=SUM(Percentages!D2424)/COUNTIF(Percentages!D2424,">0")

HTH,
Bernie
MS Excel MVP