Help with IF & Average Functions

[Cybertech]

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D11322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom

 

[Rowan]

One Way

=SUMIF(B:B,3,D)/COUNTIF(B:B,3)

 

[Max]

:
....

=AVERAGE(IF(B1:B1322=3,D11322))
All it returns is the average of all the numbers in Column "D".
What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D11322))}

(Do not type the curly braces in yourself !)

 

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


in message
I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D11322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom

 

[Cybertech]

Thanks!!!

To Max and kk for explaining my problem and showing how to enter an
array.

To Rowan for showing me another way to arrive at the correct answer.

 

[Max]

You're welcome, Cybertech !