Exel

sms 12:44 25 Feb 2007
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 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.

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

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

VoG II 17:01 27 Feb 2007

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.

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.

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