Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
I have 2 files with similar formulas. Now i need both those formulas to be
in one cell. I am getting and error for the +IF(RIGHT((ROUNDUP($J6,2))*100,1)="0" part of the formula. Below are the 2 folrmulas. Formula 1: =IF(H6="4",IF($J6<=50,CEILING(($J6)+0.01,1)-0.01,IF(AND($J6=50.01,$J6<=100),(ROUND($J6/5,0)*5)-0.01,IF(AND($J6100.01,$J6<=500),CEILING(($J6)+0.0 1,10)-0.01,IF($J6=500.01,CHOOSE(VLOOKUP($J6,Tbl_Categor y,2,TRUE),MAX(INT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01),"")))),IF(H6="3",IF(J6<200,INT(J6)+IF(RIGHT( F6*100,2)="00",1,RIGHT(F6*100,2)/100),(INT(J6/10)*10)+RIGHT(F6*100,3)/100),$F6)) Formula 2: =IF($H6=1,IF($J6<=1,$J6,IF(AND($J61,$J6<=1.05),0. 99,IF(AND($J6=1.06,$J6<=1.09),1.09,IF(AND($J6=1. 1,$J6<=9.99)+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09,IF(RIGHT($J6,2)*100<=9,19,(ROU NDUP($J6*100,-1)-1)/100)),IF($J6=10,CEILING($J6+0.01,0.5)-0.01))))),$F6) -- lmo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
"Lisa" wrote:
I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error Since you did not show us how you are trying to combine the formulas it is difficult to say exactly what __your__ error is. But when __I__ try to combine the formula one way, I get an error because it exceeds the limit of 7 nested functions (8 including the outermost function) in Excel 2003. The combined formula, as I did it, would have 9 nested functions (10 including the outermost function). I tried to combine the two formulas by dropping "$F6))" at the end of Formula1 and pasting in Formula2 without the "=" at the beginning, adding "))" at the end to balance the new logic. (It would be easier to see, but it would get mangled if I tried to paste it here.) By the way, I suspect Formula2 has a defect. Near the end, you have IF6($J6=10,CEILING($J6+0.01,0.5)-0.01). Note that there is no "false value" part. It will return FALSE if $J6<10, which is usually undersirable. I also believe the combined formula would have other defects. For example, in Formula1, you treat H6 as text, whereas in Formula2, you treat H6 as numeric. ----- original message ----- "Lisa" wrote in message ... I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error for the +IF(RIGHT((ROUNDUP($J6,2))*100,1)="0" part of the formula. Below are the 2 folrmulas. Formula 1: =IF(H6="4",IF($J6<=50,CEILING(($J6)+0.01,1)-0.01,IF(AND($J6=50.01,$J6<=100),(ROUND($J6/5,0)*5)-0.01,IF(AND($J6100.01,$J6<=500),CEILING(($J6)+0.0 1,10)-0.01,IF($J6=500.01,CHOOSE(VLOOKUP($J6,Tbl_Categor y,2,TRUE),MAX(INT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01),"")))),IF(H6="3",IF(J6<200,INT(J6)+IF(RIGHT( F6*100,2)="00",1,RIGHT(F6*100,2)/100),(INT(J6/10)*10)+RIGHT(F6*100,3)/100),$F6)) Formula 2: =IF($H6=1,IF($J6<=1,$J6,IF(AND($J61,$J6<=1.05),0. 99,IF(AND($J6=1.06,$J6<=1.09),1.09,IF(AND($J6=1. 1,$J6<=9.99)+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09,IF(RIGHT($J6,2)*100<=9,19,(ROU NDUP($J6*100,-1)-1)/100)),IF($J6=10,CEILING($J6+0.01,0.5)-0.01))))),$F6) -- lmo |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
H6 is written as text and the second formula didn't reflect that, but in the
second formula i have tested all possible results and my answers are all correct. For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. -- lmo "JoeU2004" wrote: "Lisa" wrote: I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error Since you did not show us how you are trying to combine the formulas it is difficult to say exactly what __your__ error is. But when __I__ try to combine the formula one way, I get an error because it exceeds the limit of 7 nested functions (8 including the outermost function) in Excel 2003. The combined formula, as I did it, would have 9 nested functions (10 including the outermost function). I tried to combine the two formulas by dropping "$F6))" at the end of Formula1 and pasting in Formula2 without the "=" at the beginning, adding "))" at the end to balance the new logic. (It would be easier to see, but it would get mangled if I tried to paste it here.) By the way, I suspect Formula2 has a defect. Near the end, you have IF6($J6=10,CEILING($J6+0.01,0.5)-0.01). Note that there is no "false value" part. It will return FALSE if $J6<10, which is usually undersirable. I also believe the combined formula would have other defects. For example, in Formula1, you treat H6 as text, whereas in Formula2, you treat H6 as numeric. ----- original message ----- "Lisa" wrote in message ... I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error for the +IF(RIGHT((ROUNDUP($J6,2))*100,1)="0" part of the formula. Below are the 2 folrmulas. Formula 1: =IF(H6="4",IF($J6<=50,CEILING(($J6)+0.01,1)-0.01,IF(AND($J6=50.01,$J6<=100),(ROUND($J6/5,0)*5)-0.01,IF(AND($J6100.01,$J6<=500),CEILING(($J6)+0.0 1,10)-0.01,IF($J6=500.01,CHOOSE(VLOOKUP($J6,Tbl_Categor y,2,TRUE),MAX(INT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01),"")))),IF(H6="3",IF(J6<200,INT(J6)+IF(RIGHT( F6*100,2)="00",1,RIGHT(F6*100,2)/100),(INT(J6/10)*10)+RIGHT(F6*100,3)/100),$F6)) Formula 2: =IF($H6=1,IF($J6<=1,$J6,IF(AND($J61,$J6<=1.05),0. 99,IF(AND($J6=1.06,$J6<=1.09),1.09,IF(AND($J6=1. 1,$J6<=9.99)+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09,IF(RIGHT($J6,2)*100<=9,19,(ROU NDUP($J6*100,-1)-1)/100)),IF($J6=10,CEILING($J6+0.01,0.5)-0.01))))),$F6) -- lmo |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
"Lisa" wrote:
For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. A quick-and-dirty solution might lie in finding ways to reduce the nesting level. (A better solution might lie in understanding your rounding rules and finding some simplfication in the overall algorithm, if any. But that would require in-depth knowledge of what you must do. I'm not going there.) Caveat: I have not actually tried the following myself. It will reduce the nesting level. According to my prototype, it should be enough; but I do not know for sure. Nonetheless, it might be worth a try. The overall structure of the combined formula is ostensibly: =if(H6="4", ..., if(H6="3", ..., if(H6="1", ..., $J6))) That could be written: =if(and(H6<{"1","3","4"}), $J6, choose(H6, ..., 0, ..., ...)) Note that the nesting level is reduced by one. FYI, because of the AND() pre-condition, CHOOSE will never see H6=2. The zero is just a place holder. Another simpflication lies in avoiding over-specification in your subordinate conditional expressions. Not only will this reduce the nesting level by one again, but also it will avoid discontinuities: holes in the logic that might exist due to rounding or numerical anomalies associated with numbers with decimal fractions. Specifically: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Note: I took the liberty of presuming the correction in the errata below. Caveat: The preceding assumes that J6 is explicitly rounded to 2 decimal places, not merely formatted as such. If that's not the case (explicit rounding), more tweaks to your algorithm might be desirable, depending on your tolerance. Errata .... You worte: the second formula i have tested all possible results and my answers are all correct. I believe I see your intent now. I still believe there is an error in the nesting of IF() functions; but it is not the error that I first thought. I am still puzzled by the structure of your second formula. I believe there is an error starting at: IF(AND($J6=1.1,$J6<=9.99)+IF(RIGHT((ROUNDUP(...)) *100,1)="0", ... I could be wrong. To follow along, I suggest that you copy-and-paste the formula into Notepad, break it down so that IF(condition,truePart,falsePart) is on separate lines, and number the parentheses of nesting levels like this: IF1(...)1. The following is what I see (with apologies if things do not align as intended): =IF1($H6=1, IF2($J6<=1, $J6, IF3(AND($J61,$J6<=1.05), 0.99, IF4(AND($J6=1.06,$J6<=1.09), 1.09, IF5(AND($J6=1.1,$J6<=9.99) +IF6(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09, IF7(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)7)6, IF6($J6=10,CEILING($J6+0.01,0.5)-0.01)6)5)4)3)2, $F6)1 Note that the expression IF($J6=10,...) is the true part of the expression IF(AND($J6=1.1,$J6<=9.99)+IF(...),...). If that is your intent, then I believe "IF5" will return FALSE when $J6<=9.99, as I said before. Explanation.... The expression AND(...)+IF(...) behaves like an OR operation: it is false only when both functions return false or zero. But I believe the "+IF(...)" expression is always greater than zero. So I believe AND(...)+IF(...) is always true, even when $J6<=9.99. Thus, when $J6<=9.99, IF($J6=10,...) is false. And since there is no false part, it will return FALSE. Moreover, if my analysis is correct, then as written, I do not believe "IF5" behaves at all as you intended. I suspect it should return 19 or either of the ROUNDUP results. I believe it does not. I suspect that your original intent was for "+IF(...)" to be ",IF(...)"; that is, it is intended to be the true part when 1.1<=$J6<=9.99. I suspect the formula got mangled in an attempt to solve a problem with too many nesting levels, taking RIGHT(ROUND(...)...) into account. Forgive me if I am wrong, and this is a red herring. But if my analysis is correct, then with the simplification that I suggested above, you might be able to correct this by reverting back to ",IF(...)" instead of "+IF(...)". However, if you do, I suspect you will encounter nesting level problems in the combined IF() expression, despite my suggestions. Sigh. ----- original message ----- "Lisa" wrote in message ... H6 is written as text and the second formula didn't reflect that, but in the second formula i have tested all possible results and my answers are all correct. For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. -- lmo "JoeU2004" wrote: "Lisa" wrote: I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error Since you did not show us how you are trying to combine the formulas it is difficult to say exactly what __your__ error is. But when __I__ try to combine the formula one way, I get an error because it exceeds the limit of 7 nested functions (8 including the outermost function) in Excel 2003. The combined formula, as I did it, would have 9 nested functions (10 including the outermost function). I tried to combine the two formulas by dropping "$F6))" at the end of Formula1 and pasting in Formula2 without the "=" at the beginning, adding "))" at the end to balance the new logic. (It would be easier to see, but it would get mangled if I tried to paste it here.) By the way, I suspect Formula2 has a defect. Near the end, you have IF6($J6=10,CEILING($J6+0.01,0.5)-0.01). Note that there is no "false value" part. It will return FALSE if $J6<10, which is usually undersirable. I also believe the combined formula would have other defects. For example, in Formula1, you treat H6 as text, whereas in Formula2, you treat H6 as numeric. ----- original message ----- "Lisa" wrote in message ... I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error for the +IF(RIGHT((ROUNDUP($J6,2))*100,1)="0" part of the formula. Below are the 2 folrmulas. Formula 1: =IF(H6="4",IF($J6<=50,CEILING(($J6)+0.01,1)-0.01,IF(AND($J6=50.01,$J6<=100),(ROUND($J6/5,0)*5)-0.01,IF(AND($J6100.01,$J6<=500),CEILING(($J6)+0.0 1,10)-0.01,IF($J6=500.01,CHOOSE(VLOOKUP($J6,Tbl_Categor y,2,TRUE),MAX(INT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01),"")))),IF(H6="3",IF(J6<200,INT(J6)+IF(RIGHT( F6*100,2)="00",1,RIGHT(F6*100,2)/100),(INT(J6/10)*10)+RIGHT(F6*100,3)/100),$F6)) Formula 2: =IF($H6=1,IF($J6<=1,$J6,IF(AND($J61,$J6<=1.05),0. 99,IF(AND($J6=1.06,$J6<=1.09),1.09,IF(AND($J6=1. 1,$J6<=9.99)+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09,IF(RIGHT($J6,2)*100<=9,19,(ROU NDUP($J6*100,-1)-1)/100)),IF($J6=10,CEILING($J6+0.01,0.5)-0.01))))),$F6) -- lmo |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
"JoeU2004" wrote:
"Lisa" wrote: the second formula i have tested all possible results and my answers are all correct. [....] If that is your intent, then I believe "IF5" will return FALSE when $J6<=9.99, as I said before. A fact that I verified very quickly by setting H6 to 1 and J6 to 9. (Klunk!) If you are saying that FALSE is the intended result in that case (suprise!), then I apologize for making much ado about nothing. ----- original message ----- "JoeU2004" wrote in message ... "Lisa" wrote: For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. A quick-and-dirty solution might lie in finding ways to reduce the nesting level. (A better solution might lie in understanding your rounding rules and finding some simplfication in the overall algorithm, if any. But that would require in-depth knowledge of what you must do. I'm not going there.) Caveat: I have not actually tried the following myself. It will reduce the nesting level. According to my prototype, it should be enough; but I do not know for sure. Nonetheless, it might be worth a try. The overall structure of the combined formula is ostensibly: =if(H6="4", ..., if(H6="3", ..., if(H6="1", ..., $J6))) That could be written: =if(and(H6<{"1","3","4"}), $J6, choose(H6, ..., 0, ..., ...)) Note that the nesting level is reduced by one. FYI, because of the AND() pre-condition, CHOOSE will never see H6=2. The zero is just a place holder. Another simpflication lies in avoiding over-specification in your subordinate conditional expressions. Not only will this reduce the nesting level by one again, but also it will avoid discontinuities: holes in the logic that might exist due to rounding or numerical anomalies associated with numbers with decimal fractions. Specifically: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Note: I took the liberty of presuming the correction in the errata below. Caveat: The preceding assumes that J6 is explicitly rounded to 2 decimal places, not merely formatted as such. If that's not the case (explicit rounding), more tweaks to your algorithm might be desirable, depending on your tolerance. Errata .... You worte: the second formula i have tested all possible results and my answers are all correct. I believe I see your intent now. I still believe there is an error in the nesting of IF() functions; but it is not the error that I first thought. I am still puzzled by the structure of your second formula. I believe there is an error starting at: IF(AND($J6=1.1,$J6<=9.99)+IF(RIGHT((ROUNDUP(...)) *100,1)="0", ... I could be wrong. To follow along, I suggest that you copy-and-paste the formula into Notepad, break it down so that IF(condition,truePart,falsePart) is on separate lines, and number the parentheses of nesting levels like this: IF1(...)1. The following is what I see (with apologies if things do not align as intended): =IF1($H6=1, IF2($J6<=1, $J6, IF3(AND($J61,$J6<=1.05), 0.99, IF4(AND($J6=1.06,$J6<=1.09), 1.09, IF5(AND($J6=1.1,$J6<=9.99) +IF6(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09, IF7(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)7)6, IF6($J6=10,CEILING($J6+0.01,0.5)-0.01)6)5)4)3)2, $F6)1 Note that the expression IF($J6=10,...) is the true part of the expression IF(AND($J6=1.1,$J6<=9.99)+IF(...),...). If that is your intent, then I believe "IF5" will return FALSE when $J6<=9.99, as I said before. Explanation.... The expression AND(...)+IF(...) behaves like an OR operation: it is false only when both functions return false or zero. But I believe the "+IF(...)" expression is always greater than zero. So I believe AND(...)+IF(...) is always true, even when $J6<=9.99. Thus, when $J6<=9.99, IF($J6=10,...) is false. And since there is no false part, it will return FALSE. Moreover, if my analysis is correct, then as written, I do not believe "IF5" behaves at all as you intended. I suspect it should return 19 or either of the ROUNDUP results. I believe it does not. I suspect that your original intent was for "+IF(...)" to be ",IF(...)"; that is, it is intended to be the true part when 1.1<=$J6<=9.99. I suspect the formula got mangled in an attempt to solve a problem with too many nesting levels, taking RIGHT(ROUND(...)...) into account. Forgive me if I am wrong, and this is a red herring. But if my analysis is correct, then with the simplification that I suggested above, you might be able to correct this by reverting back to ",IF(...)" instead of "+IF(...)". However, if you do, I suspect you will encounter nesting level problems in the combined IF() expression, despite my suggestions. Sigh. ----- original message ----- "Lisa" wrote in message ... H6 is written as text and the second formula didn't reflect that, but in the second formula i have tested all possible results and my answers are all correct. For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. -- lmo "JoeU2004" wrote: "Lisa" wrote: I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error Since you did not show us how you are trying to combine the formulas it is difficult to say exactly what __your__ error is. But when __I__ try to combine the formula one way, I get an error because it exceeds the limit of 7 nested functions (8 including the outermost function) in Excel 2003. The combined formula, as I did it, would have 9 nested functions (10 including the outermost function). I tried to combine the two formulas by dropping "$F6))" at the end of Formula1 and pasting in Formula2 without the "=" at the beginning, adding "))" at the end to balance the new logic. (It would be easier to see, but it would get mangled if I tried to paste it here.) By the way, I suspect Formula2 has a defect. Near the end, you have IF6($J6=10,CEILING($J6+0.01,0.5)-0.01). Note that there is no "false value" part. It will return FALSE if $J6<10, which is usually undersirable. I also believe the combined formula would have other defects. For example, in Formula1, you treat H6 as text, whereas in Formula2, you treat H6 as numeric. ----- original message ----- "Lisa" wrote in message ... I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error for the +IF(RIGHT((ROUNDUP($J6,2))*100,1)="0" part of the formula. Below are the 2 folrmulas. Formula 1: =IF(H6="4",IF($J6<=50,CEILING(($J6)+0.01,1)-0.01,IF(AND($J6=50.01,$J6<=100),(ROUND($J6/5,0)*5)-0.01,IF(AND($J6100.01,$J6<=500),CEILING(($J6)+0.0 1,10)-0.01,IF($J6=500.01,CHOOSE(VLOOKUP($J6,Tbl_Categor y,2,TRUE),MAX(INT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01),"")))),IF(H6="3",IF(J6<200,INT(J6)+IF(RIGHT( F6*100,2)="00",1,RIGHT(F6*100,2)/100),(INT(J6/10)*10)+RIGHT(F6*100,3)/100),$F6)) Formula 2: =IF($H6=1,IF($J6<=1,$J6,IF(AND($J61,$J6<=1.05),0. 99,IF(AND($J6=1.06,$J6<=1.09),1.09,IF(AND($J6=1. 1,$J6<=9.99)+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09,IF(RIGHT($J6,2)*100<=9,19,(ROU NDUP($J6*100,-1)-1)/100)),IF($J6=10,CEILING($J6+0.01,0.5)-0.01))))),$F6) -- lmo |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
"JoeU2004" wrote:
I am still puzzled by the structure of your second formula. I believe there is an error starting at: IF(AND($J6=1.1,$J6<=9.99)+IF(RIGHT((ROUNDUP(...)) *100,1)="0", ... [....] I suspect that your original intent was for "+IF(...)" to be ",IF(...)"; that is, it is intended to be the true part when 1.1<=$J6<=9.99. I suspect the formula got mangled in an attempt to solve a problem with too many nesting levels, taking RIGHT(ROUND(...)...) into account. Aha! I was able to change "+IF" to ",IF" with no problem. I miscounted the nesting level at that point. So I suspect the "+" is simpy a typo. No way for me to tell if the typo is just in Lisa's posting, in which case this is truly much ado about nothing (sorry), or if it is in the actual worksheet. I had assumed that Lisa had copy-and-pasted the complex formulas. ----- original message ----- "JoeU2004" wrote in message ... "Lisa" wrote: For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. A quick-and-dirty solution might lie in finding ways to reduce the nesting level. (A better solution might lie in understanding your rounding rules and finding some simplfication in the overall algorithm, if any. But that would require in-depth knowledge of what you must do. I'm not going there.) Caveat: I have not actually tried the following myself. It will reduce the nesting level. According to my prototype, it should be enough; but I do not know for sure. Nonetheless, it might be worth a try. The overall structure of the combined formula is ostensibly: =if(H6="4", ..., if(H6="3", ..., if(H6="1", ..., $J6))) That could be written: =if(and(H6<{"1","3","4"}), $J6, choose(H6, ..., 0, ..., ...)) Note that the nesting level is reduced by one. FYI, because of the AND() pre-condition, CHOOSE will never see H6=2. The zero is just a place holder. Another simpflication lies in avoiding over-specification in your subordinate conditional expressions. Not only will this reduce the nesting level by one again, but also it will avoid discontinuities: holes in the logic that might exist due to rounding or numerical anomalies associated with numbers with decimal fractions. Specifically: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Note: I took the liberty of presuming the correction in the errata below. Caveat: The preceding assumes that J6 is explicitly rounded to 2 decimal places, not merely formatted as such. If that's not the case (explicit rounding), more tweaks to your algorithm might be desirable, depending on your tolerance. Errata .... You worte: the second formula i have tested all possible results and my answers are all correct. I believe I see your intent now. I still believe there is an error in the nesting of IF() functions; but it is not the error that I first thought. I am still puzzled by the structure of your second formula. I believe there is an error starting at: IF(AND($J6=1.1,$J6<=9.99)+IF(RIGHT((ROUNDUP(...)) *100,1)="0", ... I could be wrong. To follow along, I suggest that you copy-and-paste the formula into Notepad, break it down so that IF(condition,truePart,falsePart) is on separate lines, and number the parentheses of nesting levels like this: IF1(...)1. The following is what I see (with apologies if things do not align as intended): =IF1($H6=1, IF2($J6<=1, $J6, IF3(AND($J61,$J6<=1.05), 0.99, IF4(AND($J6=1.06,$J6<=1.09), 1.09, IF5(AND($J6=1.1,$J6<=9.99) +IF6(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09, IF7(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)7)6, IF6($J6=10,CEILING($J6+0.01,0.5)-0.01)6)5)4)3)2, $F6)1 Note that the expression IF($J6=10,...) is the true part of the expression IF(AND($J6=1.1,$J6<=9.99)+IF(...),...). If that is your intent, then I believe "IF5" will return FALSE when $J6<=9.99, as I said before. Explanation.... The expression AND(...)+IF(...) behaves like an OR operation: it is false only when both functions return false or zero. But I believe the "+IF(...)" expression is always greater than zero. So I believe AND(...)+IF(...) is always true, even when $J6<=9.99. Thus, when $J6<=9.99, IF($J6=10,...) is false. And since there is no false part, it will return FALSE. Moreover, if my analysis is correct, then as written, I do not believe "IF5" behaves at all as you intended. I suspect it should return 19 or either of the ROUNDUP results. I believe it does not. I suspect that your original intent was for "+IF(...)" to be ",IF(...)"; that is, it is intended to be the true part when 1.1<=$J6<=9.99. I suspect the formula got mangled in an attempt to solve a problem with too many nesting levels, taking RIGHT(ROUND(...)...) into account. Forgive me if I am wrong, and this is a red herring. But if my analysis is correct, then with the simplification that I suggested above, you might be able to correct this by reverting back to ",IF(...)" instead of "+IF(...)". However, if you do, I suspect you will encounter nesting level problems in the combined IF() expression, despite my suggestions. Sigh. ----- original message ----- "Lisa" wrote in message ... H6 is written as text and the second formula didn't reflect that, but in the second formula i have tested all possible results and my answers are all correct. For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. -- lmo "JoeU2004" wrote: "Lisa" wrote: I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error Since you did not show us how you are trying to combine the formulas it is difficult to say exactly what __your__ error is. But when __I__ try to combine the formula one way, I get an error because it exceeds the limit of 7 nested functions (8 including the outermost function) in Excel 2003. The combined formula, as I did it, would have 9 nested functions (10 including the outermost function). I tried to combine the two formulas by dropping "$F6))" at the end of Formula1 and pasting in Formula2 without the "=" at the beginning, adding "))" at the end to balance the new logic. (It would be easier to see, but it would get mangled if I tried to paste it here.) By the way, I suspect Formula2 has a defect. Near the end, you have IF6($J6=10,CEILING($J6+0.01,0.5)-0.01). Note that there is no "false value" part. It will return FALSE if $J6<10, which is usually undersirable. I also believe the combined formula would have other defects. For example, in Formula1, you treat H6 as text, whereas in Formula2, you treat H6 as numeric. ----- original message ----- "Lisa" wrote in message ... I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error for the +IF(RIGHT((ROUNDUP($J6,2))*100,1)="0" part of the formula. Below are the 2 folrmulas. Formula 1: =IF(H6="4",IF($J6<=50,CEILING(($J6)+0.01,1)-0.01,IF(AND($J6=50.01,$J6<=100),(ROUND($J6/5,0)*5)-0.01,IF(AND($J6100.01,$J6<=500),CEILING(($J6)+0.0 1,10)-0.01,IF($J6=500.01,CHOOSE(VLOOKUP($J6,Tbl_Categor y,2,TRUE),MAX(INT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01),"")))),IF(H6="3",IF(J6<200,INT(J6)+IF(RIGHT( F6*100,2)="00",1,RIGHT(F6*100,2)/100),(INT(J6/10)*10)+RIGHT(F6*100,3)/100),$F6)) Formula 2: =IF($H6=1,IF($J6<=1,$J6,IF(AND($J61,$J6<=1.05),0. 99,IF(AND($J6=1.06,$J6<=1.09),1.09,IF(AND($J6=1. 1,$J6<=9.99)+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09,IF(RIGHT($J6,2)*100<=9,19,(ROU NDUP($J6*100,-1)-1)/100)),IF($J6=10,CEILING($J6+0.01,0.5)-0.01))))),$F6) -- lmo |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
Improvement.... (Sorry for the incessant postings.)
"JoeU2004" wrote: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) Better: choose(match(round($J6,2),{0,50.01,100.01,500.01}, 1), ...1..., ...2..., ....3..., ...4...) where ...1... is the expression associated with your condition $J6<=50, ....2... is the expression for AND($J6=50.10,$J6<=100), ...3... is the expression for AND($J6100.01,$J6<=500) [sic], and ...4.. is the expression for $J6=500.01 (CHOOSE). 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Better: choose(match(round($J6,2),{0,1.01,1.06,1.10,10},1) , ...1..., ...2..., ....3..., ...4..., ...5...) where ...1... is the expression for $J6<=1, ...2... is the expression for AND($J61,$J6<=1.05), ...3... is the expression for AND($J6=1.06,$J6<=1.09), ...4... is the expression for AND($J6=1.1,$J6<=9.99), and ...5... is the expression for $J6=10 (CEILING). ----- original message ----- "JoeU2004" wrote in message ... "Lisa" wrote: For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. A quick-and-dirty solution might lie in finding ways to reduce the nesting level. (A better solution might lie in understanding your rounding rules and finding some simplfication in the overall algorithm, if any. But that would require in-depth knowledge of what you must do. I'm not going there.) Caveat: I have not actually tried the following myself. It will reduce the nesting level. According to my prototype, it should be enough; but I do not know for sure. Nonetheless, it might be worth a try. The overall structure of the combined formula is ostensibly: =if(H6="4", ..., if(H6="3", ..., if(H6="1", ..., $J6))) That could be written: =if(and(H6<{"1","3","4"}), $J6, choose(H6, ..., 0, ..., ...)) Note that the nesting level is reduced by one. FYI, because of the AND() pre-condition, CHOOSE will never see H6=2. The zero is just a place holder. Another simpflication lies in avoiding over-specification in your subordinate conditional expressions. Not only will this reduce the nesting level by one again, but also it will avoid discontinuities: holes in the logic that might exist due to rounding or numerical anomalies associated with numbers with decimal fractions. Specifically: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Note: I took the liberty of presuming the correction in the errata below. Caveat: The preceding assumes that J6 is explicitly rounded to 2 decimal places, not merely formatted as such. If that's not the case (explicit rounding), more tweaks to your algorithm might be desirable, depending on your tolerance. Errata .... You worte: the second formula i have tested all possible results and my answers are all correct. I believe I see your intent now. I still believe there is an error in the nesting of IF() functions; but it is not the error that I first thought. I am still puzzled by the structure of your second formula. I believe there is an error starting at: IF(AND($J6=1.1,$J6<=9.99)+IF(RIGHT((ROUNDUP(...)) *100,1)="0", ... I could be wrong. To follow along, I suggest that you copy-and-paste the formula into Notepad, break it down so that IF(condition,truePart,falsePart) is on separate lines, and number the parentheses of nesting levels like this: IF1(...)1. The following is what I see (with apologies if things do not align as intended): =IF1($H6=1, IF2($J6<=1, $J6, IF3(AND($J61,$J6<=1.05), 0.99, IF4(AND($J6=1.06,$J6<=1.09), 1.09, IF5(AND($J6=1.1,$J6<=9.99) +IF6(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09, IF7(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)7)6, IF6($J6=10,CEILING($J6+0.01,0.5)-0.01)6)5)4)3)2, $F6)1 Note that the expression IF($J6=10,...) is the true part of the expression IF(AND($J6=1.1,$J6<=9.99)+IF(...),...). If that is your intent, then I believe "IF5" will return FALSE when $J6<=9.99, as I said before. Explanation.... The expression AND(...)+IF(...) behaves like an OR operation: it is false only when both functions return false or zero. But I believe the "+IF(...)" expression is always greater than zero. So I believe AND(...)+IF(...) is always true, even when $J6<=9.99. Thus, when $J6<=9.99, IF($J6=10,...) is false. And since there is no false part, it will return FALSE. Moreover, if my analysis is correct, then as written, I do not believe "IF5" behaves at all as you intended. I suspect it should return 19 or either of the ROUNDUP results. I believe it does not. I suspect that your original intent was for "+IF(...)" to be ",IF(...)"; that is, it is intended to be the true part when 1.1<=$J6<=9.99. I suspect the formula got mangled in an attempt to solve a problem with too many nesting levels, taking RIGHT(ROUND(...)...) into account. Forgive me if I am wrong, and this is a red herring. But if my analysis is correct, then with the simplification that I suggested above, you might be able to correct this by reverting back to ",IF(...)" instead of "+IF(...)". However, if you do, I suspect you will encounter nesting level problems in the combined IF() expression, despite my suggestions. Sigh. ----- original message ----- "Lisa" wrote in message ... H6 is written as text and the second formula didn't reflect that, but in the second formula i have tested all possible results and my answers are all correct. For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. -- lmo "JoeU2004" wrote: "Lisa" wrote: I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error Since you did not show us how you are trying to combine the formulas it is difficult to say exactly what __your__ error is. But when __I__ try to combine the formula one way, I get an error because it exceeds the limit of 7 nested functions (8 including the outermost function) in Excel 2003. The combined formula, as I did it, would have 9 nested functions (10 including the outermost function). I tried to combine the two formulas by dropping "$F6))" at the end of Formula1 and pasting in Formula2 without the "=" at the beginning, adding "))" at the end to balance the new logic. (It would be easier to see, but it would get mangled if I tried to paste it here.) By the way, I suspect Formula2 has a defect. Near the end, you have IF6($J6=10,CEILING($J6+0.01,0.5)-0.01). Note that there is no "false value" part. It will return FALSE if $J6<10, which is usually undersirable. I also believe the combined formula would have other defects. For example, in Formula1, you treat H6 as text, whereas in Formula2, you treat H6 as numeric. ----- original message ----- "Lisa" wrote in message ... I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error for the +IF(RIGHT((ROUNDUP($J6,2))*100,1)="0" part of the formula. Below are the 2 folrmulas. Formula 1: =IF(H6="4",IF($J6<=50,CEILING(($J6)+0.01,1)-0.01,IF(AND($J6=50.01,$J6<=100),(ROUND($J6/5,0)*5)-0.01,IF(AND($J6100.01,$J6<=500),CEILING(($J6)+0.0 1,10)-0.01,IF($J6=500.01,CHOOSE(VLOOKUP($J6,Tbl_Categor y,2,TRUE),MAX(INT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01),"")))),IF(H6="3",IF(J6<200,INT(J6)+IF(RIGHT( F6*100,2)="00",1,RIGHT(F6*100,2)/100),(INT(J6/10)*10)+RIGHT(F6*100,3)/100),$F6)) Formula 2: =IF($H6=1,IF($J6<=1,$J6,IF(AND($J61,$J6<=1.05),0. 99,IF(AND($J6=1.06,$J6<=1.09),1.09,IF(AND($J6=1. 1,$J6<=9.99)+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09,IF(RIGHT($J6,2)*100<=9,19,(ROU NDUP($J6*100,-1)-1)/100)),IF($J6=10,CEILING($J6+0.01,0.5)-0.01))))),$F6) -- lmo |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
I will work on your suggestions and let you know how it works out. I was
also thinking of doing a VBA macro with case choices. Thanks for you help. -- lmo "JoeU2004" wrote: Improvement.... (Sorry for the incessant postings.) "JoeU2004" wrote: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) Better: choose(match(round($J6,2),{0,50.01,100.01,500.01}, 1), ...1..., ...2..., ....3..., ...4...) where ...1... is the expression associated with your condition $J6<=50, ....2... is the expression for AND($J6=50.10,$J6<=100), ...3... is the expression for AND($J6100.01,$J6<=500) [sic], and ...4.. is the expression for $J6=500.01 (CHOOSE). 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Better: choose(match(round($J6,2),{0,1.01,1.06,1.10,10},1) , ...1..., ...2..., ....3..., ...4..., ...5...) where ...1... is the expression for $J6<=1, ...2... is the expression for AND($J61,$J6<=1.05), ...3... is the expression for AND($J6=1.06,$J6<=1.09), ...4... is the expression for AND($J6=1.1,$J6<=9.99), and ...5... is the expression for $J6=10 (CEILING). ----- original message ----- "JoeU2004" wrote in message ... "Lisa" wrote: For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. A quick-and-dirty solution might lie in finding ways to reduce the nesting level. (A better solution might lie in understanding your rounding rules and finding some simplfication in the overall algorithm, if any. But that would require in-depth knowledge of what you must do. I'm not going there.) Caveat: I have not actually tried the following myself. It will reduce the nesting level. According to my prototype, it should be enough; but I do not know for sure. Nonetheless, it might be worth a try. The overall structure of the combined formula is ostensibly: =if(H6="4", ..., if(H6="3", ..., if(H6="1", ..., $J6))) That could be written: =if(and(H6<{"1","3","4"}), $J6, choose(H6, ..., 0, ..., ...)) Note that the nesting level is reduced by one. FYI, because of the AND() pre-condition, CHOOSE will never see H6=2. The zero is just a place holder. Another simpflication lies in avoiding over-specification in your subordinate conditional expressions. Not only will this reduce the nesting level by one again, but also it will avoid discontinuities: holes in the logic that might exist due to rounding or numerical anomalies associated with numbers with decimal fractions. Specifically: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Note: I took the liberty of presuming the correction in the errata below. Caveat: The preceding assumes that J6 is explicitly rounded to 2 decimal places, not merely formatted as such. If that's not the case (explicit rounding), more tweaks to your algorithm might be desirable, depending on your tolerance. Errata .... You worte: the second formula i have tested all possible results and my answers are all correct. I believe I see your intent now. I still believe there is an error in the nesting of IF() functions; but it is not the error that I first thought. I am still puzzled by the structure of your second formula. I believe there is an error starting at: IF(AND($J6=1.1,$J6<=9.99)+IF(RIGHT((ROUNDUP(...)) *100,1)="0", ... I could be wrong. To follow along, I suggest that you copy-and-paste the formula into Notepad, break it down so that IF(condition,truePart,falsePart) is on separate lines, and number the parentheses of nesting levels like this: IF1(...)1. The following is what I see (with apologies if things do not align as intended): =IF1($H6=1, IF2($J6<=1, $J6, IF3(AND($J61,$J6<=1.05), 0.99, IF4(AND($J6=1.06,$J6<=1.09), 1.09, IF5(AND($J6=1.1,$J6<=9.99) +IF6(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09, IF7(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)7)6, IF6($J6=10,CEILING($J6+0.01,0.5)-0.01)6)5)4)3)2, $F6)1 Note that the expression IF($J6=10,...) is the true part of the expression IF(AND($J6=1.1,$J6<=9.99)+IF(...),...). If that is your intent, then I believe "IF5" will return FALSE when $J6<=9.99, as I said before. Explanation.... The expression AND(...)+IF(...) behaves like an OR operation: it is false only when both functions return false or zero. But I believe the "+IF(...)" expression is always greater than zero. So I believe AND(...)+IF(...) is always true, even when $J6<=9.99. Thus, when $J6<=9.99, IF($J6=10,...) is false. And since there is no false part, it will return FALSE. Moreover, if my analysis is correct, then as written, I do not believe "IF5" behaves at all as you intended. I suspect it should return 19 or either of the ROUNDUP results. I believe it does not. I suspect that your original intent was for "+IF(...)" to be ",IF(...)"; that is, it is intended to be the true part when 1.1<=$J6<=9.99. I suspect the formula got mangled in an attempt to solve a problem with too many nesting levels, taking RIGHT(ROUND(...)...) into account. Forgive me if I am wrong, and this is a red herring. But if my analysis is correct, then with the simplification that I suggested above, you might be able to correct this by reverting back to ",IF(...)" instead of "+IF(...)". However, if you do, I suspect you will encounter nesting level problems in the combined IF() expression, despite my suggestions. Sigh. ----- original message ----- "Lisa" wrote in message ... H6 is written as text and the second formula didn't reflect that, but in the second formula i have tested all possible results and my answers are all correct. For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. -- lmo "JoeU2004" wrote: "Lisa" wrote: I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error Since you did not show us how you are trying to combine the formulas it is difficult to say exactly what __your__ error is. But when __I__ try to combine the formula one way, I get an error because it exceeds the limit of 7 nested functions (8 including the outermost function) in Excel 2003. The combined formula, as I did it, would have 9 nested functions (10 including the outermost function). I tried to combine the two formulas by dropping "$F6))" at the end of Formula1 and pasting in Formula2 without the "=" at the beginning, adding "))" at the end to balance the new logic. (It would be easier to see, but it would get mangled if I tried to paste it here.) By the way, I suspect Formula2 has a defect. Near the end, you have IF6($J6=10,CEILING($J6+0.01,0.5)-0.01). Note that there is no "false value" part. It will return FALSE if $J6<10, which is usually undersirable. I also believe the combined formula would have other defects. For example, in Formula1, you treat H6 as text, whereas in Formula2, you treat H6 as numeric. ----- original message ----- "Lisa" wrote in message ... I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error for the +IF(RIGHT((ROUNDUP($J6,2))*100,1)="0" part of the formula. Below are the 2 folrmulas. Formula 1: =IF(H6="4",IF($J6<=50,CEILING(($J6)+0.01,1)-0.01,IF(AND($J6=50.01,$J6<=100),(ROUND($J6/5,0)*5)-0.01,IF(AND($J6100.01,$J6<=500),CEILING(($J6)+0.0 1,10)-0.01,IF($J6=500.01,CHOOSE(VLOOKUP($J6,Tbl_Categor y,2,TRUE),MAX(INT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01),"")))),IF(H6="3",IF(J6<200,INT(J6)+IF(RIGHT( F6*100,2)="00",1,RIGHT(F6*100,2)/100),(INT(J6/10)*10)+RIGHT(F6*100,3)/100),$F6)) Formula 2: =IF($H6=1,IF($J6<=1,$J6,IF(AND($J61,$J6<=1.05),0. 99,IF(AND($J6=1.06,$J6<=1.09),1.09,IF(AND($J6=1. 1,$J6<=9.99)+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09,IF(RIGHT($J6,2)*100<=9,19,(ROU NDUP($J6*100,-1)-1)/100)),IF($J6=10,CEILING($J6+0.01,0.5)-0.01))))),$F6) -- lmo |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
The 3 range names are as follows.
Tbl_Category coulmnA columnB 500.01 4 1000.01 5 2000.01 6 Tbl_Category4 (represents 500.01 to 1000.00 = 19.99 39.99 49.99 69.99 89.99 99.99) columnD columnE 0 19.99 20.00 39.99 40.00 49.99 50.00 69.99 70.00 89.99 90.00 99.99 Tbl_Category5 (represents 1000.01 to 2000.00 = 29.99 49.99 69.99 99.99) columnD columnF 0 29.99 30.00 49.99 50.00 69.99 70.00 99.99 -- lmo "JoeU2004" wrote: Improvement.... (Sorry for the incessant postings.) "JoeU2004" wrote: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) Better: choose(match(round($J6,2),{0,50.01,100.01,500.01}, 1), ...1..., ...2..., ....3..., ...4...) where ...1... is the expression associated with your condition $J6<=50, ....2... is the expression for AND($J6=50.10,$J6<=100), ...3... is the expression for AND($J6100.01,$J6<=500) [sic], and ...4.. is the expression for $J6=500.01 (CHOOSE). 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Better: choose(match(round($J6,2),{0,1.01,1.06,1.10,10},1) , ...1..., ...2..., ....3..., ...4..., ...5...) where ...1... is the expression for $J6<=1, ...2... is the expression for AND($J61,$J6<=1.05), ...3... is the expression for AND($J6=1.06,$J6<=1.09), ...4... is the expression for AND($J6=1.1,$J6<=9.99), and ...5... is the expression for $J6=10 (CEILING). ----- original message ----- "JoeU2004" wrote in message ... "Lisa" wrote: For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. A quick-and-dirty solution might lie in finding ways to reduce the nesting level. (A better solution might lie in understanding your rounding rules and finding some simplfication in the overall algorithm, if any. But that would require in-depth knowledge of what you must do. I'm not going there.) Caveat: I have not actually tried the following myself. It will reduce the nesting level. According to my prototype, it should be enough; but I do not know for sure. Nonetheless, it might be worth a try. The overall structure of the combined formula is ostensibly: =if(H6="4", ..., if(H6="3", ..., if(H6="1", ..., $J6))) That could be written: =if(and(H6<{"1","3","4"}), $J6, choose(H6, ..., 0, ..., ...)) Note that the nesting level is reduced by one. FYI, because of the AND() pre-condition, CHOOSE will never see H6=2. The zero is just a place holder. Another simpflication lies in avoiding over-specification in your subordinate conditional expressions. Not only will this reduce the nesting level by one again, but also it will avoid discontinuities: holes in the logic that might exist due to rounding or numerical anomalies associated with numbers with decimal fractions. Specifically: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Note: I took the liberty of presuming the correction in the errata below. Caveat: The preceding assumes that J6 is explicitly rounded to 2 decimal places, not merely formatted as such. If that's not the case (explicit rounding), more tweaks to your algorithm might be desirable, depending on your tolerance. Errata .... You worte: the second formula i have tested all possible results and my answers are all correct. I believe I see your intent now. I still believe there is an error in the nesting of IF() functions; but it is not the error that I first thought. I am still puzzled by the structure of your second formula. I believe there is an error starting at: IF(AND($J6=1.1,$J6<=9.99)+IF(RIGHT((ROUNDUP(...)) *100,1)="0", ... I could be wrong. To follow along, I suggest that you copy-and-paste the formula into Notepad, break it down so that IF(condition,truePart,falsePart) is on separate lines, and number the parentheses of nesting levels like this: IF1(...)1. The following is what I see (with apologies if things do not align as intended): =IF1($H6=1, IF2($J6<=1, $J6, IF3(AND($J61,$J6<=1.05), 0.99, IF4(AND($J6=1.06,$J6<=1.09), 1.09, IF5(AND($J6=1.1,$J6<=9.99) +IF6(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09, IF7(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)7)6, IF6($J6=10,CEILING($J6+0.01,0.5)-0.01)6)5)4)3)2, $F6)1 Note that the expression IF($J6=10,...) is the true part of the expression IF(AND($J6=1.1,$J6<=9.99)+IF(...),...). If that is your intent, then I believe "IF5" will return FALSE when $J6<=9.99, as I said before. Explanation.... The expression AND(...)+IF(...) behaves like an OR operation: it is false only when both functions return false or zero. But I believe the "+IF(...)" expression is always greater than zero. So I believe AND(...)+IF(...) is always true, even when $J6<=9.99. Thus, when $J6<=9.99, IF($J6=10,...) is false. And since there is no false part, it will return FALSE. Moreover, if my analysis is correct, then as written, I do not believe "IF5" behaves at all as you intended. I suspect it should return 19 or either of the ROUNDUP results. I believe it does not. I suspect that your original intent was for "+IF(...)" to be ",IF(...)"; that is, it is intended to be the true part when 1.1<=$J6<=9.99. I suspect the formula got mangled in an attempt to solve a problem with too many nesting levels, taking RIGHT(ROUND(...)...) into account. Forgive me if I am wrong, and this is a red herring. But if my analysis is correct, then with the simplification that I suggested above, you might be able to correct this by reverting back to ",IF(...)" instead of "+IF(...)". However, if you do, I suspect you will encounter nesting level problems in the combined IF() expression, despite my suggestions. Sigh. ----- original message ----- "Lisa" wrote in message ... H6 is written as text and the second formula didn't reflect that, but in the second formula i have tested all possible results and my answers are all correct. For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. -- lmo "JoeU2004" wrote: "Lisa" wrote: I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error Since you did not show us how you are trying to combine the formulas it is difficult to say exactly what __your__ error is. But when __I__ try to combine the formula one way, I get an error because it exceeds the limit of 7 nested functions (8 including the outermost function) in Excel 2003. The combined formula, as I did it, would have 9 nested functions (10 including the outermost function). I tried to combine the two formulas by dropping "$F6))" at the end of Formula1 and pasting in Formula2 without the "=" at the beginning, adding "))" at the end to balance the new logic. (It would be easier to see, but it would get mangled if I tried to paste it here.) By the way, I suspect Formula2 has a defect. Near the end, you have IF6($J6=10,CEILING($J6+0.01,0.5)-0.01). Note that there is no "false value" part. It will return FALSE if $J6<10, which is usually undersirable. I also believe the combined formula would have other defects. For example, in Formula1, you treat H6 as text, whereas in Formula2, you treat H6 as numeric. ----- original message ----- "Lisa" wrote in message ... I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error for the +IF(RIGHT((ROUNDUP($J6,2))*100,1)="0" part of the formula. Below are the 2 folrmulas. Formula 1: =IF(H6="4",IF($J6<=50,CEILING(($J6)+0.01,1)-0.01,IF(AND($J6=50.01,$J6<=100),(ROUND($J6/5,0)*5)-0.01,IF(AND($J6100.01,$J6<=500),CEILING(($J6)+0.0 1,10)-0.01,IF($J6=500.01,CHOOSE(VLOOKUP($J6,Tbl_Categor y,2,TRUE),MAX(INT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01),"")))),IF(H6="3",IF(J6<200,INT(J6)+IF(RIGHT( F6*100,2)="00",1,RIGHT(F6*100,2)/100),(INT(J6/10)*10)+RIGHT(F6*100,3)/100),$F6)) Formula 2: =IF($H6=1,IF($J6<=1,$J6,IF(AND($J61,$J6<=1.05),0. 99,IF(AND($J6=1.06,$J6<=1.09),1.09,IF(AND($J6=1. 1,$J6<=9.99)+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09,IF(RIGHT($J6,2)*100<=9,19,(ROU NDUP($J6*100,-1)-1)/100)),IF($J6=10,CEILING($J6+0.01,0.5)-0.01))))),$F6) -- lmo |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
I am not sure what purpose your follow-up posting intends to serve. But
just to be clear: I am not trying to improve your original formulas in any substantial way. I am simply suggesting a mechanical way to stitch them together without incurring the limit of 7 nested function calls (8 including the outermost). The previous posting of my suggestion might have been too abstract and difficult to understand. The following is my attempt to implement my suggestion. It is formatted in a manner to try to make the structure clear. My apologies if it is mangled by the news server or news readers. Please read the notes following my implementation. =IF(AND(H6<{"1","3","4"}), $F6, CHOOSE(H6, CHOOSE(MATCH(ROUND($J6,2),{0,1.01,1.06,1.1,10},1), $J6, 0.99, 1.09, IF(RIGHT(ROUNDUP($J6,2)*100,1)="0", ROUNDUP($J6,1)+0.09, IF(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100))), 0, IF(J6<200, INT(J6) +IF(RIGHT(F6*100,2)="00", 1, RIGHT(F6*100,2)/100), INT(J6/10)*10+RIGHT(F6*100,3)/100), CHOOSE(MATCH(ROUND($J6,2),{0,50.01,100.01,500.01}, 1), CEILING($J6+0.01,1)-0.01, ROUND($J6/5,0)*5-0.01, CEILING($J6+0.01,10)-0.01, CHOOSE(VLOOKUP($J6,Tbl_Category,2,TRUE), MAX(INT($J6-0.49)+.99), MAX(INT(($J6-2.49)/5)*5+4.99), MAX(INT(($J6-4.99)/10)*10+9.99), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE), CEILING($J6+0.01,50)-0.01)))) Notes: For my trouble, please comment on the following notes, starting with #4. 1. Although I made an effort to replicate the logic of your formulas correctly, I did not test this implementation beyond copy-and-pasting it into Excel to ensure that it is syntactically correct. 2. Unfortunately, we cannot copy-and-paste the text above directly into Excel; Excel complains that the formula is too long. I copy-and-pasted the text into Notepad, eliminated the leading spaces in every line, then copy-and-pasted from Notepad into Excel successfully. 3. The maximum nested function depth is 6 (including the outermost), if I counted correctly. That gives you some wiggle room, since the maximum supported depth in Excel 2003 is 8 (including the outermost). 4. I left the MAX(INT) expressions in the last CHOOSE argument list as they were in the original formula. However, I think the use of MAX there is superfluous. MAX only makes sense with two or more arguments; so I was tempted to replace "+" with a comma. But looking at the other related expressions, I suspect the intent is to add a factor to the INT expression in order to ensure that the result ends in ".99". Is that right? In that case, you can remove the use of MAX. 5. I incorporated the "correction" that I noted in a previous posting, interpreting IF(AND(...)+IF(...),...) in the original formula as IF(AND(...),IF(...),...). Is that right? If not, the 2nd CHOOSE argument list needs to be changed significantly. Is this helpful? ----- original message ----- "Lisa" wrote in message ... The 3 range names are as follows. Tbl_Category coulmnA columnB 500.01 4 1000.01 5 2000.01 6 Tbl_Category4 (represents 500.01 to 1000.00 = 19.99 39.99 49.99 69.99 89.99 99.99) columnD columnE 0 19.99 20.00 39.99 40.00 49.99 50.00 69.99 70.00 89.99 90.00 99.99 Tbl_Category5 (represents 1000.01 to 2000.00 = 29.99 49.99 69.99 99.99) columnD columnF 0 29.99 30.00 49.99 50.00 69.99 70.00 99.99 -- lmo "JoeU2004" wrote: Improvement.... (Sorry for the incessant postings.) "JoeU2004" wrote: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) Better: choose(match(round($J6,2),{0,50.01,100.01,500.01}, 1), ...1..., ...2..., ....3..., ...4...) where ...1... is the expression associated with your condition $J6<=50, ....2... is the expression for AND($J6=50.10,$J6<=100), ...3... is the expression for AND($J6100.01,$J6<=500) [sic], and ...4.. is the expression for $J6=500.01 (CHOOSE). 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Better: choose(match(round($J6,2),{0,1.01,1.06,1.10,10},1) , ...1..., ...2..., ....3..., ...4..., ...5...) where ...1... is the expression for $J6<=1, ...2... is the expression for AND($J61,$J6<=1.05), ...3... is the expression for AND($J6=1.06,$J6<=1.09), ...4... is the expression for AND($J6=1.1,$J6<=9.99), and ...5... is the expression for $J6=10 (CEILING). ----- original message ----- "JoeU2004" wrote in message ... "Lisa" wrote: For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. A quick-and-dirty solution might lie in finding ways to reduce the nesting level. (A better solution might lie in understanding your rounding rules and finding some simplfication in the overall algorithm, if any. But that would require in-depth knowledge of what you must do. I'm not going there.) Caveat: I have not actually tried the following myself. It will reduce the nesting level. According to my prototype, it should be enough; but I do not know for sure. Nonetheless, it might be worth a try. The overall structure of the combined formula is ostensibly: =if(H6="4", ..., if(H6="3", ..., if(H6="1", ..., $J6))) That could be written: =if(and(H6<{"1","3","4"}), $J6, choose(H6, ..., 0, ..., ...)) Note that the nesting level is reduced by one. FYI, because of the AND() pre-condition, CHOOSE will never see H6=2. The zero is just a place holder. Another simpflication lies in avoiding over-specification in your subordinate conditional expressions. Not only will this reduce the nesting level by one again, but also it will avoid discontinuities: holes in the logic that might exist due to rounding or numerical anomalies associated with numbers with decimal fractions. Specifically: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Note: I took the liberty of presuming the correction in the errata below. Caveat: The preceding assumes that J6 is explicitly rounded to 2 decimal places, not merely formatted as such. If that's not the case (explicit rounding), more tweaks to your algorithm might be desirable, depending on your tolerance. Errata .... You worte: the second formula i have tested all possible results and my answers are all correct. I believe I see your intent now. I still believe there is an error in the nesting of IF() functions; but it is not the error that I first thought. I am still puzzled by the structure of your second formula. I believe there is an error starting at: IF(AND($J6=1.1,$J6<=9.99)+IF(RIGHT((ROUNDUP(...)) *100,1)="0", ... I could be wrong. To follow along, I suggest that you copy-and-paste the formula into Notepad, break it down so that IF(condition,truePart,falsePart) is on separate lines, and number the parentheses of nesting levels like this: IF1(...)1. The following is what I see (with apologies if things do not align as intended): =IF1($H6=1, IF2($J6<=1, $J6, IF3(AND($J61,$J6<=1.05), 0.99, IF4(AND($J6=1.06,$J6<=1.09), 1.09, IF5(AND($J6=1.1,$J6<=9.99) +IF6(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09, IF7(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)7)6, IF6($J6=10,CEILING($J6+0.01,0.5)-0.01)6)5)4)3)2, $F6)1 Note that the expression IF($J6=10,...) is the true part of the expression IF(AND($J6=1.1,$J6<=9.99)+IF(...),...). If that is your intent, then I believe "IF5" will return FALSE when $J6<=9.99, as I said before. Explanation.... The expression AND(...)+IF(...) behaves like an OR operation: it is false only when both functions return false or zero. But I believe the "+IF(...)" expression is always greater than zero. So I believe AND(...)+IF(...) is always true, even when $J6<=9.99. Thus, when $J6<=9.99, IF($J6=10,...) is false. And since there is no false part, it will return FALSE. Moreover, if my analysis is correct, then as written, I do not believe "IF5" behaves at all as you intended. I suspect it should return 19 or either of the ROUNDUP results. I believe it does not. I suspect that your original intent was for "+IF(...)" to be ",IF(...)"; that is, it is intended to be the true part when 1.1<=$J6<=9.99. I suspect the formula got mangled in an attempt to solve a problem with too many nesting levels, taking RIGHT(ROUND(...)...) into account. Forgive me if I am wrong, and this is a red herring. But if my analysis is correct, then with the simplification that I suggested above, you might be able to correct this by reverting back to ",IF(...)" instead of "+IF(...)". However, if you do, I suspect you will encounter nesting level problems in the combined IF() expression, despite my suggestions. Sigh. ----- original message ----- "Lisa" wrote in message ... H6 is written as text and the second formula didn't reflect that, but in the second formula i have tested all possible results and my answers are all correct. For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. -- lmo "JoeU2004" wrote: "Lisa" wrote: I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error Since you did not show us how you are trying to combine the formulas it is difficult to say exactly what __your__ error is. But when __I__ try to combine the formula one way, I get an error because it exceeds the limit of 7 nested functions (8 including the outermost function) in Excel 2003. The combined formula, as I did it, would have 9 nested functions (10 including the outermost function). I tried to combine the two formulas by dropping "$F6))" at the end of Formula1 and pasting in Formula2 without the "=" at the beginning, adding "))" at the end to balance the new logic. (It would be easier to see, but it would get mangled if I tried to paste it here.) By the way, I suspect Formula2 has a defect. Near the end, you have IF6($J6=10,CEILING($J6+0.01,0.5)-0.01). Note that there is no "false value" part. It will return FALSE if $J6<10, which is usually undersirable. I also believe the combined formula would have other defects. For example, in Formula1, you treat H6 as text, whereas in Formula2, you treat H6 as numeric. ----- original message ----- "Lisa" wrote in message ... I have 2 files with similar formulas. Now i need both those formulas to be in one cell. I am getting and error for the +IF(RIGHT((ROUNDUP($J6,2))*100,1)="0" part of the formula. Below are the 2 folrmulas. Formula 1: =IF(H6="4",IF($J6<=50,CEILING(($J6)+0.01,1)-0.01,IF(AND($J6=50.01,$J6<=100),(ROUND($J6/5,0)*5)-0.01,IF(AND($J6100.01,$J6<=500),CEILING(($J6)+0.0 1,10)-0.01,IF($J6=500.01,CHOOSE(VLOOKUP($J6,Tbl_Categor y,2,TRUE),MAX(INT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01),"")))),IF(H6="3",IF(J6<200,INT(J6)+IF(RIGHT( F6*100,2)="00",1,RIGHT(F6*100,2)/100),(INT(J6/10)*10)+RIGHT(F6*100,3)/100),$F6)) Formula 2: =IF($H6=1,IF($J6<=1,$J6,IF(AND($J61,$J6<=1.05),0. 99,IF(AND($J6=1.06,$J6<=1.09),1.09,IF(AND($J6=1. 1,$J6<=9.99)+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09,IF(RIGHT($J6,2)*100<=9,19,(ROU NDUP($J6*100,-1)-1)/100)),IF($J6=10,CEILING($J6+0.01,0.5)-0.01))))),$F6) -- lmo |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
I thought you wanted to know what information was in the range names that is
why i posted that. Any way in trying your new formula rule 3 and 4 are working correclty but 1 is not. I am getting a value# error for anything in cell J10 10.00 or greater. I am also have an issue with if cell J10 has 1.01 through 1.05 the answer = 0. Let me give you a synopsis of rule 1 and hopefully you can help me on this. below is the original rule 1 formula. =IF($H13=1,IF($J13<1,$J13,IF(AND($J13=1,$J13<=1.0 5),0.99,IF(AND($J13=1.06,$J13<=1.09),1.09,IF(AND( $J13=1.1,$J13<=9.99),+IF(RIGHT((ROUNDUP($J13,2))* 100,1)="0",ROUNDUP($J13,1)+0.09,IF(RIGHT($J13,2)*1 00<=9,19,(ROUNDUP($J13*100,-1)-1)/100)),IF($J13=10,CEILING($J13+0.01,0.5)-0.01,$F13)))))) Rules for 1: if cell J13 is <1.00 no adjustment if cell J13 is <1.05 adjust to .99 if cell J13 is between 1.06 and 1.09 (inclusive) adjust to 1.09 if cell J13 is between 1.10 and 9.99 (inclusive) and cents portion is .09 or less, adjust cents to .19 if cell J13 is between 1.10 and 9.99 (inclusive) and cents portion is .10 or more, adjust cents up to the next .x9 cent ending if cell J13 is 10.00 or higher and cents portion is .49 or less, adjust cents to .49 ending if cell J13 is 10.00 or higher and cents portion is .50 or more, adjust cents to .99 ending Thanks -- lmo "JoeU2004" wrote: I am not sure what purpose your follow-up posting intends to serve. But just to be clear: I am not trying to improve your original formulas in any substantial way. I am simply suggesting a mechanical way to stitch them together without incurring the limit of 7 nested function calls (8 including the outermost). The previous posting of my suggestion might have been too abstract and difficult to understand. The following is my attempt to implement my suggestion. It is formatted in a manner to try to make the structure clear. My apologies if it is mangled by the news server or news readers. Please read the notes following my implementation. =IF(AND(H6<{"1","3","4"}), $F6, CHOOSE(H6, CHOOSE(MATCH(ROUND($J6,2),{0,1.01,1.06,1.1,10},1), $J6, 0.99, 1.09, IF(RIGHT(ROUNDUP($J6,2)*100,1)="0", ROUNDUP($J6,1)+0.09, IF(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100))), 0, IF(J6<200, INT(J6) +IF(RIGHT(F6*100,2)="00", 1, RIGHT(F6*100,2)/100), INT(J6/10)*10+RIGHT(F6*100,3)/100), CHOOSE(MATCH(ROUND($J6,2),{0,50.01,100.01,500.01}, 1), CEILING($J6+0.01,1)-0.01, ROUND($J6/5,0)*5-0.01, CEILING($J6+0.01,10)-0.01, CHOOSE(VLOOKUP($J6,Tbl_Category,2,TRUE), MAX(INT($J6-0.49)+.99), MAX(INT(($J6-2.49)/5)*5+4.99), MAX(INT(($J6-4.99)/10)*10+9.99), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE), CEILING($J6+0.01,50)-0.01)))) Notes: For my trouble, please comment on the following notes, starting with #4. 1. Although I made an effort to replicate the logic of your formulas correctly, I did not test this implementation beyond copy-and-pasting it into Excel to ensure that it is syntactically correct. 2. Unfortunately, we cannot copy-and-paste the text above directly into Excel; Excel complains that the formula is too long. I copy-and-pasted the text into Notepad, eliminated the leading spaces in every line, then copy-and-pasted from Notepad into Excel successfully. 3. The maximum nested function depth is 6 (including the outermost), if I counted correctly. That gives you some wiggle room, since the maximum supported depth in Excel 2003 is 8 (including the outermost). 4. I left the MAX(INT) expressions in the last CHOOSE argument list as they were in the original formula. However, I think the use of MAX there is superfluous. MAX only makes sense with two or more arguments; so I was tempted to replace "+" with a comma. But looking at the other related expressions, I suspect the intent is to add a factor to the INT expression in order to ensure that the result ends in ".99". Is that right? In that case, you can remove the use of MAX. 5. I incorporated the "correction" that I noted in a previous posting, interpreting IF(AND(...)+IF(...),...) in the original formula as IF(AND(...),IF(...),...). Is that right? If not, the 2nd CHOOSE argument list needs to be changed significantly. Is this helpful? ----- original message ----- "Lisa" wrote in message ... The 3 range names are as follows. Tbl_Category coulmnA columnB 500.01 4 1000.01 5 2000.01 6 Tbl_Category4 (represents 500.01 to 1000.00 = 19.99 39.99 49.99 69.99 89.99 99.99) columnD columnE 0 19.99 20.00 39.99 40.00 49.99 50.00 69.99 70.00 89.99 90.00 99.99 Tbl_Category5 (represents 1000.01 to 2000.00 = 29.99 49.99 69.99 99.99) columnD columnF 0 29.99 30.00 49.99 50.00 69.99 70.00 99.99 -- lmo "JoeU2004" wrote: Improvement.... (Sorry for the incessant postings.) "JoeU2004" wrote: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) Better: choose(match(round($J6,2),{0,50.01,100.01,500.01}, 1), ...1..., ...2..., ....3..., ...4...) where ...1... is the expression associated with your condition $J6<=50, ....2... is the expression for AND($J6=50.10,$J6<=100), ...3... is the expression for AND($J6100.01,$J6<=500) [sic], and ...4.. is the expression for $J6=500.01 (CHOOSE). 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Better: choose(match(round($J6,2),{0,1.01,1.06,1.10,10},1) , ...1..., ...2..., ....3..., ...4..., ...5...) where ...1... is the expression for $J6<=1, ...2... is the expression for AND($J61,$J6<=1.05), ...3... is the expression for AND($J6=1.06,$J6<=1.09), ...4... is the expression for AND($J6=1.1,$J6<=9.99), and ...5... is the expression for $J6=10 (CEILING). ----- original message ----- "JoeU2004" wrote in message ... "Lisa" wrote: For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. A quick-and-dirty solution might lie in finding ways to reduce the nesting level. (A better solution might lie in understanding your rounding rules and finding some simplfication in the overall algorithm, if any. But that would require in-depth knowledge of what you must do. I'm not going there.) Caveat: I have not actually tried the following myself. It will reduce the nesting level. According to my prototype, it should be enough; but I do not know for sure. Nonetheless, it might be worth a try. The overall structure of the combined formula is ostensibly: =if(H6="4", ..., if(H6="3", ..., if(H6="1", ..., $J6))) That could be written: =if(and(H6<{"1","3","4"}), $J6, choose(H6, ..., 0, ..., ...)) Note that the nesting level is reduced by one. FYI, because of the AND() pre-condition, CHOOSE will never see H6=2. The zero is just a place holder. Another simpflication lies in avoiding over-specification in your subordinate conditional expressions. Not only will this reduce the nesting level by one again, but also it will avoid discontinuities: holes in the logic that might exist due to rounding or numerical anomalies associated with numbers with decimal fractions. Specifically: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Note: I took the liberty of presuming the correction in the errata below. Caveat: The preceding assumes that J6 is explicitly rounded to 2 decimal places, not merely formatted as such. If that's not the case (explicit rounding), more tweaks to your algorithm might be desirable, depending on your tolerance. Errata .... You worte: the second formula i have tested all possible results and my answers are all correct. I believe I see your intent now. I still believe there is an error in the nesting of IF() functions; but it is not the error that I first thought. I am still puzzled by the structure of your second formula. I believe there is an error starting at: IF(AND($J6=1.1,$J6<=9.99)+IF(RIGHT((ROUNDUP(...)) *100,1)="0", ... I could be wrong. To follow along, I suggest that you copy-and-paste the formula into Notepad, break it down so that IF(condition,truePart,falsePart) is on separate lines, and number the parentheses of nesting levels like this: IF1(...)1. The following is what I see (with apologies if things do not align as intended): =IF1($H6=1, IF2($J6<=1, $J6, IF3(AND($J61,$J6<=1.05), 0.99, IF4(AND($J6=1.06,$J6<=1.09), 1.09, IF5(AND($J6=1.1,$J6<=9.99) +IF6(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09, IF7(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)7)6, IF6($J6=10,CEILING($J6+0.01,0.5)-0.01)6)5)4)3)2, $F6)1 Note that the expression IF($J6=10,...) is the true part of the expression IF(AND($J6=1.1,$J6<=9.99)+IF(...),...). If that is your intent, then I believe "IF5" will return FALSE when $J6<=9.99, as I said before. Explanation.... |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
"Lisa" wrote:
I am getting a value# error for anything in cell J10 10.00 or greater. My mistake: I had omitted the argument for CHOOSE index 5 corresponding to IF($J6=10,CEILING($J6+0.01,0.5)-0.01) in your original posting. See the complete updated formula below. I am also have an issue with if cell J10 has 1.01 through 1.05 the answer = 0 I cannot help with you that without seeing exactly (by copy-and-paste) what you entered. In my formulation, a value in that range should result in a CHOOSE index of 2; and the expression corresponding to that index is the constant 0.99. Ostensibly, there is no way that can result in 0. Perhaps a formatting error. Perhaps a typo when you entered the formula. My complete updated prototype (based on your original posting): =IF(AND(H6<{"1","3","4"}), $F6, CHOOSE(H6, CHOOSE(MATCH(ROUND($J6,2),{0,1.01,1.06,1.1,10},1), $J6, 0.99, 1.09, IF(RIGHT(ROUNDUP($J6,2)*100,1)="0", ROUNDUP($J6,1)+0.09, IF(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)), CEILING($J6+0.01,0.5)-0.01)), 0, IF(J6<200, INT(J6) +IF(RIGHT(F6*100,2)="00", 1, RIGHT(F6*100,2)/100), INT(J6/10)*10+RIGHT(F6*100,3)/100), CHOOSE(MATCH(ROUND($J6,2),{0,50.01,100.01,500.01}, 1), CEILING($J6+0.01,1)-0.01, ROUND($J6/5,0)*5-0.01, CEILING($J6+0.01,10)-0.01, CHOOSE(VLOOKUP($J6,Tbl_Category,2,TRUE), MAX(INT($J6-0.49),0.99), MAX(INT(($J6-2.49)/5)*5,4.99), MAX(INT(($J6-4.99)/10)*10,9.99), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE), CEILING($J6+0.01,50)-0.01)))) ----- original posting ----- "Lisa" wrote in message ... I thought you wanted to know what information was in the range names that is why i posted that. Any way in trying your new formula rule 3 and 4 are working correclty but 1 is not. I am getting a value# error for anything in cell J10 10.00 or greater. I am also have an issue with if cell J10 has 1.01 through 1.05 the answer = 0. Let me give you a synopsis of rule 1 and hopefully you can help me on this. below is the original rule 1 formula. =IF($H13=1,IF($J13<1,$J13,IF(AND($J13=1,$J13<=1.0 5),0.99,IF(AND($J13=1.06,$J13<=1.09),1.09,IF(AND( $J13=1.1,$J13<=9.99),+IF(RIGHT((ROUNDUP($J13,2))* 100,1)="0",ROUNDUP($J13,1)+0.09,IF(RIGHT($J13,2)*1 00<=9,19,(ROUNDUP($J13*100,-1)-1)/100)),IF($J13=10,CEILING($J13+0.01,0.5)-0.01,$F13)))))) Rules for 1: if cell J13 is <1.00 no adjustment if cell J13 is <1.05 adjust to .99 if cell J13 is between 1.06 and 1.09 (inclusive) adjust to 1.09 if cell J13 is between 1.10 and 9.99 (inclusive) and cents portion is .09 or less, adjust cents to .19 if cell J13 is between 1.10 and 9.99 (inclusive) and cents portion is .10 or more, adjust cents up to the next .x9 cent ending if cell J13 is 10.00 or higher and cents portion is .49 or less, adjust cents to .49 ending if cell J13 is 10.00 or higher and cents portion is .50 or more, adjust cents to .99 ending Thanks -- lmo "JoeU2004" wrote: I am not sure what purpose your follow-up posting intends to serve. But just to be clear: I am not trying to improve your original formulas in any substantial way. I am simply suggesting a mechanical way to stitch them together without incurring the limit of 7 nested function calls (8 including the outermost). The previous posting of my suggestion might have been too abstract and difficult to understand. The following is my attempt to implement my suggestion. It is formatted in a manner to try to make the structure clear. My apologies if it is mangled by the news server or news readers. Please read the notes following my implementation. =IF(AND(H6<{"1","3","4"}), $F6, CHOOSE(H6, CHOOSE(MATCH(ROUND($J6,2),{0,1.01,1.06,1.1,10},1), $J6, 0.99, 1.09, IF(RIGHT(ROUNDUP($J6,2)*100,1)="0", ROUNDUP($J6,1)+0.09, IF(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100))), 0, IF(J6<200, INT(J6) +IF(RIGHT(F6*100,2)="00", 1, RIGHT(F6*100,2)/100), INT(J6/10)*10+RIGHT(F6*100,3)/100), CHOOSE(MATCH(ROUND($J6,2),{0,50.01,100.01,500.01}, 1), CEILING($J6+0.01,1)-0.01, ROUND($J6/5,0)*5-0.01, CEILING($J6+0.01,10)-0.01, CHOOSE(VLOOKUP($J6,Tbl_Category,2,TRUE), MAX(INT($J6-0.49)+.99), MAX(INT(($J6-2.49)/5)*5+4.99), MAX(INT(($J6-4.99)/10)*10+9.99), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE), CEILING($J6+0.01,50)-0.01)))) Notes: For my trouble, please comment on the following notes, starting with #4. 1. Although I made an effort to replicate the logic of your formulas correctly, I did not test this implementation beyond copy-and-pasting it into Excel to ensure that it is syntactically correct. 2. Unfortunately, we cannot copy-and-paste the text above directly into Excel; Excel complains that the formula is too long. I copy-and-pasted the text into Notepad, eliminated the leading spaces in every line, then copy-and-pasted from Notepad into Excel successfully. 3. The maximum nested function depth is 6 (including the outermost), if I counted correctly. That gives you some wiggle room, since the maximum supported depth in Excel 2003 is 8 (including the outermost). 4. I left the MAX(INT) expressions in the last CHOOSE argument list as they were in the original formula. However, I think the use of MAX there is superfluous. MAX only makes sense with two or more arguments; so I was tempted to replace "+" with a comma. But looking at the other related expressions, I suspect the intent is to add a factor to the INT expression in order to ensure that the result ends in ".99". Is that right? In that case, you can remove the use of MAX. 5. I incorporated the "correction" that I noted in a previous posting, interpreting IF(AND(...)+IF(...),...) in the original formula as IF(AND(...),IF(...),...). Is that right? If not, the 2nd CHOOSE argument list needs to be changed significantly. Is this helpful? ----- original message ----- "Lisa" wrote in message ... The 3 range names are as follows. Tbl_Category coulmnA columnB 500.01 4 1000.01 5 2000.01 6 Tbl_Category4 (represents 500.01 to 1000.00 = 19.99 39.99 49.99 69.99 89.99 99.99) columnD columnE 0 19.99 20.00 39.99 40.00 49.99 50.00 69.99 70.00 89.99 90.00 99.99 Tbl_Category5 (represents 1000.01 to 2000.00 = 29.99 49.99 69.99 99.99) columnD columnF 0 29.99 30.00 49.99 50.00 69.99 70.00 99.99 -- lmo "JoeU2004" wrote: Improvement.... (Sorry for the incessant postings.) "JoeU2004" wrote: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) Better: choose(match(round($J6,2),{0,50.01,100.01,500.01}, 1), ...1..., ...2..., ....3..., ...4...) where ...1... is the expression associated with your condition $J6<=50, ....2... is the expression for AND($J6=50.10,$J6<=100), ...3... is the expression for AND($J6100.01,$J6<=500) [sic], and ...4.. is the expression for $J6=500.01 (CHOOSE). 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Better: choose(match(round($J6,2),{0,1.01,1.06,1.10,10},1) , ...1..., ...2..., ....3..., ...4..., ...5...) where ...1... is the expression for $J6<=1, ...2... is the expression for AND($J61,$J6<=1.05), ...3... is the expression for AND($J6=1.06,$J6<=1.09), ...4... is the expression for AND($J6=1.1,$J6<=9.99), and ...5... is the expression for $J6=10 (CEILING). ----- original message ----- "JoeU2004" wrote in message ... "Lisa" wrote: For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific rounding rule applied. I have one excel file for rules 4 and 5 and a second file for rule 1 now i need one file for all 3 rules; if none of those rules exist cell f6 stays the same. A quick-and-dirty solution might lie in finding ways to reduce the nesting level. (A better solution might lie in understanding your rounding rules and finding some simplfication in the overall algorithm, if any. But that would require in-depth knowledge of what you must do. I'm not going there.) Caveat: I have not actually tried the following myself. It will reduce the nesting level. According to my prototype, it should be enough; but I do not know for sure. Nonetheless, it might be worth a try. The overall structure of the combined formula is ostensibly: =if(H6="4", ..., if(H6="3", ..., if(H6="1", ..., $J6))) That could be written: =if(and(H6<{"1","3","4"}), $J6, choose(H6, ..., 0, ..., ...)) Note that the nesting level is reduced by one. FYI, because of the AND() pre-condition, CHOOSE will never see H6=2. The zero is just a place holder. Another simpflication lies in avoiding over-specification in your subordinate conditional expressions. Not only will this reduce the nesting level by one again, but also it will avoid discontinuities: holes in the logic that might exist due to rounding or numerical anomalies associated with numbers with decimal fractions. Specifically: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Note: I took the liberty of presuming the correction in the errata below. Caveat: The preceding assumes that J6 is explicitly rounded to 2 decimal places, not merely formatted as such. If that's not the case (explicit rounding), more tweaks to your algorithm might be desirable, depending on your tolerance. Errata .... You worte: the second formula i have tested all possible results and my answers are all correct. I believe I see your intent now. I still believe there is an error in the nesting of IF() functions; but it is not the error that I first thought. I am still puzzled by the structure of your second formula. I believe there is an error starting at: IF(AND($J6=1.1,$J6<=9.99)+IF(RIGHT((ROUNDUP(...)) *100,1)="0", ... I could be wrong. To follow along, I suggest that you copy-and-paste the formula into Notepad, break it down so that IF(condition,truePart,falsePart) is on separate lines, and number the parentheses of nesting levels like this: IF1(...)1. The following is what I see (with apologies if things do not align as intended): =IF1($H6=1, IF2($J6<=1, $J6, IF3(AND($J61,$J6<=1.05), 0.99, IF4(AND($J6=1.06,$J6<=1.09), 1.09, IF5(AND($J6=1.1,$J6<=9.99) +IF6(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09, IF7(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)7)6, IF6($J6=10,CEILING($J6+0.01,0.5)-0.01)6)5)4)3)2, $F6)1 Note that the expression IF($J6=10,...) is the true part of the expression IF(AND($J6=1.1,$J6<=9.99)+IF(...),...). If that is your intent, then I believe "IF5" will return FALSE when $J6<=9.99, as I said before. Explanation.... |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
Joe,
The final formula works great, thanks so much for your help. -- lmo "JoeU2004" wrote: "Lisa" wrote: I am getting a value# error for anything in cell J10 10.00 or greater. My mistake: I had omitted the argument for CHOOSE index 5 corresponding to IF($J6=10,CEILING($J6+0.01,0.5)-0.01) in your original posting. See the complete updated formula below. I am also have an issue with if cell J10 has 1.01 through 1.05 the answer = 0 I cannot help with you that without seeing exactly (by copy-and-paste) what you entered. In my formulation, a value in that range should result in a CHOOSE index of 2; and the expression corresponding to that index is the constant 0.99. Ostensibly, there is no way that can result in 0. Perhaps a formatting error. Perhaps a typo when you entered the formula. My complete updated prototype (based on your original posting): =IF(AND(H6<{"1","3","4"}), $F6, CHOOSE(H6, CHOOSE(MATCH(ROUND($J6,2),{0,1.01,1.06,1.1,10},1), $J6, 0.99, 1.09, IF(RIGHT(ROUNDUP($J6,2)*100,1)="0", ROUNDUP($J6,1)+0.09, IF(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)), CEILING($J6+0.01,0.5)-0.01)), 0, IF(J6<200, INT(J6) +IF(RIGHT(F6*100,2)="00", 1, RIGHT(F6*100,2)/100), INT(J6/10)*10+RIGHT(F6*100,3)/100), CHOOSE(MATCH(ROUND($J6,2),{0,50.01,100.01,500.01}, 1), CEILING($J6+0.01,1)-0.01, ROUND($J6/5,0)*5-0.01, CEILING($J6+0.01,10)-0.01, CHOOSE(VLOOKUP($J6,Tbl_Category,2,TRUE), MAX(INT($J6-0.49),0.99), MAX(INT(($J6-2.49)/5)*5,4.99), MAX(INT(($J6-4.99)/10)*10,9.99), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE), CEILING($J6+0.01,50)-0.01)))) ----- original posting ----- "Lisa" wrote in message ... I thought you wanted to know what information was in the range names that is why i posted that. Any way in trying your new formula rule 3 and 4 are working correclty but 1 is not. I am getting a value# error for anything in cell J10 10.00 or greater. I am also have an issue with if cell J10 has 1.01 through 1.05 the answer = 0. Let me give you a synopsis of rule 1 and hopefully you can help me on this. below is the original rule 1 formula. =IF($H13=1,IF($J13<1,$J13,IF(AND($J13=1,$J13<=1.0 5),0.99,IF(AND($J13=1.06,$J13<=1.09),1.09,IF(AND( $J13=1.1,$J13<=9.99),+IF(RIGHT((ROUNDUP($J13,2))* 100,1)="0",ROUNDUP($J13,1)+0.09,IF(RIGHT($J13,2)*1 00<=9,19,(ROUNDUP($J13*100,-1)-1)/100)),IF($J13=10,CEILING($J13+0.01,0.5)-0.01,$F13)))))) Rules for 1: if cell J13 is <1.00 no adjustment if cell J13 is <1.05 adjust to .99 if cell J13 is between 1.06 and 1.09 (inclusive) adjust to 1.09 if cell J13 is between 1.10 and 9.99 (inclusive) and cents portion is .09 or less, adjust cents to .19 if cell J13 is between 1.10 and 9.99 (inclusive) and cents portion is .10 or more, adjust cents up to the next .x9 cent ending if cell J13 is 10.00 or higher and cents portion is .49 or less, adjust cents to .49 ending if cell J13 is 10.00 or higher and cents portion is .50 or more, adjust cents to .99 ending Thanks -- lmo "JoeU2004" wrote: I am not sure what purpose your follow-up posting intends to serve. But just to be clear: I am not trying to improve your original formulas in any substantial way. I am simply suggesting a mechanical way to stitch them together without incurring the limit of 7 nested function calls (8 including the outermost). The previous posting of my suggestion might have been too abstract and difficult to understand. The following is my attempt to implement my suggestion. It is formatted in a manner to try to make the structure clear. My apologies if it is mangled by the news server or news readers. Please read the notes following my implementation. =IF(AND(H6<{"1","3","4"}), $F6, CHOOSE(H6, CHOOSE(MATCH(ROUND($J6,2),{0,1.01,1.06,1.1,10},1), $J6, 0.99, 1.09, IF(RIGHT(ROUNDUP($J6,2)*100,1)="0", ROUNDUP($J6,1)+0.09, IF(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100))), 0, IF(J6<200, INT(J6) +IF(RIGHT(F6*100,2)="00", 1, RIGHT(F6*100,2)/100), INT(J6/10)*10+RIGHT(F6*100,3)/100), CHOOSE(MATCH(ROUND($J6,2),{0,50.01,100.01,500.01}, 1), CEILING($J6+0.01,1)-0.01, ROUND($J6/5,0)*5-0.01, CEILING($J6+0.01,10)-0.01, CHOOSE(VLOOKUP($J6,Tbl_Category,2,TRUE), MAX(INT($J6-0.49)+.99), MAX(INT(($J6-2.49)/5)*5+4.99), MAX(INT(($J6-4.99)/10)*10+9.99), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE), CEILING($J6+0.01,50)-0.01)))) Notes: For my trouble, please comment on the following notes, starting with #4. 1. Although I made an effort to replicate the logic of your formulas correctly, I did not test this implementation beyond copy-and-pasting it into Excel to ensure that it is syntactically correct. 2. Unfortunately, we cannot copy-and-paste the text above directly into Excel; Excel complains that the formula is too long. I copy-and-pasted the text into Notepad, eliminated the leading spaces in every line, then copy-and-pasted from Notepad into Excel successfully. 3. The maximum nested function depth is 6 (including the outermost), if I counted correctly. That gives you some wiggle room, since the maximum supported depth in Excel 2003 is 8 (including the outermost). 4. I left the MAX(INT) expressions in the last CHOOSE argument list as they were in the original formula. However, I think the use of MAX there is superfluous. MAX only makes sense with two or more arguments; so I was tempted to replace "+" with a comma. But looking at the other related expressions, I suspect the intent is to add a factor to the INT expression in order to ensure that the result ends in ".99". Is that right? In that case, you can remove the use of MAX. 5. I incorporated the "correction" that I noted in a previous posting, interpreting IF(AND(...)+IF(...),...) in the original formula as IF(AND(...),IF(...),...). Is that right? If not, the 2nd CHOOSE argument list needs to be changed significantly. Is this helpful? ----- original message ----- "Lisa" wrote in message ... The 3 range names are as follows. Tbl_Category coulmnA columnB 500.01 4 1000.01 5 2000.01 6 Tbl_Category4 (represents 500.01 to 1000.00 = 19.99 39.99 49.99 69.99 89.99 99.99) columnD columnE 0 19.99 20.00 39.99 40.00 49.99 50.00 69.99 70.00 89.99 90.00 99.99 Tbl_Category5 (represents 1000.01 to 2000.00 = 29.99 49.99 69.99 99.99) columnD columnF 0 29.99 30.00 49.99 50.00 69.99 70.00 99.99 -- lmo "JoeU2004" wrote: Improvement.... (Sorry for the incessant postings.) "JoeU2004" wrote: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) Better: choose(match(round($J6,2),{0,50.01,100.01,500.01}, 1), ...1..., ...2..., ....3..., ...4...) where ...1... is the expression associated with your condition $J6<=50, ....2... is the expression for AND($J6=50.10,$J6<=100), ...3... is the expression for AND($J6100.01,$J6<=500) [sic], and ...4.. is the expression for $J6=500.01 (CHOOSE). 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Better: choose(match(round($J6,2),{0,1.01,1.06,1.10,10},1) , ...1..., ...2..., ....3..., ...4..., ...5...) where ...1... is the expression for $J6<=1, ...2... is the expression for AND($J61,$J6<=1.05), ...3... is the expression for AND($J6=1.06,$J6<=1.09), ...4... is the expression for AND($J6=1.1,$J6<=9.99), and ...5... is the expression for $J6=10 (CEILING). ----- original message ----- "JoeU2004" wrote in message ... "Lisa" wrote: For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
"Lisa" wrote:
The final formula works great, thanks so much for your help. You're welcome. But I hope you caught this potential mistake of mine. I wrote: MAX(INT($J6-0.49),0.99), MAX(INT(($J6-2.49)/5)*5,4.99), MAX(INT(($J6-4.99)/10)*10,9.99), You're original formula had: MAX(INT($J6-0.49)+0.99), MAX(INT(($J6-2.49)/5)*5+4.99), MAX(INT(($J6-4.99)/10)*10+9.99), I raised questions about that, which you never addresses :-(. But I had come to the conclusion that you probably intended it the way you wrote it, and the use of MAX was merely superfluous. Apparently, I had made the correction in an earlier posting, but not in the template that I relied for my subsequent posting. ----- original message ----- "Lisa" wrote in message ... Joe, The final formula works great, thanks so much for your help. -- lmo "JoeU2004" wrote: "Lisa" wrote: I am getting a value# error for anything in cell J10 10.00 or greater. My mistake: I had omitted the argument for CHOOSE index 5 corresponding to IF($J6=10,CEILING($J6+0.01,0.5)-0.01) in your original posting. See the complete updated formula below. I am also have an issue with if cell J10 has 1.01 through 1.05 the answer = 0 I cannot help with you that without seeing exactly (by copy-and-paste) what you entered. In my formulation, a value in that range should result in a CHOOSE index of 2; and the expression corresponding to that index is the constant 0.99. Ostensibly, there is no way that can result in 0. Perhaps a formatting error. Perhaps a typo when you entered the formula. My complete updated prototype (based on your original posting): =IF(AND(H6<{"1","3","4"}), $F6, CHOOSE(H6, CHOOSE(MATCH(ROUND($J6,2),{0,1.01,1.06,1.1,10},1), $J6, 0.99, 1.09, IF(RIGHT(ROUNDUP($J6,2)*100,1)="0", ROUNDUP($J6,1)+0.09, IF(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)), CEILING($J6+0.01,0.5)-0.01)), 0, IF(J6<200, INT(J6) +IF(RIGHT(F6*100,2)="00", 1, RIGHT(F6*100,2)/100), INT(J6/10)*10+RIGHT(F6*100,3)/100), CHOOSE(MATCH(ROUND($J6,2),{0,50.01,100.01,500.01}, 1), CEILING($J6+0.01,1)-0.01, ROUND($J6/5,0)*5-0.01, CEILING($J6+0.01,10)-0.01, CHOOSE(VLOOKUP($J6,Tbl_Category,2,TRUE), MAX(INT($J6-0.49),0.99), MAX(INT(($J6-2.49)/5)*5,4.99), MAX(INT(($J6-4.99)/10)*10,9.99), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE), CEILING($J6+0.01,50)-0.01)))) ----- original posting ----- "Lisa" wrote in message ... I thought you wanted to know what information was in the range names that is why i posted that. Any way in trying your new formula rule 3 and 4 are working correclty but 1 is not. I am getting a value# error for anything in cell J10 10.00 or greater. I am also have an issue with if cell J10 has 1.01 through 1.05 the answer = 0. Let me give you a synopsis of rule 1 and hopefully you can help me on this. below is the original rule 1 formula. =IF($H13=1,IF($J13<1,$J13,IF(AND($J13=1,$J13<=1.0 5),0.99,IF(AND($J13=1.06,$J13<=1.09),1.09,IF(AND( $J13=1.1,$J13<=9.99),+IF(RIGHT((ROUNDUP($J13,2))* 100,1)="0",ROUNDUP($J13,1)+0.09,IF(RIGHT($J13,2)*1 00<=9,19,(ROUNDUP($J13*100,-1)-1)/100)),IF($J13=10,CEILING($J13+0.01,0.5)-0.01,$F13)))))) Rules for 1: if cell J13 is <1.00 no adjustment if cell J13 is <1.05 adjust to .99 if cell J13 is between 1.06 and 1.09 (inclusive) adjust to 1.09 if cell J13 is between 1.10 and 9.99 (inclusive) and cents portion is .09 or less, adjust cents to .19 if cell J13 is between 1.10 and 9.99 (inclusive) and cents portion is .10 or more, adjust cents up to the next .x9 cent ending if cell J13 is 10.00 or higher and cents portion is .49 or less, adjust cents to .49 ending if cell J13 is 10.00 or higher and cents portion is .50 or more, adjust cents to .99 ending Thanks -- lmo "JoeU2004" wrote: I am not sure what purpose your follow-up posting intends to serve. But just to be clear: I am not trying to improve your original formulas in any substantial way. I am simply suggesting a mechanical way to stitch them together without incurring the limit of 7 nested function calls (8 including the outermost). The previous posting of my suggestion might have been too abstract and difficult to understand. The following is my attempt to implement my suggestion. It is formatted in a manner to try to make the structure clear. My apologies if it is mangled by the news server or news readers. Please read the notes following my implementation. =IF(AND(H6<{"1","3","4"}), $F6, CHOOSE(H6, CHOOSE(MATCH(ROUND($J6,2),{0,1.01,1.06,1.1,10},1), $J6, 0.99, 1.09, IF(RIGHT(ROUNDUP($J6,2)*100,1)="0", ROUNDUP($J6,1)+0.09, IF(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100))), 0, IF(J6<200, INT(J6) +IF(RIGHT(F6*100,2)="00", 1, RIGHT(F6*100,2)/100), INT(J6/10)*10+RIGHT(F6*100,3)/100), CHOOSE(MATCH(ROUND($J6,2),{0,50.01,100.01,500.01}, 1), CEILING($J6+0.01,1)-0.01, ROUND($J6/5,0)*5-0.01, CEILING($J6+0.01,10)-0.01, CHOOSE(VLOOKUP($J6,Tbl_Category,2,TRUE), MAX(INT($J6-0.49)+.99), MAX(INT(($J6-2.49)/5)*5+4.99), MAX(INT(($J6-4.99)/10)*10+9.99), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE), CEILING($J6+0.01,50)-0.01)))) Notes: For my trouble, please comment on the following notes, starting with #4. 1. Although I made an effort to replicate the logic of your formulas correctly, I did not test this implementation beyond copy-and-pasting it into Excel to ensure that it is syntactically correct. 2. Unfortunately, we cannot copy-and-paste the text above directly into Excel; Excel complains that the formula is too long. I copy-and-pasted the text into Notepad, eliminated the leading spaces in every line, then copy-and-pasted from Notepad into Excel successfully. 3. The maximum nested function depth is 6 (including the outermost), if I counted correctly. That gives you some wiggle room, since the maximum supported depth in Excel 2003 is 8 (including the outermost). 4. I left the MAX(INT) expressions in the last CHOOSE argument list as they were in the original formula. However, I think the use of MAX there is superfluous. MAX only makes sense with two or more arguments; so I was tempted to replace "+" with a comma. But looking at the other related expressions, I suspect the intent is to add a factor to the INT expression in order to ensure that the result ends in ".99". Is that right? In that case, you can remove the use of MAX. 5. I incorporated the "correction" that I noted in a previous posting, interpreting IF(AND(...)+IF(...),...) in the original formula as IF(AND(...),IF(...),...). Is that right? If not, the 2nd CHOOSE argument list needs to be changed significantly. Is this helpful? ----- original message ----- "Lisa" wrote in message ... The 3 range names are as follows. Tbl_Category coulmnA columnB 500.01 4 1000.01 5 2000.01 6 Tbl_Category4 (represents 500.01 to 1000.00 = 19.99 39.99 49.99 69.99 89.99 99.99) columnD columnE 0 19.99 20.00 39.99 40.00 49.99 50.00 69.99 70.00 89.99 90.00 99.99 Tbl_Category5 (represents 1000.01 to 2000.00 = 29.99 49.99 69.99 99.99) columnD columnF 0 29.99 30.00 49.99 50.00 69.99 70.00 99.99 -- lmo "JoeU2004" wrote: Improvement.... (Sorry for the incessant postings.) "JoeU2004" wrote: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) to: IF($J6<=50, ..., IF($J6<=100, ..., IF($J6<=500, ..., CHOOSE(...)))) Better: choose(match(round($J6,2),{0,50.01,100.01,500.01}, 1), ...1..., ...2..., ....3..., ...4...) where ...1... is the expression associated with your condition $J6<=50, ....2... is the expression for AND($J6=50.10,$J6<=100), ...3... is the expression for AND($J6100.01,$J6<=500) [sic], and ...4.. is the expression for $J6=500.01 (CHOOSE). 2. Change: IF($J6<=1, ..., IF(AND($J61,$J6<=1.05), ..., IF(AND($J6=1.06,$J6<=1.09), ..., IF(AND($J6=1.1,$J6<=9.99), ..., IF($J6=10, CEILING($J6+0.01,0.5)-0.01))))) to: IF($J6<=1, ..., IF($J6<1.06, ..., IF($J6<1.1, ..., IF($J6<10, ..., CEILING(...)-0.01)))) Better: choose(match(round($J6,2),{0,1.01,1.06,1.10,10},1) , ...1..., ...2..., ....3..., ...4..., ...5...) where ...1... is the expression for $J6<=1, ...2... is the expression for AND($J61,$J6<=1.05), ...3... is the expression for AND($J6=1.06,$J6<=1.09), ...4... is the expression for AND($J6=1.1,$J6<=9.99), and ...5... is the expression for $J6=10 (CEILING). ----- original message ----- "JoeU2004" wrote in message ... "Lisa" wrote: For my new formula i need the following: If the contents of cell f6 = 1, 4 or 5 I need a specfic percentage added to that cell and a specific |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 2 formulas
Yes i correct that. My final formula is below.
'=IF(AND(H6<{1,3,4}),$F6,CHOOSE(H6,CHOOSE(MATCH(R OUND($J6,2),{0,1,1.06,1.1,10},1),$J6,0.99,1.09,IF( RIGHT(ROUNDUP($J6,2)*100,1)="0",ROUNDUP($J6,1)+0.0 9,IF(RIGHT($J6,2)*100<=9,19,(ROUNDUP($J6*100,-1)-1)/100)),CEILING($J6+0.01,0.5)-0.01),0,IF(J6<200,INT(J6)+IF(RIGHT(F6*100,2)="00", 1,RIGHT(F6*100,2)/100),INT(J6/10)*10+RIGHT(F6*100,3)/100),CHOOSE(MATCH(ROUND($J6,2),{0,50.01,100.01,500 .01},1),CEILING($J6+0.01,1)-0.01,ROUND($J6/5,0)*5-0.01,CEILING($J6+0.01,10)-0.01,CHOOSE(VLOOKUP($J6,Tbl_Category,2,TRUE),MAX(I NT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01)))) Thanks again. -- lmo "JoeU2004" wrote: "Lisa" wrote: The final formula works great, thanks so much for your help. You're welcome. But I hope you caught this potential mistake of mine. I wrote: MAX(INT($J6-0.49),0.99), MAX(INT(($J6-2.49)/5)*5,4.99), MAX(INT(($J6-4.99)/10)*10,9.99), You're original formula had: MAX(INT($J6-0.49)+0.99), MAX(INT(($J6-2.49)/5)*5+4.99), MAX(INT(($J6-4.99)/10)*10+9.99), I raised questions about that, which you never addresses :-(. But I had come to the conclusion that you probably intended it the way you wrote it, and the use of MAX was merely superfluous. Apparently, I had made the correction in an earlier posting, but not in the template that I relied for my subsequent posting. ----- original message ----- "Lisa" wrote in message ... Joe, The final formula works great, thanks so much for your help. -- lmo "JoeU2004" wrote: "Lisa" wrote: I am getting a value# error for anything in cell J10 10.00 or greater. My mistake: I had omitted the argument for CHOOSE index 5 corresponding to IF($J6=10,CEILING($J6+0.01,0.5)-0.01) in your original posting. See the complete updated formula below. I am also have an issue with if cell J10 has 1.01 through 1.05 the answer = 0 I cannot help with you that without seeing exactly (by copy-and-paste) what you entered. In my formulation, a value in that range should result in a CHOOSE index of 2; and the expression corresponding to that index is the constant 0.99. Ostensibly, there is no way that can result in 0. Perhaps a formatting error. Perhaps a typo when you entered the formula. My complete updated prototype (based on your original posting): =IF(AND(H6<{"1","3","4"}), $F6, CHOOSE(H6, CHOOSE(MATCH(ROUND($J6,2),{0,1.01,1.06,1.1,10},1), $J6, 0.99, 1.09, IF(RIGHT(ROUNDUP($J6,2)*100,1)="0", ROUNDUP($J6,1)+0.09, IF(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100)), CEILING($J6+0.01,0.5)-0.01)), 0, IF(J6<200, INT(J6) +IF(RIGHT(F6*100,2)="00", 1, RIGHT(F6*100,2)/100), INT(J6/10)*10+RIGHT(F6*100,3)/100), CHOOSE(MATCH(ROUND($J6,2),{0,50.01,100.01,500.01}, 1), CEILING($J6+0.01,1)-0.01, ROUND($J6/5,0)*5-0.01, CEILING($J6+0.01,10)-0.01, CHOOSE(VLOOKUP($J6,Tbl_Category,2,TRUE), MAX(INT($J6-0.49),0.99), MAX(INT(($J6-2.49)/5)*5,4.99), MAX(INT(($J6-4.99)/10)*10,9.99), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE), CEILING($J6+0.01,50)-0.01)))) ----- original posting ----- "Lisa" wrote in message ... I thought you wanted to know what information was in the range names that is why i posted that. Any way in trying your new formula rule 3 and 4 are working correclty but 1 is not. I am getting a value# error for anything in cell J10 10.00 or greater. I am also have an issue with if cell J10 has 1.01 through 1.05 the answer = 0. Let me give you a synopsis of rule 1 and hopefully you can help me on this. below is the original rule 1 formula. =IF($H13=1,IF($J13<1,$J13,IF(AND($J13=1,$J13<=1.0 5),0.99,IF(AND($J13=1.06,$J13<=1.09),1.09,IF(AND( $J13=1.1,$J13<=9.99),+IF(RIGHT((ROUNDUP($J13,2))* 100,1)="0",ROUNDUP($J13,1)+0.09,IF(RIGHT($J13,2)*1 00<=9,19,(ROUNDUP($J13*100,-1)-1)/100)),IF($J13=10,CEILING($J13+0.01,0.5)-0.01,$F13)))))) Rules for 1: if cell J13 is <1.00 no adjustment if cell J13 is <1.05 adjust to .99 if cell J13 is between 1.06 and 1.09 (inclusive) adjust to 1.09 if cell J13 is between 1.10 and 9.99 (inclusive) and cents portion is .09 or less, adjust cents to .19 if cell J13 is between 1.10 and 9.99 (inclusive) and cents portion is .10 or more, adjust cents up to the next .x9 cent ending if cell J13 is 10.00 or higher and cents portion is .49 or less, adjust cents to .49 ending if cell J13 is 10.00 or higher and cents portion is .50 or more, adjust cents to .99 ending Thanks -- lmo "JoeU2004" wrote: I am not sure what purpose your follow-up posting intends to serve. But just to be clear: I am not trying to improve your original formulas in any substantial way. I am simply suggesting a mechanical way to stitch them together without incurring the limit of 7 nested function calls (8 including the outermost). The previous posting of my suggestion might have been too abstract and difficult to understand. The following is my attempt to implement my suggestion. It is formatted in a manner to try to make the structure clear. My apologies if it is mangled by the news server or news readers. Please read the notes following my implementation. =IF(AND(H6<{"1","3","4"}), $F6, CHOOSE(H6, CHOOSE(MATCH(ROUND($J6,2),{0,1.01,1.06,1.1,10},1), $J6, 0.99, 1.09, IF(RIGHT(ROUNDUP($J6,2)*100,1)="0", ROUNDUP($J6,1)+0.09, IF(RIGHT($J6,2)*100<=9, 19, (ROUNDUP($J6*100,-1)-1)/100))), 0, IF(J6<200, INT(J6) +IF(RIGHT(F6*100,2)="00", 1, RIGHT(F6*100,2)/100), INT(J6/10)*10+RIGHT(F6*100,3)/100), CHOOSE(MATCH(ROUND($J6,2),{0,50.01,100.01,500.01}, 1), CEILING($J6+0.01,1)-0.01, ROUND($J6/5,0)*5-0.01, CEILING($J6+0.01,10)-0.01, CHOOSE(VLOOKUP($J6,Tbl_Category,2,TRUE), MAX(INT($J6-0.49)+.99), MAX(INT(($J6-2.49)/5)*5+4.99), MAX(INT(($J6-4.99)/10)*10+9.99), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE), INT($J6/100)*100 +VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE), CEILING($J6+0.01,50)-0.01)))) Notes: For my trouble, please comment on the following notes, starting with #4. 1. Although I made an effort to replicate the logic of your formulas correctly, I did not test this implementation beyond copy-and-pasting it into Excel to ensure that it is syntactically correct. 2. Unfortunately, we cannot copy-and-paste the text above directly into Excel; Excel complains that the formula is too long. I copy-and-pasted the text into Notepad, eliminated the leading spaces in every line, then copy-and-pasted from Notepad into Excel successfully. 3. The maximum nested function depth is 6 (including the outermost), if I counted correctly. That gives you some wiggle room, since the maximum supported depth in Excel 2003 is 8 (including the outermost). 4. I left the MAX(INT) expressions in the last CHOOSE argument list as they were in the original formula. However, I think the use of MAX there is superfluous. MAX only makes sense with two or more arguments; so I was tempted to replace "+" with a comma. But looking at the other related expressions, I suspect the intent is to add a factor to the INT expression in order to ensure that the result ends in ".99". Is that right? In that case, you can remove the use of MAX. 5. I incorporated the "correction" that I noted in a previous posting, interpreting IF(AND(...)+IF(...),...) in the original formula as IF(AND(...),IF(...),...). Is that right? If not, the 2nd CHOOSE argument list needs to be changed significantly. Is this helpful? ----- original message ----- "Lisa" wrote in message ... The 3 range names are as follows. Tbl_Category coulmnA columnB 500.01 4 1000.01 5 2000.01 6 Tbl_Category4 (represents 500.01 to 1000.00 = 19.99 39.99 49.99 69.99 89.99 99.99) columnD columnE 0 19.99 20.00 39.99 40.00 49.99 50.00 69.99 70.00 89.99 90.00 99.99 Tbl_Category5 (represents 1000.01 to 2000.00 = 29.99 49.99 69.99 99.99) columnD columnF 0 29.99 30.00 49.99 50.00 69.99 70.00 99.99 -- lmo "JoeU2004" wrote: Improvement.... (Sorry for the incessant postings.) "JoeU2004" wrote: 1. Change: IF($J6<=50, ..., IF(AND($J6=50.01,$J6<=100), ..., IF(AND($J6100.01,$J6<=500), ..., IF($J6=500.01, CHOOSE(...), "")))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I combine 2 formulas? | New Users to Excel | |||
Combine 2 formulas | Excel Worksheet Functions | |||
how to combine formulas to......... | Excel Worksheet Functions | |||
Combine 2 formulas | Excel Worksheet Functions | |||
Help combine 2 formulas into 1 | Excel Worksheet Functions |