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