Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using IF
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))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using IF
Use a lookup formula. See this:
http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "Lise" wrote in message ... 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))) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using IF
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,2 0,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","st a"}),25,""))))) Rick "Lise" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|