nest if statements with more than 7 conditions

[553]

I have a worksheet called benefits with a column Monthly Contribution. I need to bring in the employeer contribution by writing the statement below.
I was trying to write an if statement but I can only have 7 levels. How do I do this in Visual Basic.
My conditions I was using in Excel is as follows:

=IF(I2=304.99,652.05,
IF(AND(I2=84.14),"210.34",IF(AND(I2=84.13),"210.34",
IF(AND(I2=210.34),"462.74",IF(AND(I2=210.33),"462.74",
IF(AND(I2=168.26),"378.60",IF(AND(I2=305.00),"652.05",
IF(AND(I2=114.26),"285.62",IF(AND(I2=114.25),"285.62",
IF(AND(I2=285.62),"628.35",IF(AND(I2=285.61),"628.35",
IF(AND(I2=414.14),"885.40",IF(AND(I2=102.16),"255.41",
IF(AND(I2=255.42),"561.92",IF(AND(I2=204.34),"459.75",
IF(AND(I2=370.36),"791.79",IF(AND(I2=228.50),"514.10",
IF(AND(I2=228.49),"514.10")))))))))))))))))

 

[PHV]

Something like this:
Select Case varI2
Case 304.99: Result = 652.05
Case 84.14, 84.13: Result = 210.34
Case 210.34, 210.33: Result = 462.74
Case 168.26: Result = 378.60
Case 305.00: Result = 652.05
Case 114.26, 114.25: Result = 285.62
...
Case 228.50, 228.49: Result = 514.10
End Select

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

If you're looking for a worksheet function, try something like this ..

=LOOKUP(I2,B4:B21,C4:C21)

Where a list in B4:B21 equals ..

84.13
84.14
102.16
114.25
114.26
168.26
204.34
210.33
210.34
228.49
228.50
255.42
285.61
285.62
304.99
305.00
370.36
414.14

And the list in C4:C21 equals ..

210.34
210.34
255.41
285.62
285.62
378.60
459.75
462.74
462.74
514.10
514.10
561.92
328.35
328.35
652.05
652.05
791.79
885.40

It's generally not a good idea to max out the nested IFs if you can. I know that the new Excel (2007) will have up to 64 nesting possibilities (why, I have no idea), but that doesn't mean it's going to be efficient.

-----------
Regards,
Zack Barresse

 

[macropod]

Hi 553,

If you don't want a lookup table, you could use:
=(I2=304.99)*652.05+OR(I2=84.13,I2=84.14)*210.34+OR(I2=210.33,I2=210.34)*462.74+(I2=168.26)*378.6+(I2=305)*652.05+OR(I2=114.25,I2=114.26)*285.62+OR(I2=285.61,I2=285.62)*628.35+(I2=414.14)*885.4+(I2=102.16)*255.41+(I2=255.42)*561.92+(I2=204.34)*459.75+(I2=370.36)*791.79+OR(I2=228.49,I2=228.5)*514.1

Note: Note an IF or AND function in sight - your AND functions weren't actually doing anything useful.

Cheers

[MS MVP - Word]