Is there a better way of doing this?

[Karl]

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<(PERCENTILE((B2:B29),0.64)),"D",IF(B2<(PERCENTILE((B2:B29),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/micr...ted+if+problem&rnum=35&hl=en#1cecd6edb78b0642

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<(PERCENTILE(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<(PERCENTILE(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<(PERCENTILE(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<(PERCENTILE(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<(PERCENTILE(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

 

[Ryan Poth]

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

 

[Randy Harmelink]

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.

 

[Karl]

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

 

[Karl]

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