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?

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?

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

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?

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?

* 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?