I am trying to create a formula that will look for a particular condition in
one column, and if that condition exists, then average the numbers from a
different column for the same row. As an example, one column contain the
names of multiple sales people, the other column contains the number of units
each one sold each day. I want the formula to average the number of units
sold for each person's name. So, if "Joe" sold 10 units one day and 20 units
another day, the formula would indicate that Joe sold 15 units.

=AVERGE(IF(A2:A100="Joe",B2:B100))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom

"William" wrote in message
...
I am trying to create a formula that will look for a particular condition
in
one column, and if that condition exists, then average the numbers from a
different column for the same row. As an example, one column contain the
names of multiple sales people, the other column contains the number of
units
each one sold each day. I want the formula to average the number of units
sold for each person's name. So, if "Joe" sold 10 units one day and 20
units
another day, the formula would indicate that Joe sold 15 units.

=SUMPRODUCT(--(A1:A1000="Joe"),B1:B1000)/SUMPRODUCT(--(A1:A1000="Joe"))

or

=AVERAGE(IF(A1:A1000="Joe",B1:B1000))

The second is an array formula, so is committed with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"William" wrote in message
...
I am trying to create a formula that will look for a particular condition
in
one column, and if that condition exists, then average the numbers from a
different column for the same row. As an example, one column contain the
names of multiple sales people, the other column contains the number of
units
each one sold each day. I want the formula to average the number of units
sold for each person's name. So, if "Joe" sold 10 units one day and 20
units
another day, the formula would indicate that Joe sold 15 units.

On Fri, 25 Feb 2005 11:23:04 -0800, "William"
wrote:

I am trying to create a formula that will look for a particular condition in
one column, and if that condition exists, then average the numbers from a
different column for the same row. As an example, one column contain the
names of multiple sales people, the other column contains the number of units
each one sold each day. I want the formula to average the number of units
sold for each person's name. So, if "Joe" sold 10 units one day and 20 units
another day, the formula would indicate that Joe sold 15 units.

Here's one way:

Column of Names is named 'Names'
Column of Units is named 'Units'

=SUMIF(Names,"Joe",Units) / COUNTIF(Names, "Joe")

"Joe" (without the quotes) can be in a cell, also.


--ron