"Martin Brown" wrote in message
...
On 27/08/2011 23:15, Howard Brazee wrote:
On Sat, 27 Aug 2011 14:41:06 -0700, wrote:

=AVERAGE(A2:Axxxxx) in A1

AVERAGE function ignores blank cells and/or text

What is in the "blank" cells that causes your average to be
incorrect?


=IF(ISNUMBER(AD3),F3-ROUND(AC3+0.5,0),"")

Is that deliberate? Testing AD3 for being a number and then using F3
and AC3 in the formula offers no protection against bad input data.

Or, at the bottom of my spreadsheet, nothing at all.

I have #VALUE! displayed in G2 where I entered =AVERAGE(G3:G122)

I see G52 also displays #VALUE! There's something bad in that row,
but there are lots of complex items there. That may be what I need
to replace (with space for now?)


If you do have zeros(not shown) then in A1 place this array formula.

=AVERAGE(IF(A2:Axxxx0,A2:Axxxx))

Array formulas are entered with CTRL + SHIFT + ENTER


{AVERAGE(IF(ISNUMBER(A2:Annn), A2:Annn,0))}

As an array formula should give you an average of the numerically
valid entries.

Regards,
Martin Brown


Don't you mean

{AVERAGE(IF(ISNUMBER(A2:Annn), A2:Annn,""))}

so the non-numeric cells are simply ignored?

(as array formula)

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)