Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IIF Function
Hi,
I am working on an Office 2003 worksheet which determines a grade for users. Grades are based on scores and are graded as shown below. GD1 GD2 GD3 GD4 GD5 96.2 and above 89.3 and above 84.2 to 89.2 78.1 to 84.1 78.0 and below I have a total score for each person i.e. 86.8 which would put this person into GD3. How do I automatically populate a grade cell for each person based on there scores? Is an IIF function the best method here? if so how would I code this? Please help if possible. Kind Regards John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IIF Function
Hi John
I'd be tempted to write a function for this that you can call from your code utilizing a Select Case structure, maybe something like: Function Grade(d As Double) As String Select Case d Case Is = 96.2 Grade = "GD1" Case Is = 89.3 Grade = "GD2" Case Is = 84.2 Grade = "GD3" Case Is = 78.1 Grade = "GD4" Case Else Grade = "GD5" End Select End Function Then if you want to fill a particular cell with the grade, you can call the function in code like: Range("B1") = Grade(Range("A1").Value) Assuming that the actual grade score was contained in A1. Make sense? Best regards Richard On 18 Feb, 18:20, "John" wrote: Hi, I am working on an Office 2003 worksheet which determines a grade for users. Grades are based on scores and are graded as shown below. GD1 GD2 GD3 GD4 GD5 96.2 and above 89.3 and above 84.2 to 89.2 78.1 to 84.1 78.0 and below I have a total score for each person i.e. 86.8 which would put this person into GD3. How do I automatically populate a grade cell for each person based on there scores? Is an IIF function the best method here? if so how would I code this? Please help if possible. Kind Regards John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IIF Function
A worksheet formula with embedded IF's would suffice. Suppose the
lower bounds for GD1-GD4 are in cells B1:E1 and a student's numerical score is in A2. Then =IF(A2<$E$1,"GD5",IF(A2=$B$1,"GD1",IF(A2=$C$1,"G D2",IF(A2=$D $1,"GD3","GD4")))) gets what you want. Hth, Merjet |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IIF Function
If you don't want to use VBA:
=IF(C2<78.1,"GD5",CHOOSE(MATCH(C2,{78.1,84.2,89.3, 96.2},1),"GD4","GD3","GD2","GD1")) -- Regards, Tom Ogilvy "John" wrote in message ... Hi, I am working on an Office 2003 worksheet which determines a grade for users. Grades are based on scores and are graded as shown below. GD1 GD2 GD3 GD4 GD5 96.2 and above 89.3 and above 84.2 to 89.2 78.1 to 84.1 78.0 and below I have a total score for each person i.e. 86.8 which would put this person into GD3. How do I automatically populate a grade cell for each person based on there scores? Is an IIF function the best method here? if so how would I code this? Please help if possible. Kind Regards John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming |