Calculating an average based on 2 and 3 criteria
 

I am in desperate need of some assistance. I am a decent excel user, and
I can create formulas with 7 nested ifs, but I am totally stuck on
determining the appropriate formulas to calcuate an average of 1) one
column based on criteria in two other columns, and 2) one column based
on criteria in 3 other columns. Here's an example:

Column A = Division Name
Column D= Contract Type
Comlumn L= Yes/No - The contract is ready for review
Colum S= Days to Process Contract

(sorry - can't get the formatting to appear rigt in this post - the
first column of #s represent row references, the underlined letters =
headers, the rest = data in cells)


_A_ _D_ _L_ _S_

2 A MOB Y
3
3 A MOB Y
6
4 G MOB N
5 G MOB Y
8
6 N RA Y
10
7 N RA N
9 A RA Y
3
10 A RA Y
6
11 G EA N
12 G EA Y
8
13 N EA Y
10
14 N EA N

I want to calculate the average of column S (derived from a sequence of
nested ifs based on other columns in the spreadsheet) in two ways:

1) By the contract type in column D if column L =Yes so the average is
based on only days to process by contract type if the contract was
ready for review (note if column L=N column S is blank); and

2) By the same as #1 above but also broken out by each division in
column A.

FOR #1 I'VE TRIED THE FOLLOWING EXAMPLES:

=SUMIF(D2:D169,"RA",S2:S169)/COUNTIF(D2:D169,"RA")

and

=AVERAGE(IF((D2:D169="RA")*(L2:L169="Y"),S2:S169)) plus
Ctrl+Shift+Enter

FOR #2 I'VE TRIED THE FOLLOWING EXAMPLE:

=AVERAGE(IF((A2:A169="A"),(D2:D169="MOB"),S2:S169) ) plus
Ctrl+Shift+Enter


Remember, column S contains blank cells if column L=No, so if I'm not
mistaken, they shouldn't be included in my average

Obviously, each of these formulas will output numbers, but they don't
seem to produce accurate averages. Can someone PLEASE help me figure
out what I'm doing wrong? Any suggestions are most appreciated.
Thanks!!


--
craggergirl
Posted from - http://www.officehelp.in

Calculating an average based on 2 and 3 criteria
 

Hi!

Try these:

Both array entered:

1. =AVERAGE(IF(D2:D13="mob",IF(L2:L13="Y",S2:S13)))

2. =AVERAGE(IF(A2:A13="A",IF(D2:D13="mob",IF(L2:L13=" Y",S2:S13))))

Biff

"craggergirl" wrote in message
...

I am in desperate need of some assistance. I am a decent excel user, and
I can create formulas with 7 nested ifs, but I am totally stuck on
determining the appropriate formulas to calcuate an average of 1) one
column based on criteria in two other columns, and 2) one column based
on criteria in 3 other columns. Here's an example:

Column A = Division Name
Column D= Contract Type
Comlumn L= Yes/No - The contract is ready for review
Colum S= Days to Process Contract

(sorry - can't get the formatting to appear rigt in this post - the
first column of #s represent row references, the underlined letters =
headers, the rest = data in cells)


_A_ _D_ _L_ _S_

2 A MOB Y
3
3 A MOB Y
6
4 G MOB N
5 G MOB Y
8
6 N RA Y
10
7 N RA N
9 A RA Y
3
10 A RA Y
6
11 G EA N
12 G EA Y
8
13 N EA Y
10
14 N EA N

I want to calculate the average of column S (derived from a sequence of
nested ifs based on other columns in the spreadsheet) in two ways:

1) By the contract type in column D if column L =Yes so the average is
based on only days to process by contract type if the contract was
ready for review (note if column L=N column S is blank); and

2) By the same as #1 above but also broken out by each division in
column A.

FOR #1 I'VE TRIED THE FOLLOWING EXAMPLES:

=SUMIF(D2:D169,"RA",S2:S169)/COUNTIF(D2:D169,"RA")

and

=AVERAGE(IF((D2:D169="RA")*(L2:L169="Y"),S2:S169)) plus
Ctrl+Shift+Enter

FOR #2 I'VE TRIED THE FOLLOWING EXAMPLE:

=AVERAGE(IF((A2:A169="A"),(D2:D169="MOB"),S2:S169) ) plus
Ctrl+Shift+Enter


Remember, column S contains blank cells if column L=No, so if I'm not
mistaken, they shouldn't be included in my average

Obviously, each of these formulas will output numbers, but they don't
seem to produce accurate averages. Can someone PLEASE help me figure
out what I'm doing wrong? Any suggestions are most appreciated.
Thanks!!


--
craggergirl
Posted from - http://www.officehelp.in

Calculating an average based on 2 and 3 criteria
 

Thanks for the prompt reply, Bill! I appreciate your assistance. Will
try the formula as soo as I get to work! Many thanks!


--
craggergirl
Posted from - http://www.officehelp.in