Exam scores

[Guest]

Hi,

I have a several exam scores, eg 85%,75%,65% etc etc
I would like to know the easiest way of banding the scores such as >90% is
shown as A+, >80<90 is shown as A, >70<80 is B etc etc.

Grateful for any assistance.

 

[Kevin H. Stecyk]

Newbie...

Hi,

I have a several exam scores, eg 85%,75%,65% etc etc
I would like to know the easiest way of banding the scores such as >90% is
shown as A+, >80<90 is shown as A, >70<80 is B etc etc.

Grateful for any assistance.

Hi Newbie,

=IF(B1>=90,"A+",IF(B1>=80,"A",IF(B1>=65,"B",IF(B1>=50,"C",IF(B1>=40,"D","F")))))Watch the line wrap.I would use a vlookup table.In column 1 of your table, have your scores listed in ascending order.In column 2 of your table, have your letter grades.VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)=vlookup(A1, k1:L10, 2), where A1 was the grade, and K1 to L10 was yourlookup table.I hope this helps.Kevin

 

[Kevin H. Stecyk]

Something happened with my last post. I will try that again.

Newbie...

Hi,

I have a several exam scores, eg 85%,75%,65% etc etc
I would like to know the easiest way of banding the scores such as >90% is
shown as A+, >80<90 is shown as A, >70<80 is B etc etc.

Grateful for any assistance.

Hi Newbie,

=IF(B1>=90,"A+",IF(B1>=80,"A",IF(B1>=65,"B",IF(B1>=50,"C",IF(B1>=40,"D","F")))))Watch the line wrap.I would use a vlookup table.In column 1 of your table, have your scores listed in ascending order.In column 2 of your table, have your letter grades.VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)=vlookup(A1, k1:L10, 2), where A1 was the grade, and K1 to L10 wasyourlookup table.I hope this helps.Kevin(Let's hope this is more legible.)

 

[Gord Dibben]

Newbie

Assuming scores are in column A starting at A1.

In B1 enter this formula then drag/copy down column B

=LOOKUP(A1,{0,31,41,51,61,71,81,91,101},{"E","D","C-","C","C+","B","B+","A"})

Adjust the ranges and letters to suit.


Gord Dibben Excel MVP