Setting ranges for comparison operators

Hi everyone,

Is it possible to set a range using comparison operators? ie. if a number lies within x and y then such and such happens.

To be more precise:

Function grade(maths As Double, english As Double, science As Double) As String

If maths >= 70 And english >= 70 And science >= 70 Then
    grade = "Distinction"

ElseIf maths >= 70 And english >= 50 And science >= 50 Then
    grade = "Merit"

ElseIf maths < 50 Or english < 50 Or science < 50 Then
    grade = "Fail"
    
End If
End Function

Focusing on the red text, I was hoping to set english and science so that they are also < 70 (lie between 50 - 69).

Thanks for your time!

Not sure of the logic you're trying to apply. Maybe this:

Function grade(maths As Double, english As Double, science As Double) As String
    Dim dMin As Double
    Dim dMax As Double
    
    dMin = WorksheetFunction.Min(maths, english, science)
    dMax = WorksheetFunction.Max(maths, english, science)
    
    If dMin >= 70 Then
        grade = "Distinction"
    ElseIf dMax >= 70 And dMin >= 50 Then
        grade = "Merit"
    ElseIf dMin < 50 Then
        grade = "Fail"
    Else
        grade = "???"
    End If
End Function

hi Dems,

I think you have given the answer to your own question in your example (once corrected by changing the red "70" to a "50")...?

Here's a very slightly alternative approach to your function, using Long for the arguments which may be slightly faster for large data sets, because I think that Double is "overkill". Note that the order of the tests in the function means that the range of 50-69 is already "defined" within the second Case, because the arguments have failed the initial test (ie ">=70") but do pass the ">50" test.

Function grade(maths As Long, english As Long, science As Long) As String
'alternative #1
Select Case True
    Case maths >= 70 And english >= 70 And science >= 70
        grade = "Distinction"
    Case maths >= 50 And english >= 50 And science >= 50
        grade = "Merit"
    Case maths < 50 Or english < 50 Or science < 50
        grade = "Fail"
End Select

'alternative #2
'Using slightly different rules for the grading which _
allows high grades in one subject to pull up the _
overall mark (provided all subjects are >50).
Dim TtlOfMarks As Long
TtlOfMarks = maths + english + science
Select Case True
    Case maths < 50 Or english < 50 Or science < 50
        grade = "Fail"
    Case TtlOfMarks >= 210 'ie 70x3
        grade = "Distinction"
    Case TtlOfMarks >= 150
        grade = "Merit"
End Select
End Function
End Function

If you need more help let us know

hth
Rob

Shg, you beat me to it again - good to see that I'm starting to think similarly to yourself though

Although, I do like your use of the .min function because this would mean changes would only need to be made in the Function's arguments & the .min line of code if (or when) the function is modified to allow for more subjects.

Rob

I dunno, Rob, don't know what the OP is trying to do. Many roads lead to Rome.

I'm pleased we could help - thanks for the feedback

Rob