Conditional Formulas For Averaging

[Guest]

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.

 

[Peo Sjoblom]

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

entered with ctrl + shift & enter

 

[Bob Phillips]

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

 

[Ron Rosenfeld]

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