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