IF Statement Limitations

[Imran Malik]

Hi All
I need help regarding IF statement , the case I am feeling problem is that,
there are about 23 conditions to be connected with IF loop, while excel does
not allow, it, how can i solve it
sincerely

 

[Imran Malik]

The situation is
if(value is C5,'rate M11............and so on.
i-e for a single cell there are many values about 23, to be displayed
depending what we need, in that cell
thanks

 

[Anon]

The situation is
if(value is C5,'rate M11............and so on.
i-e for a single cell there are many values about 23, to be displayed
depending what we need, in that cell
thanks
excel

Make a little table somewhere of the possible values and their corresponding
rates - say in Sheet2!A1:B23.
Then you can use this formula:
=VLOOKUP(C5,Sheet2!A1:B23,2,0)
This will take whatever value you have in C5, look it up in the leftmost
column of the table (i.e. A1:A23) and return the corresponding value from
the second column (i.e. B1:B23).
A lot simpler and easier to understand than 23 IFs!

 

[John Tjia]

Maybe the CHOOSE function can help here. You can list up to 29
alternatives (if I am not wrong) with CHOOSE.

 

[Earle]

You might try using the Select Case function - its almost
limitless. Select Case will work with either Numeric or
Text (String) data types.

Function Discount(Qty As Integer) As Single
Select Case Qty
Case Is <5
Discount = 0

Case 6 to 10
Discount = .025

Case 11 to 24
Discount = .1

Case Else
Discount = .125
End Select
End Function

or

Function ZipCode(State As String) As String
Select Case State
Case < "C"
ZipCode = "Who Cares"

Case = "Massachusetts"
ZipCode = "MA"

Case Else
ZipCode = "* * E R R O R * *"
End Select
End Function

Any number of instructions can be created below each Case
test, and those instructions will execute if the test
returns True.

Structure of test can be like:
Case Is < 0

Case 4

Case 4 To 11

Case 5 To 11, 14, 23, 37, 45, 46, 52 To 99
(Note that you must have these in numeric order left-to-
right and same is for Strings, alphabetically left-to-
right)

Case Is > 15
(This test would only activate (be True) if the number
fed into the Select Case is greater than 15 but not 23,
37, 45, 46, 52 to 99 of the immediate above test.)


To make this run a bit faster place your most likely True
conditions (tests) first. VBA starts at the top looking
for a match.

ALWAYS build in a Case Else as the last test just in case
something is wrong with your code OR you want a "just in
case nothing matches or exceeds" as I showed above in the
Discount example where Cas Else Discount = .125