Score grades

[jmthanz]

I don't know if there is already related threads regarding my post, i just want to ask for anyone's help on how to create grades range,

such as, grades 76 to 79 would be 2.75, 90 to 100 is 1
and all grades lower than 74.99 would be 5
and this is how it would appear

thank you very much

 

[Mr_Roscoe]

You need to create a reference table with your ranges and rates e.g. (in cells F1:H8)

From To Rate
0 74.99 5.00
75 75.99 3.00
76 79.99 2.75
80 82.99 2.00
83 85.99 1.50
86 89.99 1.25
90 100 1.00

I have had to guess at the From/To so please adjust/enter further rows as required.

Then your formula would be (using John's rate as an example):

=VLOOKUP(B2,$F$1:$H$8,3,TRUE)

When testing this it returned the results you had except for Armand - I cannot see why the Rate would be 2.25, shouldn't it be 1.5

 

[jmthanz]

thanks for the reply but i already figure out how,
its just the if function
ex.
=if(cell>=95,"1", if(cell>=90,"1.25", and so on,
i used to do the reverse from lowest grades to highest, it must be from the highest to lowest

any way thanks for the help

 

[Mr_Roscoe]

jmthanz - your IF statement is quite OK. And creating a combination of them is called Nesting If Statements. There is a limitation to Nesting (usually 7 statements) and if you reach that you may have to try a different method so please keep my solution in mind.

cheers, Ian R.

 

[jmthanz]

the VLOOKUP, right, thanks