sms 12:44 25 Feb 2007

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.

  VoG II 12:48 25 Feb 2007

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.


What are you trying to do?

  sms 13:02 25 Feb 2007

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 2007

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 2007

Workaround click here

  Simsy 20:47 27 Feb 2007

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"



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

Elsewhere on IDG sites

Intel Coffee Lake release date and specifications

12 Amazing British Craft Beer Label Designs

watchOS 4 review

Les meilleurs navigateurs internet 2017