Nested If's
 

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!

Hi Cletus

You can use a worksheetfunction to do this
http://www.cpearson.com/excel/excelF.htm#Grades

Or on the worksheet make a list with GPA's and scores and use
A Vlookup formula


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Cletus Stripling" wrote in message ...
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!

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


--
bill k


------------------------------------------------------------------------
bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821
View this thread: http://www.excelforum.com/showthread...hreadid=471935

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

On Thu, 29 Sep 2005 16:58:33 -0400, Cletus Stripling wrote:

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!

You're right! Typo! Excatly what I was looking for...many thanks!

Gord Dibben wrote:
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

On Thu, 29 Sep 2005 16:58:33 -0400, Cletus Stripling wrote:


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!