Can anyone tell me whether there is a way to average a whole column of
numbers but ignoring blank cells or cells with errors in

e.g.

Column A

1
2
4
5
3
5
4
DIV0!
DIV0!
3
4
blank
2

Many thanks

John

Blanks are already ignored.

=AVERAGE(IF(ISNUMBER(A1:A100),A1:a100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JRD" wrote in message
...
Can anyone tell me whether there is a way to average a whole column of
numbers but ignoring blank cells or cells with errors in

e.g.

Column A

1
2
4
5
3
5
4
DIV0!
DIV0!
3
4
blank
2

Many thanks

John

Hi

One way would be the following Array entered formula
{=SUM(IF(ISNUMBER(A1:A13),A1:A13,0))/COUNT(A1:A13)}

Use Ctrl+Shift+Enter to commit or Edit the formula
Do not type the curly braces { } yourself, Excel will enter them for
you if you use Ctrl+Shift+Enter

--
Regards

Roger Govier


"JRD" wrote in message
...
Can anyone tell me whether there is a way to average a whole column of
numbers but ignoring blank cells or cells with errors in

e.g.

Column A

1
2
4
5
3
5
4
DIV0!
DIV0!
3
4
blank
2

Many thanks

John

Hi Bob

I tried that first of all, but had used ,0 as the False argument to the
If statement

{=AVERAGE(IF(ISNUMBER(A1:A13),A1:A13,0))}

and got a result of 2.538462 whereas the correct result (with the data
as posted) is 3.3

Omitting the ,0 (as you did) gives the correct result.

I am curious to know why it is that you can omit the false argument. I
have never thought of doing so before, but clearly it makes for a
shorter formula than my SUM()/COUNT() solution as posted.?

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Blanks are already ignored.

=AVERAGE(IF(ISNUMBER(A1:A100),A1:a100))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JRD" wrote in message
...
Can anyone tell me whether there is a way to average a whole column
of
numbers but ignoring blank cells or cells with errors in

e.g.

Column A

1
2
4
5
3
5
4
DIV0!
DIV0!
3
4
blank
2

Many thanks

John

Hi Roger,

Been out for a walk on this glorious day, so it wasn't that I was taking
time thinking of the reason.

The reason is that if you put 0 there, that counts as another element and
skews the average. If you put nothing, it generates a FALSE for all the
mismatches (just as =IF(A1="xyz",1) would do), and the FALSE values do not
get averaged, just as TEXT does not.

In your formula, you could use FALSE instead of 0 and it would have worked.

=SUM(IF(... would be fine with a 0 for the FALSE action, but not AVERAGE,
not MIN, not STDEV, etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

I tried that first of all, but had used ,0 as the False argument to the
If statement

{=AVERAGE(IF(ISNUMBER(A1:A13),A1:A13,0))}

and got a result of 2.538462 whereas the correct result (with the data
as posted) is 3.3

Omitting the ,0 (as you did) gives the correct result.

I am curious to know why it is that you can omit the false argument. I
have never thought of doing so before, but clearly it makes for a
shorter formula than my SUM()/COUNT() solution as posted.?

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Blanks are already ignored.

=AVERAGE(IF(ISNUMBER(A1:A100),A1:a100))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JRD" wrote in message
...
Can anyone tell me whether there is a way to average a whole column
of
numbers but ignoring blank cells or cells with errors in

e.g.

Column A

1
2
4
5
3
5
4
DIV0!
DIV0!
3
4
blank
2

Many thanks

John

If I may - when the false argument is excluded, IF returns FALSE for false
conditions and AVERAGE ignores logical values.


"Roger Govier" wrote:

Hi Bob

I tried that first of all, but had used ,0 as the False argument to the
If statement

{=AVERAGE(IF(ISNUMBER(A1:A13),A1:A13,0))}

and got a result of 2.538462 whereas the correct result (with the data
as posted) is 3.3

Omitting the ,0 (as you did) gives the correct result.

I am curious to know why it is that you can omit the false argument. I
have never thought of doing so before, but clearly it makes for a
shorter formula than my SUM()/COUNT() solution as posted.?

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Blanks are already ignored.

=AVERAGE(IF(ISNUMBER(A1:A100),A1:a100))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JRD" wrote in message
...
Can anyone tell me whether there is a way to average a whole column
of
numbers but ignoring blank cells or cells with errors in

e.g.

Column A

1
2
4
5
3
5
4
DIV0!
DIV0!
3
4
blank
2

Many thanks

John

Hi Bob

After posting, I went out for lunch ( and a nice drop of Old Speckled
Hen).
Whilst gently sipping, I had decided that my putting 0 for the false
argument, was what was giving the problem as these would be values and
whilst they would not raise the total, they would raise the divisor to
13 (from 10). I had meant to try on my return using "" (null) in place,
but your post has confirmed.

The next posting, also confirms to me that omitting the False part of
the argument, defaults to False.
As usual, I learn something new every day!!!


--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Hi Roger,

Been out for a walk on this glorious day, so it wasn't that I was
taking
time thinking of the reason.

The reason is that if you put 0 there, that counts as another element
and
skews the average. If you put nothing, it generates a FALSE for all
the
mismatches (just as =IF(A1="xyz",1) would do), and the FALSE values do
not
get averaged, just as TEXT does not.

In your formula, you could use FALSE instead of 0 and it would have
worked.

=SUM(IF(... would be fine with a 0 for the FALSE action, but not
AVERAGE,
not MIN, not STDEV, etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

I tried that first of all, but had used ,0 as the False argument to
the
If statement

{=AVERAGE(IF(ISNUMBER(A1:A13),A1:A13,0))}

and got a result of 2.538462 whereas the correct result (with the
data
as posted) is 3.3

Omitting the ,0 (as you did) gives the correct result.

I am curious to know why it is that you can omit the false argument.
I
have never thought of doing so before, but clearly it makes for a
shorter formula than my SUM()/COUNT() solution as posted.?

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Blanks are already ignored.

=AVERAGE(IF(ISNUMBER(A1:A100),A1:a100))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JRD" wrote in message
...
Can anyone tell me whether there is a way to average a whole
column
of
numbers but ignoring blank cells or cells with errors in

e.g.

Column A

1
2
4
5
3
5
4
DIV0!
DIV0!
3
4
blank
2

Many thanks

John

Hi

Thanks for that.
On reflection, I had come to the conclusion that, had I used Nulls ("")
rather than 0's in my IF clause, it would probably have worked.
I had not realised that omitting the False argument, automatically
returns FALSE for False.

Thank you for helping me learn something new today.


--
Regards

Roger Govier


"JMB" wrote in message
...
If I may - when the false argument is excluded, IF returns FALSE for
false
conditions and AVERAGE ignores logical values.


"Roger Govier" wrote:

Hi Bob

I tried that first of all, but had used ,0 as the False argument to
the
If statement

{=AVERAGE(IF(ISNUMBER(A1:A13),A1:A13,0))}

and got a result of 2.538462 whereas the correct result (with the
data
as posted) is 3.3

Omitting the ,0 (as you did) gives the correct result.

I am curious to know why it is that you can omit the false argument.
I
have never thought of doing so before, but clearly it makes for a
shorter formula than my SUM()/COUNT() solution as posted.?

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Blanks are already ignored.

=AVERAGE(IF(ISNUMBER(A1:A100),A1:a100))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JRD" wrote in message
...
Can anyone tell me whether there is a way to average a whole
column
of
numbers but ignoring blank cells or cells with errors in

e.g.

Column A

1
2
4
5
3
5
4
DIV0!
DIV0!
3
4
blank
2

Many thanks

John

I think you probably know what drink I had <g

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

After posting, I went out for lunch ( and a nice drop of Old Speckled
Hen).
Whilst gently sipping, I had decided that my putting 0 for the false
argument, was what was giving the problem as these would be values and
whilst they would not raise the total, they would raise the divisor to
13 (from 10). I had meant to try on my return using "" (null) in place,
but your post has confirmed.

The next posting, also confirms to me that omitting the False part of
the argument, defaults to False.
As usual, I learn something new every day!!!


--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Hi Roger,

Been out for a walk on this glorious day, so it wasn't that I was
taking
time thinking of the reason.

The reason is that if you put 0 there, that counts as another element
and
skews the average. If you put nothing, it generates a FALSE for all
the
mismatches (just as =IF(A1="xyz",1) would do), and the FALSE values do
not
get averaged, just as TEXT does not.

In your formula, you could use FALSE instead of 0 and it would have
worked.

=SUM(IF(... would be fine with a 0 for the FALSE action, but not
AVERAGE,
not MIN, not STDEV, etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

I tried that first of all, but had used ,0 as the False argument to
the
If statement

{=AVERAGE(IF(ISNUMBER(A1:A13),A1:A13,0))}

and got a result of 2.538462 whereas the correct result (with the
data
as posted) is 3.3

Omitting the ,0 (as you did) gives the correct result.

I am curious to know why it is that you can omit the false argument.
I
have never thought of doing so before, but clearly it makes for a
shorter formula than my SUM()/COUNT() solution as posted.?

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Blanks are already ignored.

=AVERAGE(IF(ISNUMBER(A1:A100),A1:a100))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JRD" wrote in message
...
Can anyone tell me whether there is a way to average a whole
column
of
numbers but ignoring blank cells or cells with errors in

e.g.

Column A

1
2
4
5
3
5
4
DIV0!
DIV0!
3
4
blank
2

Many thanks

John