| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A formula that is too complex for Excel?
Okay, I have written longer formulas than this and what I thought was more complex, but for some reason excel says that there is an error in this formula! I have checked it over and over and there are no syntax errors.... what is the problem? Code: -------------------- =IF(IF(AND(IF(C2+E2=1000,TRUE,FALSE),IF(B2+D2=10 0,TRUE,FALSE)),TRUE,FALSE)=FALSE,"none",IF(IF(AND( IF(C2+E2=2000,TRUE,FALSE),IF(B2+D2=200,TRUE,FALS E)),TRUE,FALSE)=FALSE,"Bronze",IF(IF(AND(IF(C2+E2 =3000,TRUE,FALSE),IF(B2+D2=300,TRUE,FALSE)),TRUE, FALSE)=FALSE,"Silver",IF(IF(AND(IF(C2+E2=4000,TRU E,FALSE),IF(B2+D2=400,TRUE,FALSE)),TRUE,FALSE)=FA LSE,"Gold",IF(IF(AND(IF(C2+E2=5000,TRUE,FALSE),IF (B2+D2=500,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Platnu m",IF(IF(AND(IF(C2+E2=6000,TRUE,FALSE),IF(B2+D2= 600,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Diamond",IF(IF (AND(IF(C2+E2=7000,TRUE,FALSE),IF(B2+D2=700,TRUE ,FALSE)),TRUE,FALSE)=FALSE,"MoonRock","CarbonNanoT ubes"))))))) -------------------- -- trex005 ------------------------------------------------------------------------ trex005's Profile: http://www.excelforum.com/member.php...o&userid=34724 View this thread: http://www.excelforum.com/showthread...hreadid=544888 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
what is the problem?
You've exceeded the nested function limit of 7. Looks like you have 9. Try explaining what you're wanting to do. Biff "trex005" wrote in message ... Okay, I have written longer formulas than this and what I thought was more complex, but for some reason excel says that there is an error in this formula! I have checked it over and over and there are no syntax errors.... what is the problem? Code: -------------------- =IF(IF(AND(IF(C2+E2=1000,TRUE,FALSE),IF(B2+D2=10 0,TRUE,FALSE)),TRUE,FALSE)=FALSE,"none",IF(IF(AND( IF(C2+E2=2000,TRUE,FALSE),IF(B2+D2=200,TRUE,FALS E)),TRUE,FALSE)=FALSE,"Bronze",IF(IF(AND(IF(C2+E2 =3000,TRUE,FALSE),IF(B2+D2=300,TRUE,FALSE)),TRUE, FALSE)=FALSE,"Silver",IF(IF(AND(IF(C2+E2=4000,TRU E,FALSE),IF(B2+D2=400,TRUE,FALSE)),TRUE,FALSE)=FA LSE,"Gold",IF(IF(AND(IF(C2+E2=5000,TRUE,FALSE),IF (B2+D2=500,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Platnu m",IF(IF(AND(IF(C2+E2=6000,TRUE,FALSE),IF(B2+D2= 600,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Diamond",IF(IF (AND(IF(C2+E2=7000,TRUE,FALSE),IF(B2+D2=700,TRUE ,FALSE)),TRUE,FALSE)=FALSE,"MoonRock","CarbonNanoT ubes"))))))) -------------------- -- trex005 ------------------------------------------------------------------------ trex005's Profile: http://www.excelforum.com/member.php...o&userid=34724 View this thread: http://www.excelforum.com/showthread...hreadid=544888 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
=IF(OR(C2+E2<1000,B2+D2<100),"none",
IF(OR(C2+E2<2000,B2+D2<200),"Bronze", IF(OR(C2+E2<3000,B2+D2<300),"Silver", IF(OR(C2+E2<4000,B2+D2<400),"Gold", IF(OR(C2+E2<5000,B2+D2<500),"Platnum", IF(OR(C2+E2<6000,B2+D2<600),"Diamond", IF(OR(C2+E2<7000,B2+D2<700),"MoonRock","CarbonNano Tubes"))))))) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "trex005" wrote in message ... Okay, I have written longer formulas than this and what I thought was more complex, but for some reason excel says that there is an error in this formula! I have checked it over and over and there are no syntax errors.... what is the problem? Code: -------------------- =IF(IF(AND(IF(C2+E2=1000,TRUE,FALSE),IF(B2+D2=10 0,TRUE,FALSE)),TRUE,FALSE) =FALSE,"none",IF(IF(AND(IF(C2+E2=2000,TRUE,FALSE) ,IF(B2+D2=200,TRUE,FALSE) ),TRUE,FALSE)=FALSE,"Bronze",IF(IF(AND(IF(C2+E2=3 000,TRUE,FALSE),IF(B2+D2= 300,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Silver",IF(IF( AND(IF(C2+E2=4000,TRUE,FA LSE),IF(B2+D2=400,TRUE,FALSE)),TRUE,FALSE)=FALSE, "Gold",IF(IF(AND(IF(C2+E2 =5000,TRUE,FALSE),IF(B2+D2=500,TRUE,FALSE)),TRUE, FALSE)=FALSE,"Platnum",IF( IF(AND(IF(C2+E2=6000,TRUE,FALSE),IF(B2+D2=600,TR UE,FALSE)),TRUE,FALSE)=FAL SE,"Diamond",IF(IF(AND(IF(C2+E2=7000,TRUE,FALSE), IF(B2+D2=700,TRUE,FALSE)) ,TRUE,FALSE)=FALSE,"MoonRock","CarbonNanoTubes"))) )))) -------------------- -- trex005 ------------------------------------------------------------------------ trex005's Profile: http://www.excelforum.com/member.php...o&userid=34724 View this thread: http://www.excelforum.com/showthread...hreadid=544888 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
Excel has a limit of 7 nested if statements. It looks like you have at least 9 -- bgeier ------------------------------------------------------------------------ bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822 View this thread: http://www.excelforum.com/showthread...hreadid=544888 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
There's a limit of 7 nested functions in Excel, I think you've exceeded that. At least one of your IF functions is superfluous....and all the TRUEs and FALSEs are unnecessary. Try this =IF((C2+E2=7000)*(B2+D2=700),"CarbonNanoTubes",I F((C2+E2=6000)*(B2+D2=600),"MoonRock",IF((C2+E2 =5000)*(B2+D2=500),"Diamond",IF((C2+E2=4000)*(B2 +D2=400),"Platinum",IF((C2+E2=3000)*(B2+D2=300) ,"Gold",IF((C2+E2=2000)*(B2+D2=200),"Silver",IF( (C2+E2=1000)*(B2+D2=100),"Bronze","None"))))))) ....although you could probably simplify further using a different approach, i.e. =CHOOSE(MIN(MATCH((C2+E2)/1000,{0,1,2,3,4,5,6,7}),MATCH((B2+D2)/100,{0,1,2,3,4,5,6,7})),"None","Bronze","Silver"," Gold","Platinum","Diamond","MoonRock","CarbonNanoT ubes") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=544888 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
"trex005" wrote in
message ... Okay, I have written longer formulas than this and what I thought was more complex, but for some reason excel says that there is an error in this formula! I have checked it over and over and there are no syntax errors.... what is the problem? Code: -------------------- =IF(IF(AND(IF(C2+E2=1000,TRUE,FALSE),IF(B2+D2=10 0,TRUE,FALSE)),TRUE,FALSE)=FALSE,"none",IF(IF(AND( IF(C2+E2=2000,TRUE,FALSE),IF(B2+D2=200,TRUE,FALS E)),TRUE,FALSE)=FALSE,"Bronze",IF(IF(AND(IF(C2+E2 =3000,TRUE,FALSE),IF(B2+D2=300,TRUE,FALSE)),TRUE, FALSE)=FALSE,"Silver",IF(IF(AND(IF(C2+E2=4000,TRU E,FALSE),IF(B2+D2=400,TRUE,FALSE)),TRUE,FALSE)=FA LSE,"Gold",IF(IF(AND(IF(C2+E2=5000,TRUE,FALSE),IF (B2+D2=500,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Platnu m",IF(IF(AND(IF(C2+E2=6000,TRUE,FALSE),IF(B2+D2= 600,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Diamond",IF(IF (AND(IF(C2+E2=7000,TRUE,FALSE),IF(B2+D2=700,TRUE ,FALSE)),TRUE,FALSE)=FALSE,"MoonRock","CarbonNanoT ubes"))))))) -------------------- The first stage of simplification is that you don't need to say to say IF(AND(TestA,TestB),TRUE,FALSE) as this is just the same as AND(Testa,TestB), which already returns a TRUE or FALSE result. This then simplifies your expression to: =IF(AND(C2+E2=1000,B2+D2=100)=FALSE,"none",IF(AN D(C2+E2=2000,B2+D2=200)=FALSE,"Bronze",IF(AND(C2 +E2=3000,B2+D2=300)=FALSE,"Silver",IF(AND(C2+E2 =4000,B2+D2=400)=FALSE,"Gold",IF(AND(C2+E2=5000, B2+D2=500)=FALSE,"Platnum",IF(AND(C2+E2=6000,B2+ D2=600)=FALSE,"Diamond",IF(AND(C2+E2=7000,B2+D2 =700)=FALSE,"MoonRock","CarbonNanoTubes"))))))) if I've got my edits right, but of course that still exceeds the 7 limit for nesting. I think you might be able to try something like: =CHOOSE(MIN(INT(MIN((C2+E2)/1000,(B2+D2)/100))+1,8),"None","Bronze","Silver","Gold","Platnu m","Diamond","Moon Rock","CarbonNanoTubes") -- David Biddulph |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
Bob Phillips wrote...
=IF(OR(C2+E2<1000,B2+D2<100),"none", IF(OR(C2+E2<2000,B2+D2<200),"Bronze", IF(OR(C2+E2<3000,B2+D2<300),"Silver", IF(OR(C2+E2<4000,B2+D2<400),"Gold", IF(OR(C2+E2<5000,B2+D2<500),"Platnum", IF(OR(C2+E2<6000,B2+D2<600),"Diamond", IF(OR(C2+E2<7000,B2+D2<700),"MoonRock","CarbonNan oTubes"))))))) .... If so, =LOOKUP(MIN(INT((C2+E2)/1000),INT((B2+D2)/100)),{-1E300;1;2;3;4;5;6;7}, {"none";"Bronze";"Silver";"Gold";"Platnum";"Diamon d";"MoonRock";"CarbonNanoTubes"}) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
"David Biddulph" wrote in message
... "trex005" wrote in message ... Okay, I have written longer formulas than this and what I thought was more complex, but for some reason excel says that there is an error in this formula! I have checked it over and over and there are no syntax errors.... what is the problem? Code: -------------------- =IF(IF(AND(IF(C2+E2=1000,TRUE,FALSE),IF(B2+D2=10 0,TRUE,FALSE)),TRUE,FALSE)=FALSE,"none",IF(IF(AND( IF(C2+E2=2000,TRUE,FALSE),IF(B2+D2=200,TRUE,FALS E)),TRUE,FALSE)=FALSE,"Bronze",IF(IF(AND(IF(C2+E2 =3000,TRUE,FALSE),IF(B2+D2=300,TRUE,FALSE)),TRUE, FALSE)=FALSE,"Silver",IF(IF(AND(IF(C2+E2=4000,TRU E,FALSE),IF(B2+D2=400,TRUE,FALSE)),TRUE,FALSE)=FA LSE,"Gold",IF(IF(AND(IF(C2+E2=5000,TRUE,FALSE),IF (B2+D2=500,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Platnu m",IF(IF(AND(IF(C2+E2=6000,TRUE,FALSE),IF(B2+D2= 600,TRUE,FALSE)),TRUE,FALSE)=FALSE,"Diamond",IF(IF (AND(IF(C2+E2=7000,TRUE,FALSE),IF(B2+D2=700,TRUE ,FALSE)),TRUE,FALSE)=FALSE,"MoonRock","CarbonNanoT ubes"))))))) -------------------- The first stage of simplification is that you don't need to say to say IF(AND(TestA,TestB),TRUE,FALSE) as this is just the same as AND(Testa,TestB), which already returns a TRUE or FALSE result. This then simplifies your expression to: =IF(AND(C2+E2=1000,B2+D2=100)=FALSE,"none",IF(AN D(C2+E2=2000,B2+D2=200)=FALSE,"Bronze",IF(AND(C2 +E2=3000,B2+D2=300)=FALSE,"Silver",IF(AND(C2+E2 =4000,B2+D2=400)=FALSE,"Gold",IF(AND(C2+E2=5000, B2+D2=500)=FALSE,"Platnum",IF(AND(C2+E2=6000,B2+ D2=600)=FALSE,"Diamond",IF(AND(C2+E2=7000,B2+D2 =700)=FALSE,"MoonRock","CarbonNanoTubes"))))))) if I've got my edits right, but of course that still exceeds the 7 limit for nesting. I think you might be able to try something like: =CHOOSE(MIN(INT(MIN((C2+E2)/1000,(B2+D2)/100))+1,8),"None","Bronze","Silver","Gold","Platnu m","Diamond","Moon Rock","CarbonNanoTubes") And in fact you can probably skip the INT(), hence: =CHOOSE(MIN(MIN((C2+E2)/1000,(B2+D2)/100)+1,8),"None","Bronze","Silver","Gold","Platnum ","Diamond","Moon Rock","CarbonNanoTubes") -- David Biddulph Rowing web pages at http://www.biddulph.org.uk/ |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
One step at a time Harlan, I was trying to show the flaws in the OPs logic
<g Bob "Harlan Grove" wrote in message oups.com... Bob Phillips wrote... =IF(OR(C2+E2<1000,B2+D2<100),"none", IF(OR(C2+E2<2000,B2+D2<200),"Bronze", IF(OR(C2+E2<3000,B2+D2<300),"Silver", IF(OR(C2+E2<4000,B2+D2<400),"Gold", IF(OR(C2+E2<5000,B2+D2<500),"Platnum", IF(OR(C2+E2<6000,B2+D2<600),"Diamond", IF(OR(C2+E2<7000,B2+D2<700),"MoonRock","CarbonNan oTubes"))))))) ... If so, =LOOKUP(MIN(INT((C2+E2)/1000),INT((B2+D2)/100)),{-1E300;1;2;3;4;5;6;7}, {"none";"Bronze";"Silver";"Gold";"Platnum";"Diamon d";"MoonRock";"CarbonNanoT ubes"}) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
Wow, this is a great wealth of information! My problem is that I have a JavaScript (don't laugh, I needed something that someone could run on any computer, modify the source and run again) program that is writing this and many other formulas that takes user input of the "tiers". I have to output many formula's, and I was using other formulas that I was outputting and simply nesting them into the other formulas. This would work if I could nest unlimited, but I guess that is not the case. Instead I decided, with your guys help, that I was just going to have to write each formula on it's own. Now my program outputs this : =IF(OR(C2+E2<1000,B2+D2<100),"none", IF(OR(C2+E2<2000,B2+D2<200),"Bronze", IF(OR(C2+E2<3000,B2+D2<300),"Silver", IF(OR(C2+E2<4000,B2+D2<400),"Gold", IF(OR(C2+E2<5000,B2+D2<500),"Platinum", IF(OR(C2+E2<6000,B2+D2<600),"Diamond", IF(OR(C2+E2<7000,B2+D2<700),"MoonRock", "CarbonNanoTubes"))))))) (exactly what you guys were simplifying to) Unfortunatly, I still then hit the nesting limit as soon as I add one more tier =IF(OR(C2+E2<1000,B2+D2<100),"none", IF(OR(C2+E2<2000,B2+D2<200),"Bronze", IF(OR(C2+E2<3000,B2+D2<300),"Silver", IF(OR(C2+E2<4000,B2+D2<400),"Gold", IF(OR(C2+E2<5000,B2+D2<500),"Platinum", IF(OR(C2+E2<6000,B2+D2<600),"Diamond", IF(OR(C2+E2<7000,B2+D2<700),"Moon Rock", IF(OR(C2+E2<8000,B2+D2<800),"Carbon Nano Tubes", "Naquida")))))))) So I guess I do need to go with a lookup system of sorts. I was looking at dadylonglegs and it looked good, except my tier levels will constantly be changing and will not be as reliable as my sample data. It will more likely look something like this : =IF(OR(C2+E2<117.5,B2+D2<12),"none", IF(OR(C2+E2<186.45,B2+D2<19),"Bronze", IF(OR(C2+E2<499.99,B2+D2<27),"Silver", IF(OR(C2+E2<1965.45,B2+D2<70),"Gold", IF(OR(C2+E2<6789.95,B2+D2<111),"Platinum", IF(OR(C2+E2<8006.41,B2+D2<173),"Diamond", IF(OR(C2+E2<9001.32,B2+D2<198),"Moon Rock", IF(OR(C2+E2<10000.39,B2+D2<275),"Carbon Nano Tubes", "Naquida")))))))) Is there an easy way to do this? -- trex005 ------------------------------------------------------------------------ trex005's Profile: http://www.excelforum.com/member.php...o&userid=34724 View this thread: http://www.excelforum.com/showthread...hreadid=544888 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
trex,
This is where you use Harlan's little beauty of an alternative. =LOOKUP(MIN(INT((C2+E2)/1000),INT((B2+D2)/100)),{-1E+300;1;2;3;4;5;6;7;8}, {"none";"Bronze";"Silver";"Gold";"Platnum";"Diamon d";"MoonRock";"CarbonNanoT ubes";"Naquida"}) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "trex005" wrote in message ... Wow, this is a great wealth of information! My problem is that I have a JavaScript (don't laugh, I needed something that someone could run on any computer, modify the source and run again) program that is writing this and many other formulas that takes user input of the "tiers". I have to output many formula's, and I was using other formulas that I was outputting and simply nesting them into the other formulas. This would work if I could nest unlimited, but I guess that is not the case. Instead I decided, with your guys help, that I was just going to have to write each formula on it's own. Now my program outputs this : =IF(OR(C2+E2<1000,B2+D2<100),"none", IF(OR(C2+E2<2000,B2+D2<200),"Bronze", IF(OR(C2+E2<3000,B2+D2<300),"Silver", IF(OR(C2+E2<4000,B2+D2<400),"Gold", IF(OR(C2+E2<5000,B2+D2<500),"Platinum", IF(OR(C2+E2<6000,B2+D2<600),"Diamond", IF(OR(C2+E2<7000,B2+D2<700),"MoonRock", "CarbonNanoTubes"))))))) (exactly what you guys were simplifying to) Unfortunatly, I still then hit the nesting limit as soon as I add one more tier =IF(OR(C2+E2<1000,B2+D2<100),"none", IF(OR(C2+E2<2000,B2+D2<200),"Bronze", IF(OR(C2+E2<3000,B2+D2<300),"Silver", IF(OR(C2+E2<4000,B2+D2<400),"Gold", IF(OR(C2+E2<5000,B2+D2<500),"Platinum", IF(OR(C2+E2<6000,B2+D2<600),"Diamond", IF(OR(C2+E2<7000,B2+D2<700),"Moon Rock", IF(OR(C2+E2<8000,B2+D2<800),"Carbon Nano Tubes", "Naquida")))))))) So I guess I do need to go with a lookup system of sorts. I was looking at dadylonglegs and it looked good, except my tier levels will constantly be changing and will not be as reliable as my sample data. It will more likely look something like this : =IF(OR(C2+E2<117.5,B2+D2<12),"none", IF(OR(C2+E2<186.45,B2+D2<19),"Bronze", IF(OR(C2+E2<499.99,B2+D2<27),"Silver", IF(OR(C2+E2<1965.45,B2+D2<70),"Gold", IF(OR(C2+E2<6789.95,B2+D2<111),"Platinum", IF(OR(C2+E2<8006.41,B2+D2<173),"Diamond", IF(OR(C2+E2<9001.32,B2+D2<198),"Moon Rock", IF(OR(C2+E2<10000.39,B2+D2<275),"Carbon Nano Tubes", "Naquida")))))))) Is there an easy way to do this? -- trex005 ------------------------------------------------------------------------ trex005's Profile: http://www.excelforum.com/member.php...o&userid=34724 View this thread: http://www.excelforum.com/showthread...hreadid=544888 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
Because that assumes that there is consistency between the tiers, which is easy to write without, but even more because it assumes that there is consistency within the tier, it does not work. I'm sure that there is a way to write it, but it is too much for my little brain to handle! if you look at the last sample I gave, you will see that I used more realistic numbers. Thank you everyone for your help already given, and the help you are still providing! Bob Phillips Wrote: trex, This is where you use Harlan's little beauty of an alternative. =LOOKUP(MIN(INT((C2+E2)/1000),INT((B2+D2)/100)),{-1E+300;1;2;3;4;5;6;7;8}, {"none";"Bronze";"Silver";"Gold";"Platnum";"Diamon d";"MoonRock";"CarbonNanoT ubes";"Naquida"}) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "trex005" wrote in message ... Wow, this is a great wealth of information! My problem is that I have a JavaScript (don't laugh, I needed something that someone could run on any computer, modify the source and run again) program that is writing this and many other formulas that takes user input of the "tiers". I have to output many formula's, and I was using other formulas that I was outputting and simply nesting them into the other formulas. This would work if I could nest unlimited, but I guess that is not the case. Instead I decided, with your guys help, that I was just going to have to write each formula on it's own. Now my program outputs this : =IF(OR(C2+E2<1000,B2+D2<100),"none", IF(OR(C2+E2<2000,B2+D2<200),"Bronze", IF(OR(C2+E2<3000,B2+D2<300),"Silver", IF(OR(C2+E2<4000,B2+D2<400),"Gold", IF(OR(C2+E2<5000,B2+D2<500),"Platinum", IF(OR(C2+E2<6000,B2+D2<600),"Diamond", IF(OR(C2+E2<7000,B2+D2<700),"MoonRock", "CarbonNanoTubes"))))))) (exactly what you guys were simplifying to) Unfortunatly, I still then hit the nesting limit as soon as I add one more tier =IF(OR(C2+E2<1000,B2+D2<100),"none", IF(OR(C2+E2<2000,B2+D2<200),"Bronze", IF(OR(C2+E2<3000,B2+D2<300),"Silver", IF(OR(C2+E2<4000,B2+D2<400),"Gold", IF(OR(C2+E2<5000,B2+D2<500),"Platinum", IF(OR(C2+E2<6000,B2+D2<600),"Diamond", IF(OR(C2+E2<7000,B2+D2<700),"Moon Rock", IF(OR(C2+E2<8000,B2+D2<800),"Carbon Nano Tubes", "Naquida")))))))) So I guess I do need to go with a lookup system of sorts. I was looking at dadylonglegs and it looked good, except my tier levels will constantly be changing and will not be as reliable as my sample data. It will more likely look something like this : =IF(OR(C2+E2<117.5,B2+D2<12),"none", IF(OR(C2+E2<186.45,B2+D2<19),"Bronze", IF(OR(C2+E2<499.99,B2+D2<27),"Silver", IF(OR(C2+E2<1965.45,B2+D2<70),"Gold", IF(OR(C2+E2<6789.95,B2+D2<111),"Platinum", IF(OR(C2+E2<8006.41,B2+D2<173),"Diamond", IF(OR(C2+E2<9001.32,B2+D2<198),"Moon Rock", IF(OR(C2+E2<10000.39,B2+D2<275),"Carbon Nano Tubes", "Naquida")))))))) Is there an easy way to do this? -- trex005 ------------------------------------------------------------------------ trex005's Profile: http://www.excelforum.com/member.php...o&userid=34724 View this thread: http://www.excelforum.com/showthread...hreadid=544888 -- trex005 ------------------------------------------------------------------------ trex005's Profile: http://www.excelforum.com/member.php...o&userid=34724 View this thread: http://www.excelforum.com/showthread...hreadid=544888 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
trex005 wrote...
Because that assumes that there is consistency between the tiers, which is easy to write without, but even more because it assumes that there is consistency within the tier, it does not work. I'm sure that there is a way to write it, but it is too much for my little brain to handle! if you look at the last sample I gave, you will see that I used more realistic numbers. .... Regularity isn't necessary. Lack of regularity makes this only slightly more complicated. Instead of MIN((C2+E2)/1000,(B2+D2)/100), use MIN(MATCH(C2+E2,{-1E300;117.5;186.45;499.99;1965.45;6789.95;8006.41; 9001.32; 10000.39}),MATCH(B2+D2,{-1E300;12;19;27;70;111;173;198;275})) and dispense with the LOOKUP and use INDEX instead. =INDEX({"none";"Bronze";"Silver";"Gold";"Platinum" ;"Diamond";"Moon Rock"; "Carbon Nano Tubes";"Naquida"},MIN(MATCH(C2+E2,{-1E300;117.5;186.45;499.99; 1965.45;6789.95;8006.41;9001.32;10000.39}),MATCH(B 2+D2,{-1E300;12;19;27;70;111; 173;198;275})) |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A formula that is too complex for Excel?
Give this a whirl
=INDEX({"Naquida";"Carbon Nano Tubes";"Moon Rock";"Diamond";"Platinum";"Gold";"Silver";"Bronze ";"none"}, MAX(MATCH(C2+E2,{999999;10000.39;9001.32;8006.41;6 789.95;1965.45;499.99;186. 45;117.5},-1), MATCH(B2+D2,{999999;275;198;173;111;70;27;19;12},-1))) -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "trex005" wrote in message ... Because that assumes that there is consistency between the tiers, which is easy to write without, but even more because it assumes that there is consistency within the tier, it does not work. I'm sure that there is a way to write it, but it is too much for my little brain to handle! if you look at the last sample I gave, you will see that I used more realistic numbers. Thank you everyone for your help already given, and the help you are still providing! Bob Phillips Wrote: trex, This is where you use Harlan's little beauty of an alternative. =LOOKUP(MIN(INT((C2+E2)/1000),INT((B2+D2)/100)),{-1E+300;1;2;3;4;5;6;7;8}, {"none";"Bronze";"Silver";"Gold";"Platnum";"Diamon d";"MoonRock";"CarbonNanoT ubes";"Naquida"}) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "trex005" wrote in message ... Wow, this is a great wealth of information! My problem is that I have a JavaScript (don't laugh, I needed something that someone could run on any computer, modify the source and run again) program that is writing this and many other formulas that takes user input of the "tiers". I have to output many formula's, and I was using other formulas that I was outputting and simply nesting them into the other formulas. This would work if I could nest unlimited, but I guess that is not the case. Instead I decided, with your guys help, that I was just going to have to write each formula on it's own. Now my program outputs this : =IF(OR(C2+E2<1000,B2+D2<100),"none", IF(OR(C2+E2<2000,B2+D2<200),"Bronze", IF(OR(C2+E2<3000,B2+D2<300),"Silver", IF(OR(C2+E2<4000,B2+D2<400),"Gold", IF(OR(C2+E2<5000,B2+D2<500),"Platinum", IF(OR(C2+E2<6000,B2+D2<600),"Diamond", IF(OR(C2+E2<7000,B2+D2<700),"MoonRock", "CarbonNanoTubes"))))))) (exactly what you guys were simplifying to) Unfortunatly, I still then hit the nesting limit as soon as I add one more tier =IF(OR(C2+E2<1000,B2+D2<100),"none", IF(OR(C2+E2<2000,B2+D2<200),"Bronze", IF(OR(C2+E2<3000,B2+D2<300),"Silver", IF(OR(C2+E2<4000,B2+D2<400),"Gold", IF(OR(C2+E2<5000,B2+D2<500),"Platinum", IF(OR(C2+E2<6000,B2+D2<600),"Diamond", IF(OR(C2+E2<7000,B2+D2<700),"Moon Rock", IF(OR(C2+E2<8000,B2+D2<800),"Carbon Nano Tubes", "Naquida")))))))) So I guess I do need to go with a lookup system of sorts. I was looking at dadylonglegs and it looked good, except my tier levels will constantly be changing and will not be as reliable as my sample data. It will more likely look something like this : =IF(OR(C2+E2<117.5,B2+D2<12),"none", IF(OR(C2+E2<186.45,B2+D2<19),"Bronze", IF(OR(C2+E2<499.99,B2+D2<27),"Silver", IF(OR(C2+E2<1965.45,B2+D2<70),"Gold", IF(OR(C2+E2<6789.95,B2+D2<111),"Platinum", IF(OR(C2+E2<8006.41,B2+D2<173),"Diamond", IF(OR(C2+E2<9001.32,B2+D2<198),"Moon Rock", IF(OR(C2+E2<10000.39,B2+D2<275),"Carbon Nano Tubes", "Naquida")))))))) Is there an easy way to do this? -- trex005 ------------------------------------------------------------------------ trex005's Profile: http://www.excelforum.com/member.php...o&userid=34724 View this thread: http://www.excelforum.com/showthread...hreadid=544888 -- trex005 ------------------------------------------------------------------------ trex005's Profile: http://www.excelforum.com/member.php...o&userid=34724 View this thread: http://www.excelforum.com/showthread...hreadid=544888 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is it possible? | Excel Worksheet Functions | |||
| Complex Formula Getting Error.. | Excel Discussion (Misc queries) | |||
| 2 Nesting questions | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Complex formula | Excel Discussion (Misc queries) | |||
Linear Mode
