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