If, Elseif, Else statements for a UDF

[billj]

Hello everyone,

I wanted to converted numeric values in a column to rating scales from 0 - 10. The scheme is:

ER < -10.0% 0
-10.0% <= ER < -5.0% 1
-5.0% <= ER < -2.0% 2
-2.0% <= ER < -0.5% 3
-0.5% <= ER < -0.1% 4
-0.1% <= ER < 0.1% 5
0.1% <= ER < 0.5% 6
0.5% <= ER < 2.0% 7
2.0% <= ER < 5.0% 8
5.0% <= ER < 10.0% 9
ER >= 10.0% 10

Please Login or Register  to view this content.

But is there a better way to define this UDF for my purpose? I feel the If, Elseif, Else statements are not that tidy at all.

Many thanks in advance.

[Arkadi]

I'd go with Select Case rather than if, eleseif... always strikes me as easier to read or maintain, but I can't say it would make much difference in performance, given that the options are not that many. An If statement (as far as I know) gets fully evaluated, whereas the Case method would stop evaluating once it reaches the "true" condition.

[MrShorty]

Your example shows a basic "lookup table" kind of thing, so I might set up a lookup table kind of thing in VBA.
1) Assign array of lookup values (rank divisions) to an array.
2) Use one of Excel's lookup functions (I would use Application.WorksheetFunction.Match()) to find the position of rcell in the array.
3) (optional) assign an array of return values to an array (if you ever want to use something other than position in array for the ranking).
4) Use the result of 2 to return the appropriate value from the array created in 3.

A quick and incomplete function I put together

Please Login or Register  to view this content.

[PCI]

Try

Please Login or Register  to view this content.

[xladept]

Another:

Please Login or Register  to view this content.

[billj]

Many thanks, everyone. They are much better codes than my basic IF ... ELSE ones. They are really useful to me. Cheers.