nested If statement

[LOSS1574]

I'm lost trying to figure out the best way to get the following code to work. I know you can not have more then 7 nested if statements. I've tried using lookup,choose, and define named ranges to no avail. Please help :)


=IF(AND(C5>=1,C5<=10),"MUM",IF(AND(C5>=11,C5<=19),"IUM",IF(AND(C5>=20,C5<=27),"EUM",IF(AND(C5>=28,C5<=34),"XUM",IF(AND(C5>=34,C5<=40),"BV1",IF(AND(C5>=41,C5<=45),"BV2",IF(AND(C5>=46,C5<=49),"BV3"))))))), IF(AND(C5>=50,C5<=52),"VER1",IF(AND(C5>=53,C5<=54),"VER2" IF(AND(C5>=55,C5<=55),"VER3"))))))))))

[grichey]

Just break it up into two cells with the second depended on the first and hide it.

[LOSS1574]

Thanks for the reply.. How do I tie the second to be depended on the first?

[LOSS1574]

Thanks I used a simple if statement to tie the second to the first.

[grichey]

You could do this w/ cases in vba but anyways, here it is in formulas.

Note: this will only work if you're number going in C5 is a number between 1 and 55 otherwise result in F5 will show "NEXT1"

Columns C:D are hidden. Open them to see formulas. This is what I meant by breaking it up.

[grichey]

post what you ended up doing... if you don't mind. I'm curious what method you used..

[JimmyTheHand]

You can also use other approaches, that are different from nested IF formulas.
For eaxmple, a UDF could help.

Also, in this particular case, you can replace the original nested IF-s with this formula (I hope I translated it OK : pray2:)


=TRIM(MID("MUM IUM EUM XUM BV1 BV2 BV3 VER1VER2VER3",INT((21-SQRT(441-8*(C5-1)))/2)*4+1,4))


Jimmy

[Bob Phillips]

=IF(C5<1,"",LOOKUP(C5,{1,11,21,28,35,41,46,50,53,55},{"MUM","IUM","EUM","XUM","BVI","BV2","BV3","VER1","VER2","VER3"}))

[JimmyTheHand]

=IF(C5<1,"",LOOKUP(C5,{1,11,21,28,35,41,46,50,53,55},{"MUM","IUM","EUM","XUM","BVI","BV2","BV3","VER1","VER2","VER3"}))
Incredible. :bow:

[grichey]

=IF(C5<1,"",LOOKUP(C5,{1,11,21,28,35,41,46,50,53,55},{"MUM","IUM","EUM","XUM","BVI","BV2","BV3","VER1","VER2","VER3"}))


I think in his original, VER3 was for 55 only. Nonetheless, I've learned something new.

What does the {} signify?

[Bob Phillips]

What does the {} signify?

It tells the formula that you are passing an array of values.

[Bob Phillips]

I think in his original, VER3 was for 55 only. Nonetheless, I've learned something new.

You are right, so we need to incorporate a test. That test can be embedded in the LOOKUP, as can the test for <1, so it simplifies down to

=LOOKUP(C5,{-9999,1,11,21,28,35,41,46,50,53,55,56},
{"","MUM","IUM","EUM","XU M","BVI","BV2","BV3","VER1","VER2","VER3",""})

[grichey]

ah cool. thx for the info.