Average of a column, excluding spaces?

[Howard Brazee]

How do I have a header on a column which is the average value of that
column's valid numbers? Blank cells should not be averaged in.

 

[Gord]

=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 you do have zeros(not shown) then in A1 place this array formula.

=AVERAGE(IF(A2:Axxxx>0,A2:Axxxx))

Array formulas are entered with CTRL + SHIFT + ENTER


Gord Dibben Microsoft Excel MVP

 

[Howard Brazee]

=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),"")

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:Axxxx>0,A2:Axxxx))

Array formulas are entered with CTRL + SHIFT + ENTER

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

 

[Andrew]

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

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?)




--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

It appears that you are tying to get the average of G3:G122, and that
value will reside in G2. So G2 should have the formula:
=AVERAGE(G3:G122)
But you have a #VALUE error in G52. This error will cause the AVERAGE
function to also return a #VALUE error. Correct the error in G52 and,
any other errors between G3 and G122 and the AVERAGE function will
work.

I hope this helps.

 

[Martin Brown]

=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?)

{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

 

[Clif McIrvin]

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.

{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)