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

"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

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

"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

"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

"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

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

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

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