VBA function to replace nested IFs

[myemail.an]

Hi all,

I am working on an Excel template which calculates specific values
based on the bucket to which certain parameters belong. The rules are
something like:

if Param1 <= 3 Then Output = 4
if 3 < Param1 <= 7 Then Output = 6
if 7 < Param1 <= 11 Then Output = 9
if Param1 > 11 then output = 0

I have to calculate this for about 20 parameters, and for each
parameter there can be up to 12 possible outputs.

I started doing this with nested IF formulas, but it's not a very
efficient solution: not too straightforward to write, test and audit.
I could write a specific VBA function for each parameter: it would be
easier to read than nested Ifs, but not ideal: I'd like to write a
generic VBA function that takes 3 inputs:
1) Param1
2) an array of thresholds
3) an array of associated outputs

The problem is, the number of elements in the array is not fixed but
variable. Paramarray allows me to pass one vector of variable
dimension as argument to a function, but I need to use 2 arrays of
variable dimension.

Any ideas? Any help would be greatly appreciated!

Thanks!

 

[Joel]

Function bucket(myvalue)

Select Case myvalue
Case Is <= 3
bucket = 4
Case 4 To 7
bucket = 6
Case 8 To 11
bucket = 9
Case Is > 11

bucket = 0

End Select

 

[Mike H]

Hi,

If i've understood correctly there may be a method that avoids the use of VB
utilising a table that could look like this:-

0 4
5 5
10 6
15 7
10 8

The left column are parameters and the right column outputs. Note the column
is sorted on column A. Column B need not be sorted. A formula

=VLOOKUP(C1,A1:B5,2,TRUE)

Looks for the parameter in C1 in column A and returns the corresponding
output from column B. So, 0,1,2,3,4 in C1 return 4 and it's only when C1
changes to 5 that it returns 5 etc.

Mike

 

[Mike H]

Well column A would be sorted if it didn't have a typo

 

[Joel]

You are correct!

Hi,

If i've understood correctly there may be a method that avoids the use of VB
utilising a table that could look like this:-

0 4
5 5
10 6
15 7
10 8

The left column are parameters and the right column outputs. Note the column
is sorted on column A. Column B need not be sorted. A formula

=VLOOKUP(C1,A1:B5,2,TRUE)

Looks for the parameter in C1 in column A and returns the corresponding
output from column B. So, 0,1,2,3,4 in C1 return 4 and it's only when C1
changes to 5 that it returns 5 etc.

Mike

 

[Mike H]

Thanks Joel but I'm still not convinced I've understood the OP's logic, we'll
see

 

[Arvi Laanemets]

Hi

Another way using worksheet functions only is to combine CHOOSE and MATCH.

=CHOOSE(MATCH(A1,{-999999;3.0001;7.0001;11.0001},1),4,6,9,0)

You can have 29 different responses to choose between to.

 

[myemail.an]

I had thought of that but forgot to mention it. The drawback is that I
need another worksheet, and 2 columns per parameters, to set it up.
a VBA function would be more convenient. But thanks for the tip
anyway!

 

[myemail.an]

I'm sorry, I guess I didn't word my question too well.
When I said I'd like to avoid nested IFs, I actually meant that I'd
like to avoid nested IFs as worksheet functions, i.e. I want to avoid
writing a worksheet function like:

=IF(CT4>0,IF(CT4>3,IF(CT4>5,IF(CT4>7,IF(CT4>10,15,12),7),3),2),-5)

Writing a VBA function with nested IFs or nested Select Case clauses
would be fine; however, since I have over 20 parameters, and the
thresholds change for each of those, I'd have to write over 20. It
would be ideal if I could write just one VBA function, and then use it
for all the 20+ parameters I have to work with. Something like:

ClassificationFunction(Inputvalue,
Threshold1,Threshold2,Threshold3,Output1, Output2,Output3,Output4)

The problem is that the number of thresholds is different for every
parameter. Is there a way to generalize the function?