Average by Multiple Criteria.
 

How can I try to do an average requiring multiple criteria? For example, I
want to do it by Employee Name and Date Range.

So if cells A1:A100 is the Name, B1:B100 is the Date and C1:C100 is the
score to be averaged, how can I have it pull an average for John Doe for
April 1-15th?

Thank you

Average by Multiple Criteria.
 

If you are using Excel2007, you may try:

=AVERAGEIFS(C1:C100,A1:A100,"John Doe",B1:B100, "3/31/09",B1:B100,"<4/16/09")


Please advise if this helps.


RoadKill wrote:
How can I try to do an average requiring multiple criteria? For example, I
want to do it by Employee Name and Date Range.

So if cells A1:A100 is the Name, B1:B100 is the Date and C1:C100 is the
score to be averaged, how can I have it pull an average for John Doe for
April 1-15th?

Thank you

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200904/1

Average by Multiple Criteria.
 

=SUMPRODUCT(--(A1:A100 ="John Doe"),--(B1:B100 = DATE(2009,4,1)),--(B1:B100
<= DATE(2009,4,15)),(C1:C100 ))
will give you the sum
and
=SUMPRODUCT(--(A1:A100 ="John Doe"),--(B1:B100 = DATE(2009,4,1)),--(B1:B100
<= DATE(2009,4,15)),--(C1:C100<"" ))
will give you the count

So your average will be
=SUMPRODUCT(--(A1:A100 ="John Doe"),--(B1:B100 = DATE(2009,4,1)),--(B1:B100
<= DATE(2009,4,15)),--(C1:C100<"" ))/SUMPRODUCT(--(A1:A100 ="John
Doe"),--(B1:B100 = DATE(2009,4,1)),--(B1:B100 <=
DATE(2009,4,15)),--(C1:C100<"" ))



"RoadKill" wrote:

How can I try to do an average requiring multiple criteria? For example, I
want to do it by Employee Name and Date Range.

So if cells A1:A100 is the Name, B1:B100 is the Date and C1:C100 is the
score to be averaged, how can I have it pull an average for John Doe for
April 1-15th?

Thank you

Average by Multiple Criteria.
 

Try this array formula**.

Use cells to hold the criteria.

E1 = John Doe
F1 = 4/1/2009
G1 = 4/15/2009

=AVERAGE(IF((A1:A100=E1)*(B1:B100=F1)*(B1:B100<=G 1),C1:C100))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"RoadKill" wrote in message
...
How can I try to do an average requiring multiple criteria? For example, I
want to do it by Employee Name and Date Range.

So if cells A1:A100 is the Name, B1:B100 is the Date and C1:C100 is the
score to be averaged, how can I have it pull an average for John Doe for
April 1-15th?

Thank you