| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a better way of doing this?
Hello Group,
I've been given the task of replicating a report using excel. The report given to me has no intructions as to how the totals are calculated so I'm winging it. I have two questions. Any help would be appreciated. First of all, here's a comma seperated version of the report I've been given: My Agency,Measure #1,Measure #2,Measure #3,Measure #4,Measure #5,Measure #6,Measure #7,Measure #8,Measure #9,Measure #10 Competitor #1,32,41,37,52,55,33,50,57,37,19 Competitor #2,55,60,34,59,75,41,76,78,20,23 Competitor #3,31,31,36,57,51,20,44,65,29,27 Competitor #4,41,52,51,64,65,37,53,70,22,22 Competitor #5,35,41,53,64,67,48,64,67,29,22 Competitor #6,35,28,56,68,86,36,77,67,33,27 Competitor #7,38,33,35,64,52,40,72,45,48,36 Competitor #8,35,53,42,57,61,32,64,64,31,25 Competitor #9,25,47,61,59,61,35,63,65,31,17 Competitor #10,34,42,64,64,66,47,60,76,22,19 Competitor #11,53,52,60,59,88,58,87,57,39,4 Competitor #12,32,41,29,43,58,33,36,66,30,34 Competitor #13,0,0,0,0,0,0,48,70,23,22 Competitor #14,26,30,53,47,44,21,32,79,16,22 Competitor #15,44,55,39,65,68,40,57,73,23,14 Competitor #16,39,38,44,35,47,44,54,46,45,46 Competitor #17,43,58,47,67,64,29,62,61,31,21 Competitor #18,23,47,0,58,58,38,46,50,41,42 Competitor #19,36,47,39,52,61,34,53,69,28,12 Competitor #20,47,48,37,48,63,35,50,67,30,15 Competitor #21,26,36,26,60,55,22,52,40,53,46 Competitor #22,35,54,43,63,66,35,50,69,27,22 Competitor #23,36,50,29,52,56,33,50,58,38,31 Competitor #24,43,50,23,54,67,40,43,57,38,28 Competitor #25,41,59,43,64,67,37,72,63,34,28 Competitor #26,32,39,47,52,54,26,57,77,19,15 Competitor #27,29,42,31,40,57,30,27,64,31,19 Competitor #28,37,47,38,62,68,44,46,74,23,22 ----,----,----,----,----,----,----,----,----,----,---- Agency to Agency Grade: F,F,F,D-,F,F,F,D-,F,F,B ----,Increase 15% for an 'A',Increase 17% for an 'A',Increase 23% for an 'A',Increase 13% for an 'A',Increase 20% for an 'A',Increase 14% for an 'A',Increase 26% for an 'A',Increase 20% for an 'A',Decrease 17% for an 'A',Decrease 5% for an 'A' Unfavorable to 17 of 27,Unfavorable to 18 of 27,Unfavorable to 17 of 27,Unfavorable to 16 of 27,Unfavorable to 18 of 27,Unfavorable to 20 of 27,Unfavorable to 17 of 27,Unfavorable to 16 of 27,Unfavorable to 21 of 27,Unfavorable to 20 of 27,Unfavorable to 6 of 27 There should be 11 columns with 33 rows. My agency's information is in the 2nd row. Competitor info is beneath the 2nd row. Please note that in the first 8 data columns (measures 1-8) a higher score is better. In that last two columns (measures 9-10) a lower score is better. Also, here is the letter grade key You compare favorably to 95% : A+ You compare favorably to 90% : A You compare favorably to 85% : A- You compare favorably to 80% : B+ You compare favorably to 75% : B You compare favorably to 70% : B- You compare favorably to 65% : C+ You compare favorably to 60% : C You compare favorably to 55% : C- You compare favorably to 50% : D+ You compare favorably to 45% : D You compare favorably to 40% : D- You compare favorably to <40% : F The first problem I'm trying to solve is the letter grade in row 31: My first attempt was a nested IF, =IF(B2<(PERCENTILE((B2:B29),0.52)),"F",IF(B2<(PERC ENTILE((B2:B29),0.64)),"D",IF(B2<(PERCENTILE((B2:B 29),0.75)),"C",IF(B2<(PERCENTILE((B2:B29),0.85))," B","A")))) but because of the 7 level limitation, I can't represent the entire letter grade key. I found a posting at: http://groups.google.com/group/micro...ecd6edb78b0642 At the bottom there is a concatenation solution for the nested IF problem but my results are not consistant. Here's that formula: =IF(B2<=(PERCENTILE(B2:B29,0.4)),"F", +IF(AND(B2(PERCENTILE(B2:B29,0.4)),B2<(PERCENTILE (B2:B29,0.45))),"D-") +IF(AND(B2(PERCENTILE(B2:B29,0.45)),B2<(PERCENTIL E(B2:B29,0.5))),"D") +IF(AND(B2(PERCENTILE(B2:B29,0.5)),B2<(PERCENTILE (B2:B29,0.55))),"D+") +IF(AND(B2(PERCENTILE(B2:B29,0.55)),B2<(PERCENTIL E(B2:B29,0.6))),"C-") +IF(AND(B2(PERCENTILE(B2:B29,0.6)),B2<(PERCENTILE (B2:B29,0.65))),"C") +IF(AND(B2(PERCENTILE(B2:B29,0.65)),B2<(PERCENTIL E(B2:B29,0.7))),"C+") +IF(AND(B2(PERCENTILE(B2:B29,0.7)),B2<(PERCENTILE (B2:B29,0.75))),"B-") +IF(AND(B2(PERCENTILE(B2:B29,0.75)),B2<(PERCENTIL E(B2:B29,0.8))),"B") +IF(AND(B2(PERCENTILE(B2:B29,0.8)),B2<(PERCENTILE (B2:B29,0.85))),"B+") +IF(AND(B2(PERCENTILE(B2:B29,0.85)),B2<(PERCENTIL E(B2:B29,0.9))),"A-") +IF(AND(B2(PERCENTILE(B2:B29,0.9)),B2<(PERCENTILE (B2:B29,0.95))),"A") +IF(B2=(PERCENTILE(B2:B29,0.95)),"A+")) I can't use VLOOKUP because I'm using the percentile function to determine the grade. A UDF may be in order here but I can't figure out how to do it. Any ideas? The 2nd problem is row 32. I have a solution but my numbers don't always match the numbers on the report. Here's my solution: ="Increase " & TEXT((PERCENTILE((B2:B29),0.9)-H4),0) &"% for an 'A'" Is this correct? should I be including my row (row 2) in the percentile calculation? Also, what about rows that have zero for a value? should those rows be excluded? Thanks in advance for any help you might be able to provide. Karl |
|
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way of doing this?
Karl,
For you first problem, try this: =INDEX({"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"},MATCH(B2,PERCENTILE(B2:B29,{0,0.4,0.45 ,0.5,0.55,0.6,0.65,0.7,0.75,0.8,0.85,0.9,0.95,1}), 1)) HTH, Ryan "Karl" wrote: Hello Group, I've been given the task of replicating a report using excel. The report given to me has no intructions as to how the totals are calculated so I'm winging it. I have two questions. Any help would be appreciated. First of all, here's a comma seperated version of the report I've been given: My Agency,Measure #1,Measure #2,Measure #3,Measure #4,Measure #5,Measure #6,Measure #7,Measure #8,Measure #9,Measure #10 Competitor #1,32,41,37,52,55,33,50,57,37,19 Competitor #2,55,60,34,59,75,41,76,78,20,23 Competitor #3,31,31,36,57,51,20,44,65,29,27 Competitor #4,41,52,51,64,65,37,53,70,22,22 Competitor #5,35,41,53,64,67,48,64,67,29,22 Competitor #6,35,28,56,68,86,36,77,67,33,27 Competitor #7,38,33,35,64,52,40,72,45,48,36 Competitor #8,35,53,42,57,61,32,64,64,31,25 Competitor #9,25,47,61,59,61,35,63,65,31,17 Competitor #10,34,42,64,64,66,47,60,76,22,19 Competitor #11,53,52,60,59,88,58,87,57,39,4 Competitor #12,32,41,29,43,58,33,36,66,30,34 Competitor #13,0,0,0,0,0,0,48,70,23,22 Competitor #14,26,30,53,47,44,21,32,79,16,22 Competitor #15,44,55,39,65,68,40,57,73,23,14 Competitor #16,39,38,44,35,47,44,54,46,45,46 Competitor #17,43,58,47,67,64,29,62,61,31,21 Competitor #18,23,47,0,58,58,38,46,50,41,42 Competitor #19,36,47,39,52,61,34,53,69,28,12 Competitor #20,47,48,37,48,63,35,50,67,30,15 Competitor #21,26,36,26,60,55,22,52,40,53,46 Competitor #22,35,54,43,63,66,35,50,69,27,22 Competitor #23,36,50,29,52,56,33,50,58,38,31 Competitor #24,43,50,23,54,67,40,43,57,38,28 Competitor #25,41,59,43,64,67,37,72,63,34,28 Competitor #26,32,39,47,52,54,26,57,77,19,15 Competitor #27,29,42,31,40,57,30,27,64,31,19 Competitor #28,37,47,38,62,68,44,46,74,23,22 ----,----,----,----,----,----,----,----,----,----,---- Agency to Agency Grade: F,F,F,D-,F,F,F,D-,F,F,B ----,Increase 15% for an 'A',Increase 17% for an 'A',Increase 23% for an 'A',Increase 13% for an 'A',Increase 20% for an 'A',Increase 14% for an 'A',Increase 26% for an 'A',Increase 20% for an 'A',Decrease 17% for an 'A',Decrease 5% for an 'A' Unfavorable to 17 of 27,Unfavorable to 18 of 27,Unfavorable to 17 of 27,Unfavorable to 16 of 27,Unfavorable to 18 of 27,Unfavorable to 20 of 27,Unfavorable to 17 of 27,Unfavorable to 16 of 27,Unfavorable to 21 of 27,Unfavorable to 20 of 27,Unfavorable to 6 of 27 There should be 11 columns with 33 rows. My agency's information is in the 2nd row. Competitor info is beneath the 2nd row. Please note that in the first 8 data columns (measures 1-8) a higher score is better. In that last two columns (measures 9-10) a lower score is better. Also, here is the letter grade key You compare favorably to 95% : A+ You compare favorably to 90% : A You compare favorably to 85% : A- You compare favorably to 80% : B+ You compare favorably to 75% : B You compare favorably to 70% : B- You compare favorably to 65% : C+ You compare favorably to 60% : C You compare favorably to 55% : C- You compare favorably to 50% : D+ You compare favorably to 45% : D You compare favorably to 40% : D- You compare favorably to <40% : F The first problem I'm trying to solve is the letter grade in row 31: My first attempt was a nested IF, =IF(B2<(PERCENTILE((B2:B29),0.52)),"F",IF(B2<(PERC ENTILE((B2:B29),0.64)),"D",IF(B2<(PERCENTILE((B2:B 29),0.75)),"C",IF(B2<(PERCENTILE((B2:B29),0.85))," B","A")))) but because of the 7 level limitation, I can't represent the entire letter grade key. I found a posting at: http://groups.google.com/group/micro...ecd6edb78b0642 At the bottom there is a concatenation solution for the nested IF problem but my results are not consistant. Here's that formula: =IF(B2<=(PERCENTILE(B2:B29,0.4)),"F", +IF(AND(B2(PERCENTILE(B2:B29,0.4)),B2<(PERCENTILE (B2:B29,0.45))),"D-") +IF(AND(B2(PERCENTILE(B2:B29,0.45)),B2<(PERCENTIL E(B2:B29,0.5))),"D") +IF(AND(B2(PERCENTILE(B2:B29,0.5)),B2<(PERCENTILE (B2:B29,0.55))),"D+") +IF(AND(B2(PERCENTILE(B2:B29,0.55)),B2<(PERCENTIL E(B2:B29,0.6))),"C-") +IF(AND(B2(PERCENTILE(B2:B29,0.6)),B2<(PERCENTILE (B2:B29,0.65))),"C") +IF(AND(B2(PERCENTILE(B2:B29,0.65)),B2<(PERCENTIL E(B2:B29,0.7))),"C+") +IF(AND(B2(PERCENTILE(B2:B29,0.7)),B2<(PERCENTILE (B2:B29,0.75))),"B-") +IF(AND(B2(PERCENTILE(B2:B29,0.75)),B2<(PERCENTIL E(B2:B29,0.8))),"B") +IF(AND(B2(PERCENTILE(B2:B29,0.8)),B2<(PERCENTILE (B2:B29,0.85))),"B+") +IF(AND(B2(PERCENTILE(B2:B29,0.85)),B2<(PERCENTIL E(B2:B29,0.9))),"A-") +IF(AND(B2(PERCENTILE(B2:B29,0.9)),B2<(PERCENTILE (B2:B29,0.95))),"A") +IF(B2=(PERCENTILE(B2:B29,0.95)),"A+")) I can't use VLOOKUP because I'm using the percentile function to determine the grade. A UDF may be in order here but I can't figure out how to do it. Any ideas? The 2nd problem is row 32. I have a solution but my numbers don't always match the numbers on the report. Here's my solution: ="Increase " & TEXT((PERCENTILE((B2:B29),0.9)-H4),0) &"% for an 'A'" Is this correct? should I be including my row (row 2) in the percentile calculation? Also, what about rows that have zero for a value? should those rows be excluded? Thanks in advance for any help you might be able to provide. Karl |
|
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way of doing this?
I can't use VLOOKUP because I'm using the percentile function to determine the grade. <<
Why would that invalidate VLOOKUP? It would just mean the lookup column of the table is based on calculations instead of being constants. |
|
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way of doing this?
Ryan,
That works except for the last two columns. In the last two columns, the lower the value the better. I tried "reversing" your formula like this: =INDEX({"A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},MATCH(J2,PERCENTILE(J2:J29,{0,0.05,0.1,0.15 ,0.2,0.25,0.3,0.35,0.4,0.45,0.5,0.55,0.6,0.75}),1) ) and that seems to do the trick. Thanks for you help! Karl |
|
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is there a better way of doing this?
Randy Harmelink wrote: I can't use VLOOKUP because I'm using the percentile function to determine the grade. << Why would that invalidate VLOOKUP? It would just mean the lookup column of the table is based on calculations instead of being constants. Randy, If I understand correctly, VLOOKUP would work. However I would need 10 seperate lookup tables. One for each of my measure columns. That's not necessarily a bad thing. I had just envisioned trying to use a single lookup table. and I couldn't see how that would work. Thanks for you help. Karl |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
