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

[all4excel]

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]

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

[all4excel]

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]

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]

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]

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, ...

[all4excel]

I am attaching the file for you to get what i want ...

I will try your logic as well but before that please go thru the file I am attaching..

[all4excel]

What i want is to have all the grades if the no of records is more than = equal to 5 as we have 5 grades.

The logic works fine but goes for toss when the scores repeat. as the rank repeats skipping the next rank it directly affects the grades as the grades also gets skipped..


If u change the scores it does not work...

Buda 100 1 A B
Jack 91 2 B C
Steven 89 3 C D
Cynthia 75 4 D E
Tom 70 5 E E
Tim 70 5 E E
Adam 70 5 E E
Eve 70 5 E E
Jane 70 5 E E
Kean 70 5 E E
John 70 5 E E
Jill 70 5 E E
Margaret 70 5 E E

[shg]

Sorry, I don't understand a scoring system based on rank versus grades. In your example, the scores are 50, 50, 48, 40, 32, 24, and the grades A, A, C, D, E, E.

Why does someone who scored 96% of the top score receive a C?