Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average with 2 criteria | Excel Worksheet Functions | |||
Average / sumproduct based on multiple criteria | Excel Discussion (Misc queries) | |||
Average # in total group with multiple criteria? | Excel Worksheet Functions | |||
Using Multiple Array Criteria - Determine Average Result | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions |