Excel-only average cells if two cells in same row, meet two condit

[Guest]

I want to average only some of the values in a column, based on tests on
other cells in the same row.

For example:
Suppose I have numeric values in A1:A10 and alpha characters in columns
B1:B10 and C1:C10.
If the value in cell B1="ORD" and the value in cell C1="MIA" then use the
numeric value in cell A1 in the average. If these two conditions are not
TRUE then treat cell A1 as blank.
I want this same check to apply to each row.
So I want to average only the An cells that meet this condition.

Can anyone help me code this function?

 

[Guest]

hi Eulie,

I'm not sure if I understand exactly you are looking for, but try it:

=sumproduct(--(b2:b100="ORD")*(c1:c100="MIA"),(a1:a100))/sumproduct((--(b2:b100="ORD")*(c1:c100="MIA"))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Eulie-Denver" escreveu:

 

[Bob Phillips]

=AVERAGE(IF((B1:B10="ORD")*(C1:C10="MIA"),A1:A10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Yes this is exactly what I wanted to do. I cut and pasted it and changed to
the actual columns and number of columns and it worked. I'll have to read up
a bit more on array formulas though to fully understand. The asterisk must
mean 'AND'.
Thanks, this was great help.

 

[Guest]

Thank you for taking time to help. The solution from Bob Philips worked for
me.
Eulie

 

[Bob Phillips]

* certainly does mean AND.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)