Excel Incorrect Array Formula usage explanation

Question

This is my data

Region    Value
East       9800
East       5592
East       6966
West       5418

When I do

=AVERAGE(IF(A2:A5="East",B2:B5)) CTRL+SHIFT+ENTER

I get

7452.667

Which is the right answer. But when I do

    =AVERAGE(IF(A2:A5="East",B2:B5))  ENTER

I don't get an error. I get

6944

If I just typed

 =IF(A2:A5="East",B2:B5)   ENTER

I get

 9800

So 6944 is not the average of 9800. What is Excel doing here?

When I do

=IF(A2:A5="West",B2:B5)  ENTER

I get

 FALSE

So it looked like IF is reading the first row.. but then something weird happens to Average(If())

I have one/two question(s) and one prayer:

Questions: What is excel doing to arrive at 6944? Why did I not get an error when I did ENTER instead of CTRL+SHIFT+ENTER and is there any way to remind excel to tell me to do the right thing when dealing with Arrays?

Prayer: I can only hope that I have not produced a lot of garbage in the last few weeks when I have neglected to to CTRL-SHIFT-ENTER and done ENTER by mistake and not gotten errors and ran with what I got.

Answer 1

6944 is the average of all the values. You'll get it using =SUM(B2:B5)/4 as well.

When you don't use CSE, IF will evaluate as follows:

=IF(A2="East",B2:B5)

When you have an array in a non-array formula in excel, you will get only one term (or element) of the array. If, for example you put the above formula (with no AVERAGE), IF evaluates the cell corresponding to the row the formula was inserted in. The actual 'result' formula would be the following if you put it in the 2nd row of excel (in any of the cells C2, D2, E2, etc):

=IF(A2="East", B2)

What will happen if you put it in cell C3 is:

=IF(A3="East", B3)

And if you put it in C6... A2:A5 will return #VALUE!, since the corresponding row of 6 in A2:A5 doesn't exist!. In C5, of course, you'd get =IF(A5="East", B5) which would return FALSE with your sample values.

Following from the initial formula, since A2 is indeed East, you'll get the result as AVERAGE(B2:B5).

Same with the other function:

=IF(A2="West",B2:B5)

Returns FALSE, and average of FALSE is 0.

You might use the function AVERAGEIF which doesn't need to be array entered to work, but that's assuming that you have Excel 2007 or later versions:

=AVERAGEIF(A2:A5,"East",B2:B5)

If you don't want to use an array function and is stuck with an excel version before 2007, for example, Excel 2003, you can try:

=SUMIF(A2:A5,"East",B2:B5)/COUNTIF(A2:A5,"East")