I am unfamiliar with "complex" formulas in Excel. I am trying to create a nested if statement that has 11 possible results. my original data is numerical and my formula is to convert that number to a 'bracket" example: E14= 400000 result Up to 1 million etc. here is what I have been able to come up with to no avail. Any help would be greatly appreciated.

IF(E14<=1000000,"Up to 1 Million",IF(AND(E14>1000000,E14<1500000),"1 - 1.5 Million",IF(AND(E14>1500000,E14<2000000),"1.5 - 2 Million",IF(AND(E14>2000000,E14<2500000),"2 - 2.5 Million",IF(AND(E14>2500000,E14<5000000),"2.5 - 5 Million",IF(AND(E14>5000000,E14<20000000),"5 - 20 Million",IF(AND(E14>20000000,E14<40000000),"20 - 40 Million",IF(AND(E14>40000000,E14<75000000),"50 - 75 Million",IF(AND(E14>75000000,E14<100000000),"75 - 100 Million",IF(AND(E14>100000000,E14<150000000),"100 - 150 Million",IF(E14>150000000,"Over 150 Million")))))))))))

What version of Excel are you using? In 2000 & 2003 you are limited to 7 nested IF() functions

2007 gives you 64, I think.MIKE

Thanks Mike,

My office is behind the times and we use 2000. I was afraid I may be over the limit. Do you know if there is a way around this besides trying to get my company to upgrade?

Also, in your formula you really don't need the AND() functions. What you can do, is start you checks with the largest number

then cascade down to the smallest,

so your formula would look something like:=IF(E14>150000000,"Over 150 Million",

IF(E14>100000000,"100 - 150 Million",

IF(E14>75000000,"75 - 100 Million",

IF(E14>40000000,"50 - 75 Million",

IF(E14>20000000,"20 - 40 Million",

IF(E14>50000000,"5 - 20 Million",

IF(E14>2500000,"2.5 - 5 Million",

IF(E14>2000000,"2 - 2.5 Million",

IF(E14>1500000,"1.5 - 2 Million",

IF(E14>1000000,"1 - 1.5 Million","Up to 1 Million"))))))))))

MIKE

I would use VLOOKUP with the range_lookup argument set to 1 (TRUE).

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks Mike, I appreciate the advice. I am checking out the link you attached previously.

Thank you, a friend suggested VLOOKUP also. I have not used this before and am checking it out.

Ask Your Question

Weekly Poll

Do you think the newest iPhone will have a 3.5mm headphone jack?

Discuss in The Lounge

Poll History