AVERAGE function

J

JRD

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
 
B

Bob Phillips

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

Roger Govier

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
 
R

Roger Govier

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.?
 
B

Bob Phillips

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

JMB

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

Roger Govier

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!!!
 
R

Roger Govier

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

Bob Phillips

I think you probably know what drink I had <g>

--
HTH

Bob Phillips

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