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

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?

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

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:

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?

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

=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)

"Eulie-Denver" wrote in message
...
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?

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

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.

"Bob Phillips" wrote:

=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)

"Eulie-Denver" wrote in message
...
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?

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

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

"Marcelo" wrote:

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:

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?

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

* certainly does mean AND.

--

HTH

Bob Phillips

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

"Eulie-Denver" wrote in message
...
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.

"Bob Phillips" wrote:

=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)

"Eulie-Denver" wrote in message
...
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?