If Statement Unlimited In Excel

[SyntaxError]

I know that Excel states that you can only nest 7 If Statements. But I
had to make a formula that compared up to ten numbers.

After a few days of thinking I came up with this idea.

Where my formula was on the last (7th) of the IF STATEMENT, instead of
doing a calculation, I made it refer to another cell.

That particular cell then just continued my IF STATEMENTs until I had
all the calculations figured out.

By doing this you can actually have an unlimited amount of if
statement all rolled into one.

I hope this idea can help others out there. Just passing on a good
idea.

 

[Don Guillett]

Or put em in the same cell by using + between or use a lookup
table.

 

[Dave F]

More ideas here: http://www.ozgrid.com/Excel/nested-function-limit.htm

Dave

 

[Gary''s Student]

Very interesting. Can you post an example??

 

[salah]

i was researching this problem yesterday and i came across this link, hope
this helps too..

http://j-walk.com/ss/excel/usertips/tip080.htm
_______________________
salah

 

[SyntaxError]

Here's an example:

I have a MK number that could be 0 thru 16.
I have a CK number that could be 0 thru 16.

Now I also had lookup tables that would look up a number by comparing
the MK with the CK number.

Lets say this is my formula for CELL A1:

IF (MK=0), VLOOKUP(CK,TABLE0),
IF (MK=1), VLOOKUP(CK,TABLE1),
IF (MK=2), VLOOKUP(CK,TABLE2),
IF (MK=3), VLOOKUP(CK,TABLE3),
IF (MK=4), VLOOKUP(CK,TABLE4),
IF (MK=5), VLOOKUP(CK,TABLE5),
IF (MK=6), VLOOKUP(CK,TABLE6),
ELSE (A2)

Now CELL A2 would continue my formula:

IF (MK=6), VLOOKUP(CK,TABLE7),
IF (MK=7), VLOOKUP(CK,TABLE8),
IF (MK=8), VLOOKUP(CK,TABLE9),
IF (MK=9), VLOOKUP(CK,TABLE10),
IF (MK=10), VLOOKUP(CK,TABLE11),
IF (MK=11), VLOOKUP(CK,TABLE12),
IF (MK=12), VLOOKUP(CK,TABLE13),
ELSE (A3)

CELL A3 would continue my formula again:
IF (MK=13), VLOOKUP(CK,TABLE14),
IF (MK=14), VLOOKUP(CK,TABLE15),
ELSE VLOOKUP(CK,TABLE16),

With this method you should have more than enough and could continue
to your heart's content. Hope this is helpful...

SYNTAX ERROR!

 

[Harlan Grove]

Here's an example:

I have a MK number that could be 0 thru 16.
I have a CK number that could be 0 thru 16.

Now I also had lookup tables that would look up a number by
comparing the MK with the CK number.

Lets say this is my formula for CELL A1:

IF (MK=0), VLOOKUP(CK,TABLE0),
IF (MK=1), VLOOKUP(CK,TABLE1),
IF (MK=2), VLOOKUP(CK,TABLE2),
IF (MK=3), VLOOKUP(CK,TABLE3),
IF (MK=4), VLOOKUP(CK,TABLE4),
IF (MK=5), VLOOKUP(CK,TABLE5),
IF (MK=6), VLOOKUP(CK,TABLE6),
ELSE (A2)

Now CELL A2 would continue my formula:

IF (MK=6), VLOOKUP(CK,TABLE7),
IF (MK=7), VLOOKUP(CK,TABLE8),
IF (MK=8), VLOOKUP(CK,TABLE9),
IF (MK=9), VLOOKUP(CK,TABLE10),
IF (MK=10), VLOOKUP(CK,TABLE11),
IF (MK=11), VLOOKUP(CK,TABLE12),
IF (MK=12), VLOOKUP(CK,TABLE13),
ELSE (A3)

CELL A3 would continue my formula again:
IF (MK=13), VLOOKUP(CK,TABLE14),
IF (MK=14), VLOOKUP(CK,TABLE15),
ELSE VLOOKUP(CK,TABLE16),

With this method you should have more than enough and could
cntinue to your heart's content. Hope this is helpful...

If you could stand using volatile functions,



=IF((MK=INT(MK))*(MK>=0)*(MK<=16),VLOOKUP(CK,INDIRECT("TABLE"&MK),
....),"MK not in range")

Or if you want to limit your formulas to nonvolatile functions,

=IF((MK=INT(MK))*(MK>=0)*(MK<=16),VLOOKUP(CK,CHOOSE(MK,TABLE0,
TABLE1,TABLE2,TABLE3,TABLE4,TABLE5,TABLE6,TABLE7,TABLE8,TABLE9,
TABLE10,TABLE11,TABLE12,TABLE13,TABLE14,TABLE15,TABLE16),...),
"MK not in range")

 

[SyntaxError]

Well this program I made, I used the DATA VALIDATION to limit exactly
what I wanted the user the input. I made the list under DATA
VALIDATION. The other articles I've read that you guys suggested are
very interesting, and I have to study more. But the way I did it was
more practical in my situation, and could easily be debug any errors,
or if I had to recode my program.

THANKS FOR YOUR OTHER SUGGESTIONS....

Syntax Error!

 

[Gary''s Student]

Thanks for the example. An interesting alternative to the usual approaches