PDA

View Full Version : nested If statement

LOSS1574
05-28-2008, 11:33 AM
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
05-28-2008, 11:37 AM
Just break it up into two cells with the second depended on the first and hide it.

LOSS1574
05-28-2008, 11:46 AM
Thanks for the reply.. How do I tie the second to be depended on the first?

LOSS1574
05-28-2008, 11:50 AM
Thanks I used a simple if statement to tie the second to the first.

grichey
05-28-2008, 12:41 PM
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
05-28-2008, 12:42 PM
post what you ended up doing... if you don't mind. I'm curious what method you used..

JimmyTheHand
05-29-2008, 01:42 AM
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
05-29-2008, 02:41 AM
=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
05-29-2008, 02:50 AM
=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
05-29-2008, 04:43 AM
=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
05-29-2008, 05:54 AM
What does the {} signify?

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

Bob Phillips
05-29-2008, 05:58 AM
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
05-29-2008, 10:43 AM
ah cool. thx for the info.