C
craggergirl
I am in desperate need of some assistance. I am a decent excel user, an
I can create formulas with 7 nested ifs, but I am totally stuck o
determining the appropriate formulas to calcuate an average of 1) on
column based on criteria in two other columns, and 2) one column base
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 - th
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 o
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 i
based on only days to process by contract type if the contract wa
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 i
column A.
FOR #1 I'VE TRIED THE FOLLOWING EXAMPLES
=SUMIF(D2
169,"RA",S2:S169)/COUNTIF(D2169,"RA")
and
=AVERAGE(IF((D2169="RA")*(L2:L169="Y"),S2:S169)) plu
Ctrl+Shift+Enter
FOR #2 I'VE TRIED THE FOLLOWING EXAMPLE
=AVERAGE(IF((A2:A169="A"),(D2169="MOB"),S2:S169)) plu
Ctrl+Shift+Enter
Remember, column S contains blank cells if column L=No, so if I'm no
mistaken, they shouldn't be included in my average
Obviously, each of these formulas will output numbers, but they don'
seem to produce accurate averages. Can someone PLEASE help me figur
out what I'm doing wrong? Any suggestions are most appreciated
Thanks!
I can create formulas with 7 nested ifs, but I am totally stuck o
determining the appropriate formulas to calcuate an average of 1) on
column based on criteria in two other columns, and 2) one column base
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 - th
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 o
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 i
based on only days to process by contract type if the contract wa
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 i
column A.
FOR #1 I'VE TRIED THE FOLLOWING EXAMPLES
=SUMIF(D2
169,"RA",S2:S169)/COUNTIF(D2169,"RA")and
=AVERAGE(IF((D2
169="RA")*(L2:L169="Y"),S2:S169)) pluCtrl+Shift+Enter
FOR #2 I'VE TRIED THE FOLLOWING EXAMPLE
=AVERAGE(IF((A2:A169="A"),(D2
169="MOB"),S2:S169)) pluCtrl+Shift+Enter
Remember, column S contains blank cells if column L=No, so if I'm no
mistaken, they shouldn't be included in my average
Obviously, each of these formulas will output numbers, but they don'
seem to produce accurate averages. Can someone PLEASE help me figur
out what I'm doing wrong? Any suggestions are most appreciated
Thanks!