Exel

  sms 12:44 25 Feb 07
Locked

I am using the IF function in a formula that needs more than 10 IF's. However i am restricted in the number it will allow me to enter. Is there any way to get more room to increase this number or is there a better way to do it.
Thanks

  VoG II 12:48 25 Feb 07

In Excel up to version 2003 you are limited to 7 levels of nesting in an IF statement. I believe that this has been increased to 50 (!) in Excel 2007.

Alternatives are to use CHOOSE or possibly LOOKUP e.g.

=LOOKUP(C16,{1,"John";2,"Jeff";3,"Albert";4,"Gary";5,"Brian"})

What are you trying to do?

  sms 13:02 25 Feb 07

I have a bus route than runs every hour for up to 7 hours.each route has 15 bus stops and I am setting up a bus booking table that enetre the people to be picked up at each bus stop for that route.
I am using vlookup to check the bus route with the pick up but have run out of if options

  VoG II 13:15 25 Feb 07

I suggest that you post on MrExcel click here where you can post part of your sheet using click here

  VoG II 17:01 27 Feb 07

Workaround click here

  Simsy 20:47 27 Feb 07

the answer to your problem... however, the limit of 7 "ifs" is not quite what it seems...

I have a formula, (in an Excel 2000 spreadsheet), that has 13 "ifs"... and it works perfectly.

click here

I'm not suggesting that it's the best way to achieve what I was after... but it is proof that the limit in a formula is not 7!

If you look at my link you will see that, though the formula contains more than 7, Excel never needs to actually evaluate more than 7 of them. I think this is the limit... 7 EVALUATIONS of "ifs".

It MAY be possible, (though probably not the best approach!!), to structure your formula such that you can have 15 "ifs"

Regards,

Simsy

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

New Corel ParticleShop plugin now available: 11 new brushes & 6 new brush packs

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…