Excel nested If statements

November 29, 2012 at 13:29:11
Specs: Windows XP
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")))))))))))


See More: Excel nested If statements

Report •


#1
November 29, 2012 at 13:46:24
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

http://www.skeptic.com/


Report •

#2
November 29, 2012 at 13:50:35
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?

Report •

#3
November 29, 2012 at 14:22:34
You can use a Defined Name as a work around,
see here:

http://www.cpearson.com/excel/neste...

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
November 29, 2012 at 15:14:49
Options include using a VLOOKUP() (suggested), or VBA.

How To Ask Questions The Smart Way


Report •

#5
November 29, 2012 at 15:28:05
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

http://www.skeptic.com/


Report •

#6
November 29, 2012 at 17:25:50
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.


Report •

#7
November 30, 2012 at 05:47:31
Thanks Mike,

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


Report •

#8
November 30, 2012 at 05:48:26
Thank you, a friend suggested VLOOKUP also. I have not used this before and am checking it out.

Report •


Ask Question