| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
IF/THEN Average Problem
Hello, I am looking for a formula to do the following: If any cells in Column C = B2, Then average the corresponding cells in Column J... I was thinking =SUMPRODUCT((Data!$C$2:$C$181=B$3)*(AVERAGE(Data!J 1:J180)))... But it just gives me an inaccurate total. Any help would be appreciated! Thanks! -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#2
|
|||
|
|||
|
IF/THEN Average Problem
=AVERAGE(IF(C1:C1000=B2,J1:J1000)
which is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "raspywench" wrote in message ... Hello, I am looking for a formula to do the following: If any cells in Column C = B2, Then average the corresponding cells in Column J... I was thinking =SUMPRODUCT((Data!$C$2:$C$181=B$3)*(AVERAGE(Data!J 1:J180)))... But it just gives me an inaccurate total. Any help would be appreciated! Thanks! -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#3
|
|||
|
|||
|
IF/THEN Average Problem
Hi!
Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =AVERAGE(IF(Data!$C$2:$C$181=B$whatever,Data!J1:J1 80)) Your explanation states: "If any cells in Column C = B2" But your formula uses B3. You also have a different range configuration of Data!J1:J180. As long as both arrays are the EXACT same size, that won't matter. Biff "raspywench" wrote in message ... Hello, I am looking for a formula to do the following: If any cells in Column C = B2, Then average the corresponding cells in Column J... I was thinking =SUMPRODUCT((Data!$C$2:$C$181=B$3)*(AVERAGE(Data!J 1:J180)))... But it just gives me an inaccurate total. Any help would be appreciated! Thanks! -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#4
|
|||
|
|||
|
IF/THEN Average Problem
THANKS! :) So to take it a step further, using the same scenario: If any cells in Column C = B2, Then average the corresponding cells in Column J, but only if there is not an Y in Column M How could that be added into =AVERAGE(IF(Data!$C$2:$C$181=B$3,Data!J1:J1 80))? Thank you! -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#5
|
|||
|
|||
|
IF/THEN Average Problem
=AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$M$2:$M$1 81="Y"),Data!J1:J1 80))?
-- HTH RP (remove nothere from the email address if mailing direct) "raspywench" wrote in message ... THANKS! :) So to take it a step further, using the same scenario: If any cells in Column C = B2, Then average the corresponding cells in Column J, but only if there is not an Y in Column M How could that be added into =AVERAGE(IF(Data!$C$2:$C$181=B$3,Data!J1:J1 80))? Thank you! -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#6
|
|||
|
|||
|
IF/THEN Average Problem
I'm sure Bob meant:
=AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$M$2:$M$1 81<"Y"),Data!J1:J1 80)) Biff "Bob Phillips" wrote in message ... =AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$M$2:$M$1 81="Y"),Data!J1:J1 80))? -- HTH RP (remove nothere from the email address if mailing direct) "raspywench" wrote in message ... THANKS! :) So to take it a step further, using the same scenario: If any cells in Column C = B2, Then average the corresponding cells in Column J, but only if there is not an Y in Column M How could that be added into =AVERAGE(IF(Data!$C$2:$C$181=B$3,Data!J1:J1 80))? Thank you! -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#7
|
|||
|
|||
|
IF/THEN Average Problem
Don't think so, OP said ... not an Y in Column M
-- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... I'm sure Bob meant: =AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$M$2:$M$1 81<"Y"),Data!J1:J1 80)) Biff "Bob Phillips" wrote in message ... =AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$M$2:$M$1 81="Y"),Data!J1:J1 80))? -- HTH RP (remove nothere from the email address if mailing direct) "raspywench" wrote in message ... THANKS! :) So to take it a step further, using the same scenario: If any cells in Column C = B2, Then average the corresponding cells in Column J, but only if there is not an Y in Column M How could that be added into =AVERAGE(IF(Data!$C$2:$C$181=B$3,Data!J1:J1 80))? Thank you! -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#8
|
|||
|
|||
|
IF/THEN Average Problem
Don't think so, OP said ... not an Y in Column M
Data!$M$2:$M$181<"Y" What am I not understanding here? Biff "Bob Phillips" wrote in message ... Don't think so, OP said ... not an Y in Column M -- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... I'm sure Bob meant: =AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$M$2:$M$1 81<"Y"),Data!J1:J1 80)) Biff "Bob Phillips" wrote in message ... =AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$M$2:$M$1 81="Y"),Data!J1:J1 80))? -- HTH RP (remove nothere from the email address if mailing direct) "raspywench" wrote in message ... THANKS! :) So to take it a step further, using the same scenario: If any cells in Column C = B2, Then average the corresponding cells in Column J, but only if there is not an Y in Column M How could that be added into =AVERAGE(IF(Data!$C$2:$C$181=B$3,Data!J1:J1 80))? Thank you! -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#9
|
|||
|
|||
|
IF/THEN Average Problem
Thanks Biff and Bob! But both of them seem to calculate the inaccurately or give me an error. Any other ideas about this? Thanks! -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#10
|
|||
|
|||
|
IF/THEN Average Problem
Hi!
Hmmm......... I see that you're posting from ExcelForum ExcelForum allows attachments. Can you post your file THERE so I can take a look? Biff "raspywench" wrote in message ... Thanks Biff and Bob! But both of them seem to calculate the inaccurately or give me an error. Any other ideas about this? Thanks! -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#11
|
|||
|
|||
|
IF/THEN Average Problem
Hi! Here it is! I have highlighted where I am trying to place the formula. Thanks! +-------------------------------------------------------------------+ |Filename: Model.zip | |Download: http://www.excelforum.com/attachment.php?postid=4033 | +-------------------------------------------------------------------+ -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#12
|
|||
|
|||
|
IF/THEN Average Problem
Your formula was
=AVERAGE(IF(Data!$C$2:$C$18=B$3,Data!$N$2:$N$18<" X"),Data!$J$2:$J$18)) it should be =AVERAGE(IF((Data!$C$2:$C$18=B$3)*(Data!$N$2:$N$18 <"X"),Data!$J$2:$J$18)) -- HTH RP (remove nothere from the email address if mailing direct) "raspywench" wrote in message ... Hi! Here it is! I have highlighted where I am trying to place the formula. Thanks! +-------------------------------------------------------------------+ |Filename: Model.zip | |Download: http://www.excelforum.com/attachment.php?postid=4033 | +-------------------------------------------------------------------+ -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#13
|
|||
|
|||
|
IF/THEN Average Problem
Of if he wants to use it that way he needs to add another IF
=AVERAGE(IF(Data!$C$2:$C$18=B$3,IF(Data!$N$2:$N$18 <"X",Data!$J$2:$J$18))) -- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... Your formula was =AVERAGE(IF(Data!$C$2:$C$18=B$3,Data!$N$2:$N$18<" X"),Data!$J$2:$J$18)) it should be =AVERAGE(IF((Data!$C$2:$C$18=B$3)*(Data!$N$2:$N$18 <"X"),Data!$J$2:$J$18)) -- HTH RP (remove nothere from the email address if mailing direct) "raspywench" wrote in message ... Hi! Here it is! I have highlighted where I am trying to place the formula. Thanks! +-------------------------------------------------------------------+ |Filename: Model.zip | |Download: http://www.excelforum.com/attachment.php?postid=4033 | +-------------------------------------------------------------------+ -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#14
|
|||
|
|||
|
IF/THEN Average Problem
Hi!
OK, just some missing parenths....... =AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$N$2:$N$1 81<"X"),Data!$J$2:$J$181)) Returns 8.14 Biff "raspywench" wrote in message ... Hi! Here it is! I have highlighted where I am trying to place the formula. Thanks! +-------------------------------------------------------------------+ |Filename: Model.zip | |Download: http://www.excelforum.com/attachment.php?postid=4033 | +-------------------------------------------------------------------+ -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#15
|
|||
|
|||
|
IF/THEN Average Problem
On a side note:
You're using array formulas: {=SUM(IF(Data!$C$2:$C$181=B$3,Data!$K$2:$K$181))} It's not necessary to use arrays for that. Try this instead: =SUMIF(Data!$C$2:$C$181,B$3,Data!$K$2:$K$181) Biff "Biff" wrote in message ... Hi! OK, just some missing parenths....... =AVERAGE(IF((Data!$C$2:$C$181=B$3)*(Data!$N$2:$N$1 81<"X"),Data!$J$2:$J$181)) Returns 8.14 Biff "raspywench" wrote in message ... Hi! Here it is! I have highlighted where I am trying to place the formula. Thanks! +-------------------------------------------------------------------+ |Filename: Model.zip | |Download: http://www.excelforum.com/attachment.php?postid=4033 | +-------------------------------------------------------------------+ -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#16
|
|||
|
|||
|
IF/THEN Average Problem
Got it! Thank you all very much! :) -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
|
#17
|
|||
|
|||
|
IF/THEN Average Problem
He doesn't want to, he mis-transcribed what he was given.
-- HTH RP (remove nothere from the email address if mailing direct) "Peo Sjoblom" wrote in message ... Of if he wants to use it that way he needs to add another IF =AVERAGE(IF(Data!$C$2:$C$18=B$3,IF(Data!$N$2:$N$18 <"X",Data!$J$2:$J$18))) -- Regards, Peo Sjoblom "Bob Phillips" wrote in message ... Your formula was =AVERAGE(IF(Data!$C$2:$C$18=B$3,Data!$N$2:$N$18<" X"),Data!$J$2:$J$18)) it should be =AVERAGE(IF((Data!$C$2:$C$18=B$3)*(Data!$N$2:$N$18 <"X"),Data!$J$2:$J$18)) -- HTH RP (remove nothere from the email address if mailing direct) "raspywench" wrote in message ... Hi! Here it is! I have highlighted where I am trying to place the formula. Thanks! +-------------------------------------------------------------------+ |Filename: Model.zip | |Download: http://www.excelforum.com/attachment.php?postid=4033 | +-------------------------------------------------------------------+ -- raspywench ------------------------------------------------------------------------ raspywench's Profile: http://www.excelforum.com/member.php...o&userid=20717 View this thread: http://www.excelforum.com/showthread...hreadid=484061 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average Formula with Criteria | Excel Discussion (Misc queries) | |||
| "Drop the lowest" in computing average | Excel Worksheet Functions | |||
| AVERAGE and STDEV functions with logic | Excel Worksheet Functions | |||
| average function in Excel 2002 | New Users to Excel | |||
| average, array and offsets | Excel Worksheet Functions | |||
Linear Mode
