Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to create a formula in Excel
I need to create a formula that will average numbers in one column, based on
data in another column. example: AVERAGE H4:H362 IF C4:C362=M.2FL or AVERAGE H4:H362 IF C4:C362=M.MED Thanks for your help |
#2
|
|||
|
|||
You can use a CSE (control-shift-enter) average, but I don't know if it can
be done with two conditions. Of course if you want to be really basic you could do two sumif formulas and two countif formulas add each of the two pairs and then divide the sums by counts. "LWS" wrote: I need to create a formula that will average numbers in one column, based on data in another column. example: AVERAGE H4:H362 IF C4:C362=M.2FL or AVERAGE H4:H362 IF C4:C362=M.MED Thanks for your help |
#3
|
|||
|
|||
Hi
try the array formula =AVERAGE(IF(C4:C362="M.2FL",H4:H362)) comitted with CTRL+SHIFT+ENTER -- Regards Frank Kabel Frankfurt, Germany "LWS" schrieb im Newsbeitrag ... I need to create a formula that will average numbers in one column, based on data in another column. example: AVERAGE H4:H362 IF C4:C362=M.2FL or AVERAGE H4:H362 IF C4:C362=M.MED Thanks for your help |
#4
|
|||
|
|||
just to clarify, it would look something like this for your M.2FL example --
=sumif(c4:c362,"M.2FL",h4:h362)/countif(c4:c362,"M.2FL") your M.MED example would be the same, just substituting in M.MED for M.2FL. "Ted Metro" wrote: You can use a CSE (control-shift-enter) average, but I don't know if it can be done with two conditions. Of course if you want to be really basic you could do two sumif formulas and two countif formulas add each of the two pairs and then divide the sums by counts. "LWS" wrote: I need to create a formula that will average numbers in one column, based on data in another column. example: AVERAGE H4:H362 IF C4:C362=M.2FL or AVERAGE H4:H362 IF C4:C362=M.MED Thanks for your help |
#5
|
|||
|
|||
WOW !!!! it works !!! I have been fighting with this, and other variations,
for weeks, several people in my office have also been fighting it. Thank you so much !!! Frank, one question, I have never used the "comitted with CTRL+SHIFT+ENTER" before, how does that work, and how can I apply it with other formulas? Thank you very much. Lisa "Frank Kabel" wrote: Hi try the array formula =AVERAGE(IF(C4:C362="M.2FL",H4:H362)) comitted with CTRL+SHIFT+ENTER -- Regards Frank Kabel Frankfurt, Germany "LWS" schrieb im Newsbeitrag ... I need to create a formula that will average numbers in one column, based on data in another column. example: AVERAGE H4:H362 IF C4:C362=M.2FL or AVERAGE H4:H362 IF C4:C362=M.MED Thanks for your help |
#6
|
|||
|
|||
Ted,
I was planning on using this formula as a template. I also need to calculate minimum and maximum completion times based on the same variables. Will this formula work for that, as well? Thanks, Lisa "Ted Metro" wrote: just to clarify, it would look something like this for your M.2FL example -- =sumif(c4:c362,"M.2FL",h4:h362)/countif(c4:c362,"M.2FL") your M.MED example would be the same, just substituting in M.MED for M.2FL. "Ted Metro" wrote: You can use a CSE (control-shift-enter) average, but I don't know if it can be done with two conditions. Of course if you want to be really basic you could do two sumif formulas and two countif formulas add each of the two pairs and then divide the sums by counts. "LWS" wrote: I need to create a formula that will average numbers in one column, based on data in another column. example: AVERAGE H4:H362 IF C4:C362=M.2FL or AVERAGE H4:H362 IF C4:C362=M.MED Thanks for your help |
#7
|
|||
|
|||
Hi
this is an array formula. See: http://www.cpearson.com/excel/array.htm -- Regards Frank Kabel Frankfurt, Germany "LWS" schrieb im Newsbeitrag ... WOW !!!! it works !!! I have been fighting with this, and other variations, for weeks, several people in my office have also been fighting it. Thank you so much !!! Frank, one question, I have never used the "comitted with CTRL+SHIFT+ENTER" before, how does that work, and how can I apply it with other formulas? Thank you very much. Lisa "Frank Kabel" wrote: Hi try the array formula =AVERAGE(IF(C4:C362="M.2FL",H4:H362)) comitted with CTRL+SHIFT+ENTER -- Regards Frank Kabel Frankfurt, Germany "LWS" schrieb im Newsbeitrag ... I need to create a formula that will average numbers in one column, based on data in another column. example: AVERAGE H4:H362 IF C4:C362=M.2FL or AVERAGE H4:H362 IF C4:C362=M.MED Thanks for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions | |||
How do I create a formula in Excel that will countif or sumif bef. | Excel Worksheet Functions | |||
How do I create a multi formula IF function in Excel? | Excel Worksheet Functions | |||
how do i create a blank cell in excel using a formula | Excel Worksheet Functions |