RANK function to GRADE "A","B" students.

all4excel · 12-11-2007, 02:22 PM

I want to Grade students based on their scores in five subjects.

Student's Name Physics Chemistry Biology Algebra GeometryTotal RANK GRADE
John 10 10 10 10 10 50 1 A

I want to GRADE the students based on their scores in the five subjects…
The total is calculated in the column H
[ SUM(c2:g2) ].
The students are ranked based on their scores [ RANK(H2,$H$2:$H$21) ]..
Now for Grading the students,
I want to grade the students in this fashion..
The Ist 20% based on the rank - Grade A
The IInd 20% based on the rank - Grade B
The IIIrd 20% based on the rank - Grade C
The IVth 20% based on the rank - Grade D
The Vth 20% based on the rank - Grade E.

Please find the attachment below.

**shg** · 12-11-2007, 02:26 PM

Look at the PERCENTILE function, and post back if you have questions.

all4excel · 12-11-2007, 02:44 PM

Originally Posted by shg

Look at the PERCENTILE function, and post back if you have questions.

DId u go thru the file which I have attached...

I dont think that this function can really help me as i cudnt figure out that..

Can u please explain?

**shg** · 12-11-2007, 03:33 PM

Put this formula in J2 and copy down:

=LOOKUP(H2, PERCENTILE($H$2:$H$12, {0,0.2,0.4,0.6,0.8}), {"E","D","C","B","A"})

all4excel · 12-12-2007, 12:30 AM

Originally Posted by shg

Put this formula in J2 and copy down:

=LOOKUP(H2, PERCENTILE($H$2:$H$12, {0,0.2,0.4,0.6,0.8}), {"E","D","C","B","A"})

.

See what I want is the no of records should be divided into 5 layers--

Ist Layer - A (1st 20% OF max(rank) ) so on and so forth...
IInd Layer -B
IIrd Layer - C
IVth Layer -D
Vth Layer - E

**shg** · 12-12-2007, 02:12 AM

It's really not clear to me how you're grading this. Perhaps in J2 and copy down,

=LOOKUP(H2/MAX($H$2:$H$20), {0,0.2,0.4,0.6,0.8}, {"E","D","C","B","A"})

That gives student 80% of max grade an A, 60% of max grade a B, ...