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....