Average of a column, excluding spaces?
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
|