Using IF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lise - 26 May 2008 05:21 GMT

Hi

I'm using the following but shows as error yet if I only use a few it works
fine - what can I do to use all the cells I'm wanting? or should I be using a
completely different formula?

=IF(R5="None","0",IF(R5="Aqua BP","15",IF(R5="A/Tour
Desk",20,IF(R5="ATS",25,IF(R5="B/Free",15,IF(R5="Bris
Mark",10,IF(R5="CTM",20,IF(R5="Disc GC",20,IF(R5="GODO",20,IF(R5="GC
ticket",20,IF(R5="Infomaps",20,IF(R5="Is. Res",20,IF(R5="Last
Res",20,IF(R5="M/Cove",20,IF(R5="Marrakesh",20,IF(R5="Marriott
Actv",20,IF(R5="Navitour",10,IF(R5="Oasis TD",20,IF(R5="Oz
Horizon",10,IF(R5="Palace C",20,IF(R5="Palace
Travel",20,IF(R5="Ppans",20,IF(R5="Q.
Travel",20IF(R5="RACV",20,IF(R5="Raptis",20,IF(R5="Redballoon",20,IF(R5="Redcliffe Trav",20,IF(R5="Rendez",25,IF(R5="Shaftons",20,IF(R5="sta",25)))

Reply to this Message

T. Valko - 26 May 2008 05:39 GMT

Use a lookup formula. See this:

http://contextures.com/xlFunctions02.html

Signature

Biff
Microsoft Excel MVP

> Hi
>
[quoted text clipped - 14 lines]
> Travel",20IF(R5="RACV",20,IF(R5="Raptis",20,IF(R5="Redballoon",20,IF(R5="Redcliffe
> Trav",20,IF(R5="Rendez",25,IF(R5="Shaftons",20,IF(R5="sta",25)))

Reply to this Message

Rick Rothstein (MVP - VB) - 26 May 2008 06:41 GMT

You can only nest functions 7 deep... you went way beyond that. The LOOKUP
function can be used instead...

=LOOKUP(R5,{"None","Aqua BP","A/Tour Desk","ATS","B/Free","Bris
Mark","CTM","Disc GC","GODO","GC ticket","Infomaps","Is. Res","Last
Res","M/Cove","Marrakesh","Marriott Actv","Navitour","Oasis TD","Oz
Horizon","Palace C","Palace Travel","Ppans","Q.
Travel","RACV","Raptis","Redballoon","Redcliffe
Trav","Rendez","Shaftons","sta"},{0,15,20,25,15,10,20,20,20,20,20,20,20,20,20,20,10,20,10,20,20,20,20,20,20,20,20,25,20,25})

The other possibility, since you have less than 7 possible result values, is
to group the IF tests by like result values using the OR function...

=IF(R5=0,0,IF(OR(R5={"Bris Mark","Navitour","Oz
Horizon"}),10,IF(OR(R5={"Aqua BP","B/Free"}),15,IF(OR(R5={"A/Tour
Desk","CTM","Disc GC","GODO","GC ticket","Infomaps","Is. Res","Last
Res","M/Cove","Marrakesh","Marriott Actv","Oasis TD","Palace C","Palace
Travel","Ppans","Q. Travel","RACV","Raptis","Redballoon","Redcliffe
Trav","Shaftons"}),20,IF(OR(R5={"ATS","Rendez","sta"}),25,"")))))

Rick

> Hi
>
[quoted text clipped - 14 lines]
> Travel",20IF(R5="RACV",20,IF(R5="Raptis",20,IF(R5="Redballoon",20,IF(R5="Redcliffe
> Trav",20,IF(R5="Rendez",25,IF(R5="Shaftons",20,IF(R5="sta",25)))

Reply to this Message