Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More than 7 IF? any substitute?
Hey!
looks like excel 2003 limits nested IF for like 10, and i need to use IF(IF(.....) 15 times. Do you guys know what it could be a substitute, excel says use Lookup but my data is in a drop down list build by Validation Data, any help is really welcomed! Thanks a lot!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More than 7 IF? any substitute?
Jean:
One way is to split the function up into 2 or three parts like: =if(a1=1,10,if(a1=2,20),0)+if(a1=3,30,if(a1=4,40), 0)+if(a1=5,50,if(a1=6,60),0) or use a macro function as in function ans(a) select case a case 1 ans=10 case 2 ans=20 case 3 ans=30 case 4 ans=40 case 5 ans=50 case 6 ans=60 case else ans=0 end select end function and in the worksheet put =ans(1) -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jean" wrote: Hey! looks like excel 2003 limits nested IF for like 10, and i need to use IF(IF(.....) 15 times. Do you guys know what it could be a substitute, excel says use Lookup but my data is in a drop down list build by Validation Data, any help is really welcomed! Thanks a lot!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More than 7 IF? any substitute?
Jean wrote...
looks like excel 2003 limits nested IF for like 10, and i need to use IF(IF(.....) 15 times. Do you guys know what it could be a substitute, excel says use Lookup but my data is in a drop down list build by Validation Data, Unclear, but lookup is still the most likely way to accomplish this. Hard to say for sure without more details from you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More than 7 IF? any substitute?
There should never be any occasion where you have to use more than 7 nested Ifs.
There are always much cleaner solutions, typically using Vlookup. Post your If statements, and I'm sure you'll get some good suggestions. -- Regards, Fred "Jean" wrote in message ... Hey! looks like excel 2003 limits nested IF for like 10, and i need to use IF(IF(.....) 15 times. Do you guys know what it could be a substitute, excel says use Lookup but my data is in a drop down list build by Validation Data, any help is really welcomed! Thanks a lot!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More than 7 IF? any substitute?
"Fred Smith" wrote...
There should never be any occasion where you have to use more than 7 nested Ifs. There are always much cleaner solutions, typically using Vlookup. .... Never? =IF(Type="Animal", IF(Vertibrate, IF(WarmBlooded, IF(Mammal, IF(Carnivore, IF(LargerThanHumans, IF(Feline, IF(African, IF(Solitary, "Leopard", .... Not that you'd want to play 20 questions in Excel, but there are hierarchically structured data that exceed 8 levels. Locating members in such hierarchies would require more than 7 nested IFs. There are work-arounds, but they're not necessarily cleaner. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Substitute/Replace | Charts and Charting in Excel | |||
SUBSTITUTE | Excel Worksheet Functions | |||
Using &Chr$(39)& as substitute for ' in VBA | Excel Discussion (Misc queries) | |||
substitute for = | Excel Worksheet Functions | |||
substitute | Excel Worksheet Functions |