6

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.

Improve this question
4
  • 1
    +1 for the "question and a prayer" comment, and a very clear question that spells out one of the many dangers of Excel. You may very well have ended up with wrong answers as you discovered. If it is important, go back and check your work. Commented Jan 14, 2014 at 15:40
  • 1
    +1 for the question. To partially answer your second question - once a formula has (at least once) been entered using CTRL+SHIFT+ENTER you can use a .HasArray property of Range object to check whether the formula is an array formula. You could design a small protective mechanism to for example display a message when you touch /click a cell with an array formula (CSE -ctrl-shift-enter). On the other hand, if you're entering a formula for the first time and forget to hit CSE you may want to think about your file structure and create a sub to check if an array formula should be expected Commented Jan 14, 2014 at 16:01
  • @mehow Thanks. I don't have the chops yet to even clearly understand, never mind implement what you've suggested but it's still very helpful to have a road map that may make more sense a few days down the line. Commented Jan 14, 2014 at 16:14
  • 2
    @Amatya If something is supposed to be an array formula but you simply hit ENTER and get a result(possibly wrong) you could implement a system that fires off from an event and evaluates the same formula as an array formula and if the results of both are different then show a caution message (all done via VBA). I hope it makes more sense now Commented Jan 14, 2014 at 16:19

1 Answer 1

Reset to default
5

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")
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

The only thing I had added was that when he entered =IF(A2:A5="EAST",B2:B5) in and it appeared to return 9800 was because it was returning B2:B5, and that if you enter that into a cell you get the array of values inside B2:B5, and the way excel displays this is by showing only the first value, even though they are all still there, that's is why the average is not 9800. Was also going to add that he can test this in the future by using F9 on his formula inside the formula bar to see what exactly the results are.
@user2140261 Oops, I actually forgot to mention that part. Thanks for mentioning it :)
....and if you don't have Excel 2007 and you want to avoid CSE you can use this version - =SUMIF(A2:A5,"East",B2:B5)/COUNTIF(A2:A5,"East")
There are several possiblities as to what =IF(A2:A5="EAST",B2:B5) might display, depending on which cell you you put it. For example for the data given if you enter in C4 it will show 6966 (the number in B4) but if you enter in C5 it will show FALSE because A5 <> "east". The same applies to the AVERAGE formula used without CSE - the results may vary depending on where the formula resides, even if the data doesn't change
@barryhoudini could you elaborate that in an answer? or maybe Jerry could add that to his answer?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.