Average only cells containing Numbers

  • Thread starter Thread starter Ken
  • Start date Start date
K

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
 
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.
 
=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
 
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
 
Back
Top