how do I average cells

[Guest]

I need a formula to average cells only if there is a number in a cell.
Example in cell A1, A2,A3 A4 they all have a formulasand all 4 cells say 0.00
in cell A5 i have =average(A1:A4)

So if I put 100 in cell A1 then my answer in cell A5 says 25

I Would like it to say 100
only average if the number is higher that 0.00

Thanks

 

[Peo Sjoblom]

=SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")

or


=AVERAGE(IF(A1:A4>0,A1:A4))


the latter entered with ctrl + shift & enter


You might want to test for 0 in the cells because if all cells are zero it
will return a div error


=IF(COUNTIF(A1:A4,0)=4,0,rest of the formula





--


Regards,


Peo Sjoblom

 

[David Biddulph]

At one stage you say average only if there is a number in the cell, but
later you say only if the number is greater than zero.

The AVERAGE function will only take account of cells with numbers in, and
will ignore empty cells or those with text.
If you want to ignore cells where the number is <=0, then try
=AVERAGE(IF(A1:A4>0,A1:A4,"")) as an array formula (Control Shift Enter).

 

[Guest]

ok heres what I have.
in cell D6 I have formula of =AVERAGE(D710) which its at #div/0!
in cell D11 = AVERAGE(D1215) which its at 85
in cell D16 =AVERAGE(D17210) which its at 50

in cell D3 I just want the average of cell D6,D11,and D16
is this possible and how?

 

[Bob Phillips]

So what was wrong with Peo's suggestion?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)