Grading system

[jigneshkumar.hingu]

Hi Chandoo,


I am regular user of your site and formulas. I have problem in grading of the people based on certain range. e.g.,

Score Achievement Grade

60-69 66 4

70-79 75 3

80-89 81 2

90-100 93 1


How can it can be automatically grading in Grade column if I put some value in Achievement column.??


Kindly help me.


Thanks & Regards,

Jignesh

 

[anupamtiwari05]

Hi Jignesh,


Welcome to Chandoo.org.


I tried the below formula on your given data and its working fine for me however the solution may not be good enough so will wait for our excel experts to provide us a better solution.


Suppose your Score is in A column, Achievement in B and Grade in C column.


Now please enter below formula in C2 and drag it down till the last row of your data.


=IF(AND(B2<=69,B2>=60),4,IF(AND(B2<=79,B2>=70),3,IF(AND(B2<=89,B2>=80),2,IF(AND(B2<=100,B2>=90),1,0))))


Here I have considered 0 grade for achievement below 60.


Please try the above and let me know if its working fine for you.


Thanks & Regards,

Anupam

 

[jigneshkumar.hingu]

HI Anupam,


It helps me a lot.


Thanks for the update.


Jignesh.

 

[jason]

if the score is lower than 60, do they still get the same grade (4)?


if so:

=IF(B2>=90,1,IF(B2>=80,2,IF(B2>=70,3,4)))

 

[Kevin@Radstock]

Hi jigneshkumar.hingu


One option is the LOOKUP function, assuming the score is in A1:


=LOOKUP(A1,{0,70,80,90},{4,3,2,1})


You might want to look at the VLOOKUP function, where you will have a table in your sheet. Easy to maintain as well.