"Average" formula 2

[dkr1]

This formula assumes a "0" in blank cells and averages the entire column...I need to average cells with data only. What am I doing wrong?


=IF(COUNT(C8:C38)=0,0,AVERAGE(C8:C38))

 

[jebry]

Hi!

Check out the AverageA function in Excel help.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[dkr1]

Hi Jeff...

That will do it except in columns where I am ref. other cells and returning a "0".

How do I make:

=SUM(FEB!E4)

return blank instead of 0 if no value is found?

 

[mudstuffin]

Is this what you're after....

=IF(SUM(FEB!E4)=0,"",SUM(FEB!E4))

regards,



mudstuffin

 

[Zathras]

If you only have blanks, zeros and numbers then this formula should work for you. If you have any other non-empty cells in the range (with non-numbers) it won't:

  =SUM(C8:C38)/(COUNTIF(C8:C38,&quot;<>0&quot;)-COUNTBLANK(C8:C38))

 

[SkipVought]

Hi,
Use SUBTOTAL 1 for average - only includes numeric values

=SUBTOTAL(1,F6:F8)

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[Zathras]

I keep forgetting about that! A very useful tip -- definitely worth a star.

 

[dkr1]

Thanks mudstuffin! That did the trick...



Skip...cool tip! Thanks...another star to 'ya!



dave

 

[plantj]

As far as I can tell =average(RangeName) works fine with a mixture of zeros, non-zeros and blanks.