caluculate an average based on 2 criteria
Hi!
Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:
=IF(SUMIF(Sheet1!A$1:A$5,F1,Sheet1!D$1:D$5),AVERAG E(IF(Sheet1!A$1:A$5=F1,IF(Sheet1!D$1:D$5<"",Sheet 1!D$1:D$5))),0)
Copy down as needed.
Biff
wrote in message
ups.com...
I need to caluculate an average based on 2 criteria. Using the example
below, if A="brian" (F1) and D=not blank, then average all numbers in D
that have F1 ("brian") in column A. So in the example below, it would
average D1 & D4 (not include D2).
HELP anyone! Thanks.
Database (Sheet1)
A B C D*
1 brian 08/01/05 09/01/05 30
2 chris 08/15/05 09/01/05 15
3 pam
4 brian 08/15/05 09/01/05 15
5 brian 08/31/05
*-Formula to calculate days =IF(M20,(DAYS360(G2,M2))," ")
Totals/Formula (Sheet2)
F G
1 brian NEW FORMULA WILL GO HERE
2 chris
3 pam
|