Results 1 to 2 of 2

20060227, 01:13 #1
 Join Date
 Jun 2002
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
Is there a better way of doing this? (Excel 2003)
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 18) a higher score is better. In that last two columns (measures 910) 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/microsoft.p...cecd6edb78b0642
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

20060227, 08:26 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Is there a better way of doing this? (Excel 2003)
There must be something wrong with the report  data seem to have shifted.
1) Why is the text "My Agency" in row 1, but its data in row 2?
2) What does "Unfavorable to 17 of 27" in column A refer to?
3) The grades etc. in columns J and K are not consistent with the data (and "decrease for an A? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>)
4) I don't have the slightest idea how to arrive at the Increase for an 'A' percentages.
I have attached a version of your workbook with formulas for the grade and for the "compares unfavorably to" numbers, but I think you should ask the person who provided the report about the points I mentioned first, before we can proceed.