Help with IF & Average Functions
 

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,D1:D1322))

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

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

One Way

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


--
Rowan
------------------------------------------------------------------------
Rowan's Profile: http://www.excelforum.com/member.php...o&userid=26535
View this thread: http://www.excelforum.com/showthread...hreadid=398000

"Cybertech" wrote:
....
=AVERAGE(IF(B1:B1322=3,D1:D1322))
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,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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 he
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" wrote
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,D1:D1322))

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

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

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. :)
:)


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" wrote
in message ...

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. :)
:)