software

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(D2169,quot;RAquot;,S2:S169)/COUNTIF(D2169,quot;RAquot;)

and

=AVERAGE(IF((D2169=quot;RAquot;)*(L2:L169=quot;Yquot;),S2:S169)) plus
Ctrl Shift Enter

FOR #2 I'VE TRIED THE FOLLOWING EXAMPLE:

=AVERAGE(IF((A2:A169=quot;Aquot;),(D2169=quot;MOBquot;),S2:S169) ) plus
Ctrl Shift EnterRemember, 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 - www.officehelp.inHi!

Try these:

Both array entered:

1. =AVERAGE(IF(D213=quot;mobquot;,IF(L2:L13=quot;Yquot;,S2:S13)))

2. =AVERAGE(IF(A2:A13=quot;Aquot;,IF(D213=quot;mobquot;,IF(L2:L13=quot; Yquot;,S2:S13))))

Biff

quot;craggergirlquot; gt; wrote in message
...
gt;
gt; I am in desperate need of some assistance. I am a decent excel user, and
gt; I can create formulas with 7 nested ifs, but I am totally stuck on
gt; determining the appropriate formulas to calcuate an average of 1) one
gt; column based on criteria in two other columns, and 2) one column based
gt; on criteria in 3 other columns. Here's an example:
gt;
gt; Column A = Division Name
gt; Column D= Contract Type
gt; Comlumn L= Yes/No - The contract is ready for review
gt; Colum S= Days to Process Contract
gt;
gt; (sorry - can't get the formatting to appear rigt in this post - the
gt; first column of #s represent row references, the underlined letters =
gt; headers, the rest = data in cells)
gt;
gt;
gt; _A_ _D_ _L_ _S_
gt;
gt; 2 A MOB Y
gt; 3
gt; 3 A MOB Y
gt; 6
gt; 4 G MOB N
gt; 5 G MOB Y
gt; 8
gt; 6 N RA Y
gt; 10
gt; 7 N RA N
gt; 9 A RA Y
gt; 3
gt; 10 A RA Y
gt; 6
gt; 11 G EA N
gt; 12 G EA Y
gt; 8
gt; 13 N EA Y
gt; 10
gt; 14 N EA N
gt;
gt; I want to calculate the average of column S (derived from a sequence of
gt; nested ifs based on other columns in the spreadsheet) in two ways:
gt;
gt; 1) By the contract type in column D if column L =Yes so the average is
gt; based on only days to process by contract type if the contract was
gt; ready for review (note if column L=N column S is blank); and
gt;
gt; 2) By the same as #1 above but also broken out by each division in
gt; column A.
gt;
gt; FOR #1 I'VE TRIED THE FOLLOWING EXAMPLES:
gt;
gt; =SUMIF(D2169,quot;RAquot;,S2:S169)/COUNTIF(D2169,quot;RAquot;)
gt;
gt; and
gt;
gt; =AVERAGE(IF((D2169=quot;RAquot;)*(L2:L169=quot;Yquot;),S2:S169)) plus
gt; Ctrl Shift Enter
gt;
gt; FOR #2 I'VE TRIED THE FOLLOWING EXAMPLE:
gt;
gt; =AVERAGE(IF((A2:A169=quot;Aquot;),(D2169=quot;MOBquot;),S2:S169) ) plus
gt; Ctrl Shift Enter
gt;
gt;
gt; Remember, column S contains blank cells if column L=No, so if I'm not
gt; mistaken, they shouldn't be included in my average
gt;
gt; Obviously, each of these formulas will output numbers, but they don't
gt; seem to produce accurate averages. Can someone PLEASE help me figure
gt; out what I'm doing wrong? Any suggestions are most appreciated.
gt; Thanks!!
gt;
gt;
gt; --
gt; craggergirl
gt; Posted from - www.officehelp.in
gt;

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 - www.officehelp.in