"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