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.

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

Array formulas are entered with CTRL + SHIFT + ENTER


Gord Dibben Microsoft Excel MVP

On Sat, 27 Aug 2011 15:08:12 -0600, Howard Brazee
wrote:

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.

On Sat, 27 Aug 2011 14:41:06 -0700, Gord 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),"")

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

--
"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

On Aug 27, 4:15*pm, Howard Brazee wrote:
On Sat, 27 Aug 2011 14:41:06 -0700, Gord 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),"")

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

--
"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.

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

"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 :-)