#### jmthanz

##### New Member
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

##### Board Regular
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

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

##### New Member
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

##### Board Regular
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

##### New Member
the VLOOKUP, right, thanks

1,081,513
Messages
5,359,226
Members
400,521
Latest member
smarty1995

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...