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

Wouldn't it need to average D5 also?

I would suggest a lookup formula.

" wrote:

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

=AVERAGE(IF((A1:A5=F1)*(D1:D5<""),D1:D5))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please)


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

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