Difference between results of array formula and non-array, with IF(ISNUMBER)

[THOMAS CONLON]

I have the following array formula:
={(AVERAGE(ABS(IF(ISNUMBER(T119:T124),T119:T124))))}
and the following non-array formula:
=(AVERAGE(ABS(IF(ISNUMBER(T119:T124),T119:T124))))
In the cells T119:T124 are the following:
-1,-1,-1,blank,blank,blank
The result of the array formula is 0.5.
The result of the non-array formula is 1.0

I believe 1 is the correct result. The array formula appears to be counting
the blank cells as 0 when computing the average.

Can anybody explain this difference in the result? I would like to
understand what is going on here.

Thank you.
Tom

 

[Biff]

Hi!

The non-array formula is only processing the first cell in the range
(depending on where the formula is entered in relation to the range).So, it
may appear to be correct just by "dumb luck".

Try this: change T119 to 10 and put the non-array formula in a cell in the
same row, say, U119. Now the result will be 10.

Use this array formula:

=AVERAGE(IF(ISNUMBER(T119:T124),ABS(T119:T124)))

Returns 1.

Biff

 

[THOMAS CONLON]

Thanks much. You are right that the non-array formula worked by "dumb luck"
and that the formula you suggested works correctly.

I was wondering what the explanation is (even tho it works for me now,
sometimes it nice to know the "lesson learned" so it can be more effectively
applied in the future).

The two array formulas that return different results are:
={(AVERAGE(ABS(IF(ISNUMBER(T119:T124),T119:T124))))}
={AVERAGE(IF(ISNUMBER(T119:T124),ABS(T119:T124)))}

Specifically the first will count a blank value in the range as "0" for
computing the average and the second does not (which seems to me is the
'correct' treatment of a blank value; at least it is consistant with how the
AVERAGE function usually works). My disectoin of the two formulas make it
seem to me like ISNUMBER is not operating consistantly in the two forumulas,
as regards to what it returns to its caller in the case of a blank.

Any comments or further explanation?

Thanks,
tom

 

[Biff]

The difference is the location of the ABS function.

ABS will evalute empty cells as 0 and logical values, TRUE, FALSE, as 1 and
0 respectively. Not surprising that Excel Help doesn't mention this!

Using this syntax and applying it to your data sample:

=AVERAGE(ABS(IF(ISNUMBER(T119:T124),T119:T124)))

This is what you get:

=AVERAGE(ABS(IF(ISNUMBER({True;True;True;False;False;False}),T119:T124)))
=AVERAGE(ABS(IF({True;True;True;False;False;False},{-1;-1;-1;False;False;False})))
=AVERAGE(ABS({-1;-1;-1;False;False;False}))
=AVERAGE({1;1;1;0;0;0})
= 0.5

Using the other formula:

=AVERAGE(IF(ISNUMBER(T119:T124),ABS(T119:T124)))

=AVERAGE(IF(ISNUMBER({True;True;True;False;False;False}),ABS(T119:T124)))
=AVERAGE(IF({True;True;True;False;False;False},ABS(T119:T124)))
=AVERAGE(IF({True;True;True;False;False;False},{1;1;1;0;0;0}))
=AVERAGE{1;1;1;False;False;False})
= 1

Average will ignore empty cells, TEXT and logicals.

Biff