Nested If's

C

Cletus Stripling

I have a need where I have more than 7 conditions to check for and am
not sure the best approach to tackle the task. Any advice appreciated.

I have a range of various GPA's and want to assign a student a score
based on the range their GPA falls in.

3.94-4.0 75
3.81-3.93 70
3.68-3.80 65
3.54-3.67 60
3.40-3.53 55
3.25-3.39 50
3.10-3.24 45
2.95-3.09 40
2.80-2.94 35

So for example,
GPA Score
Student 1 3.95 75
Student 2 3.70 65
Student 3 2.80 40

Is the best approach for something like this to use visual basic? Anyone
know an easy solution?

Thanks!
 
B

bill k

set up a table first
0 10
2.80 35
2.95 40
3.10 45
3.25 50
3.40 55
3.54 60
3.68 65
3.81 70
3.94 75
4.01 100

note: the left column must be increasing values
and I entered some extra values for the outside ranges just in case

use the vlookup formula to get the second column values

=VLOOKUP(B1,$C$1:$D$11,2,TRUE)

where B1 is the score for the student.
$C$1:$D$11 is the table
2 indicates the second column in the table.

note 2.8 will get 35 not 40 ( :> ))

extend the formula down the list

have fun
 
G

Gord Dibben

Cletus

=LOOKUP(cellref,{0,2.8,2.95,3.1,3.25,3.4,3.54,3.68,3.81,3.94},{"fail",35,40,45,50,55,60,65,70,75})

where cellref is student score.

Also.......I believe you made an error in typing.

Student 3 at 2.80 would be a 35 GPA(2.80-2.94)


Gord Dibben Excel MVP
 
Top