only average cells w/numbers?

[metab65]

I have a column (L) that my data begins in Row 9 and ends in Row 55.
Need to have average of the numbers in those cells (L9-L55).

However, some cells contain #VALUE, because information for the row ha
not been entered.

In L56, I'd like to have the average of only those cells L9-L55 tha
contain a number.

Thank you.
MAG

 

[Frank Kabel]

Hi
one way:
=SUMPRODUCT(--(ISNUMBER(L9:L55)),L9:L55)/SUMPRODUCT(--(ISNUMBER(L9:L55)
))

 

[gaftalik]

Hi MAGS!

In your column L try to return the values to 0 instead of #value an
then in your cell L56 use this formula :
=SUM(L9:L55)/COUNTIF(L9:L55,">0")

Hope it will help you !

Gaftali

 

[Dave Peterson]

One more way:

=AVERAGE(IF(ISNUMBER(L9:l55),l9:l55))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

 

[metab65]

Dave,
I've been struggling w/this problem for 2 weeks now and yours is th
only formula that worked!
You are my hero!!!

MAG