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

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

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

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

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

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

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