CODEWITHSUNDEEP
excelvba
Marat_Muginov
Marat_Muginov

Reputation: 62

Is there a method for shortening nested If...Then...Else statements?

I am attempting to create custom Excel functions to reduce menial mental tasks in my department.

The function works but freezes up 32-bit Excel for over a minute. For reference, the average workload is >10,000 rows of inputs. 

Function Function_Name(Input)
If Val(Input) = 0 Then
    Function_Name = Input
    ElseIf Val(Input) = 1 Then Function_Name = Input
    ElseIf Val(Input) >= 1110 And Val(Input) <= 1999 Then Function_Name = "1110"
ElseIf Val(Input) >= 3100 And Val(Input) <= 3199 Then Function_Name = "3100"
'Similar ElseIf statements go on for 40+ lines......'
ElseIf Val(Input) >= 5700 And Val(Input) <= 5799 Then Function_Name = "5710"
'Note that they do not always return the lower limit of Val(Input), but sometimes may'
End If
End Function

The function provides accurate outputs, however takes long to process. I would like to know if there is a way to improve the back-end logic to reduce risk of Excel crashes and streamline processing.

Upvotes: 0

Views: 106

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

Scrap the whole function, add a new sheet (hide it if you must), type "Threshold" in A1, "Value" in A2, then begin populating your lookup table (Ctrl+T) to make it an actual table, then name it, e.g. refTable):

Threshold   Value
        0   =NA()
     1100   1100
     1200   1200
     1300   1300
      ...   ...

Now wherever you were using that function (where B2 is your current "input"):

=IFERROR(VLOOKUP(refTable[[Threshold]:[Value]], B2, TRUE), B2)

The TRUE final argument makes the function match the largest value in column 1 that isn't greater than the lookup value, so 4256 would match the 4200 threshold, stopping short of 4300.

This assumes the lookup values aren't calculatable with simple math (i.e. that the 5710 result isn't a typo). Otherwise, there's an easy an much better-performing alternative:

=IF(B2>=1100,INT(B2/100)*100,B2)

If the input is greater than or equal to 1100, take the integer value of this input divided by 100, multiply that by 100 (there's probably a way to use a rounding function for that as well); otherwise just return the input. That should calculate pretty much instantly.

Upvotes: 1

Related Questions