M Thompson said:
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 -----
