Joe-
You gave me much to consider and what you said made me realize that there
was no way I could reconcile the two. Figuring each one separately takes 5%
(or 5) off of each line and 2% of the rest of each line, whereas doing the
calculation on the whole 855.29 only takes 5% one time and 2% off the rest.
Since it is a quarterly calculation, the latter needs to be the solution.
Sorry it took up so much of everyone's time and effort for me to realize it.
Thus a classic case of "the nut behind the wheel", so to speak!

Thanks
--
OneFineDay


"JoeU2004" wrote:

"M Thompson" wrote:
It seems to me to be too big a difference to be a rounding thing

Yes. But as an aside, I think you will want to add some judicious rounding
if you want the __displayed__ values to sum to expectations. Remember:
although Excel will round according to the specified format, the underlying
values might have greater precision, unless you set the calculation option
"Precision as displayed". Did you?

Anyway, returning to your question....


Can someone explain what's happening

This is one of those mathematical problems like "the average the sum is not
necessarily the same as the sum of the averages". In this case, we are
talking about tiered percentage amounts, not averages. Basically, for each
line item, your "results" column (J) is ostensibly 5% of the first 100 plus
2% of the excess above 100 of the corresponding value in column I. But you
seem to want to limit the sum of the tiered percentage amounts to the tiered
percentage amount of the sum. Your formula does not guarantee that.

Consider this simple example: I9 = 1000, and I10 = 1000; then J9 = -23, and
J10 = -23 (100*5% + 900*2%), and they sum to -46. But if I11 is the total,
2000, then J11 is -43 (100*5% + 1900*2%). Do you see the problem? In the
last first sum, you have 100*5% + 100*5% = 200*5%. But in J11, you have
only 100*5%. (With concomitant differences in the 2% term, too.)


is there a way to fix it so that the results are reasonably in line.

The answer is "yes". But the specific solution depends on your definition
of "reasonable". There is no a priori requirement that is "most
reasonable". It depends on your application. If you need help in deciding
which of the following is the right "reasonable" requirement, we will need
to know your application. In other words, what do the numbers in column I
represent, and what do the tier percentage amounts in column J represent?

The first "reasonable" solution is: do not expect the sum of the tiered
percentage amounts to equal the tiered percentage amount of the sum. In
other words, the only error is your expectation in the first place.

A second "resonable" requirement is: the cumulative sum of tiered
percentage amounts should not exceed the tiered percentage amount of the
cumulative sum. The formula might be:

=-MAX(0,MIN(SUM($I$9:I9),100)*0.05)-MAX(0,(SUM($I$9:I9)-100)*0.02)-SUM($J$8:J8)

Note: This assumes that J8 is blank or text.

A third "reasonable" requirement is: the tiered percentage amount should
not exceed the remainder of the tiered percentage amount of the total less
the cumulative sum of the tiered percentage amounts.

=MAX(-MAX(0,MIN(I9,100)*0.05)-MAX(0,(I9-100)*0.02),$J$12-SUM($J$8:J8))

Note: This additionally assumes that the tiered percentage amount of the
total is in J12.

There may be other alternative "reasonable" requirements.

Some additional notes:

1. You need MAX(0,MIN(I9,100)*0.05) only if I9 might be negative.
Otherwise, MIN(I9,100)*0.05 should suffice.

2. I changed the needless +-MAX(...) in the second term of the expression to
simply -MAX(...). The "+" is superfluous.

3. Dealing with negative numbers can be confusing. The outermost MAX in
third possible solution actually selects the smaller __magnitude__ of the
numbers. For example, -2 is less than -1, but -1 is the smaller magnitude.

HTH.


----- original message -----

"M Thompson" wrote in message
...
I'm hoping someone somewhere can tell me what's happening and how to fix
it.
This is the formula I'm using:
=-MAX(0,MIN(I9,100)*0.05)+-MAX(0,(I9-100)*0.02) and copy down through I11

Starting in I9 I have the following amounts.
I Results i'm getting
570.75 (14.42)
246.36 (7.93)
38.18 (1.91)

If I do a Total cell and use the same calculation on it, the answer will
be
20.10, which is what I expect. The results of 9-11 are obviously going to
total to more than 20.10. Can someone explain what's happening and is
there
a way to fix it so that the results are reasonably in line. It seems to
me
to be too big a difference to be a rounding thing??

Thanks
--
OneFineDay