Score grades

jmthanz

New Member
Joined
Aug 16, 2010
Messages
3
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
Joined
Mar 28, 2006
Messages
200
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

New Member
Joined
Aug 16, 2010
Messages
3
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

Board Regular
Joined
Mar 28, 2006
Messages
200
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.
 

Forum statistics

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

Some videos you may like

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