Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
"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 -- |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. :) :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
plotted Average | Charts and Charting in Excel | |||
What is this kind of average called? | Excel Worksheet Functions | |||
AVERAGE and STDEV functions with logic | Excel Worksheet Functions | |||
MIN, MAX, AVERAGE functions | Excel Worksheet Functions |