How to create a formula in Excel

[Guest]

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

 

[Guest]

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.

 

[Frank Kabel]

Hi
try the array formula
=AVERAGE(IF(C4:C362="M.2FL",H4:H362))

comitted with CTRL+SHIFT+ENTER

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Frank Kabel]

Hi
this is an array formula. See:
http://www.cpearson.com/excel/array.htm