Average only cells containing Numbers

[Ken]

Excel 2000 ... My range contains formulas ... Sometimes
the formula produces a value & sometimes it does not ...
This is fine

Issue ... I want to AVERAGE each Col & each Row in the
Range for only those cells that contain a Value within the
specific Col or Row Range.

Using AVERAGE ... Excel appears to be dividing by all
cells in the Col or Row range.

How do I correctly write this?

Thanks ... Kha

 

[Norman Harker]

Hi Ken!

Here's an extract from Help for AVERAGE:

"If an array or reference argument contains text, logical values, or
empty cells, those values are ignored; however, cells with the value
zero are included."

What are your formulas returning when they do not produce a value?
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[GerryK]

=AVERAGE(IF(A1:A100>0,A1:A100)) will exclude any 0s
=SUMPRODUCT(1-ISBLANK(A1:A100)) will tell you how many non-
blank cells there are!
HTH

 

[Ken]

Norm ... (Hi)

Ok ... I previously saw excerpt from Help Screen ... but
gave up on exactly what it was saying ... Only after
reading it again in your reply to my post (actually
reading it a coupe times) did I see it ... Yes, my formula
is producing a "0" value (note: I have "0" values turned
off).

Also, I can write formula as written in other reply if
this is best way ...

Thanks ... Kha