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

Are you saying that the total of:

-14.42 + -7.93 + -1.91

Should be -20.10

If so, you'll have to explain why you think that should be the result.

The formulas are returning the correct results. Maybe they're not doing what
you think they're doing.

--
Biff
Microsoft Excel MVP


"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

Not sure what the problem is... this might help
=-MAX(0,MIN(I9,100)*0.05)
The above will give you 0 if I9<0, minus of 5% of I9 if it is between 0 and
100 and -5 if it is above 100
=-MAX(0,(I9-100)*0.02)
The above will give you 0 if I9<100, minus of 2% of I9-100 if it is above 100

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"M Thompson" wrote:

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

Sorry-didn't explain properly. If I do a total of the data I've input
(I9-I11), the result is 855.29. The result of the calculation on that is
20.10, which is correct and what I need to see. If I don't do a total of the
input data, but do a sum of the calculated answers for each individual piece
it comes to 24.25. Why so much difference?

--
OneFineDay


"T. Valko" wrote:

Are you saying that the total of:

-14.42 + -7.93 + -1.91

Should be -20.10

If so, you'll have to explain why you think that should be the result.

The formulas are returning the correct results. Maybe they're not doing what
you think they're doing.

--
Biff
Microsoft Excel MVP


"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

Hello and thanks for responding so quick. The explanation was very helpful,
and the formula does what I thought. Please see my response to T. Valko's
question. I hope it sheds better light on my problem.
--
OneFineDay


"Sheeloo" wrote:

Not sure what the problem is... this might help
=-MAX(0,MIN(I9,100)*0.05)
The above will give you 0 if I9<0, minus of 5% of I9 if it is between 0 and
100 and -5 if it is above 100
=-MAX(0,(I9-100)*0.02)
The above will give you 0 if I9<100, minus of 2% of I9-100 if it is above 100

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"M Thompson" wrote:

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

If you apply
=-MAX(0,MIN(I9,100)*0.05)
to 90 you will get -4.5, right?
so if have 90 in two cells you will get -9 as total...

if you apply it to 180 (sum of 90 and 90) you will get -5

Hope this helps...

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"M Thompson" wrote:

Hello and thanks for responding so quick. The explanation was very helpful,
and the formula does what I thought. Please see my response to T. Valko's
question. I hope it sheds better light on my problem.
--
OneFineDay


"Sheeloo" wrote:

Not sure what the problem is... this might help
=-MAX(0,MIN(I9,100)*0.05)
The above will give you 0 if I9<0, minus of 5% of I9 if it is between 0 and
100 and -5 if it is above 100
=-MAX(0,(I9-100)*0.02)
The above will give you 0 if I9<100, minus of 2% of I9-100 if it is above 100

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"M Thompson" wrote:

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

Because you don't necessarily use those numbers in the calculation, you take
the MAX/Min of I9 and 100 so it is changing, so your overall result should
only be based the results of the formal on the sum of all source data, not
the sum of the individual results.

--
__________________________________
HTH

Bob

"M Thompson" wrote in message
...
Sorry-didn't explain properly. If I do a total of the data I've input
(I9-I11), the result is 855.29. The result of the calculation on that is
20.10, which is correct and what I need to see. If I don't do a total of
the
input data, but do a sum of the calculated answers for each individual
piece
it comes to 24.25. Why so much difference?

--
OneFineDay


"T. Valko" wrote:

Are you saying that the total of:

-14.42 + -7.93 + -1.91

Should be -20.10

If so, you'll have to explain why you think that should be the result.

The formulas are returning the correct results. Maybe they're not doing
what
you think they're doing.

--
Biff
Microsoft Excel MVP


"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

"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

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

You're right, of course, it would necessarily have 2 different results. For
some reason it just took me awhile to realize it. Please see my answer to
Joe.

And thanks
--
OneFineDay


"Sheeloo" wrote:


If you apply
=-MAX(0,MIN(I9,100)*0.05)
to 90 you will get -4.5, right?
so if have 90 in two cells you will get -9 as total...

if you apply it to 180 (sum of 90 and 90) you will get -5

Hope this helps...

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"M Thompson" wrote:

Hello and thanks for responding so quick. The explanation was very helpful,
and the formula does what I thought. Please see my response to T. Valko's
question. I hope it sheds better light on my problem.
--
OneFineDay


"Sheeloo" wrote:

Not sure what the problem is... this might help
=-MAX(0,MIN(I9,100)*0.05)
The above will give you 0 if I9<0, minus of 5% of I9 if it is between 0 and
100 and -5 if it is above 100
=-MAX(0,(I9-100)*0.02)
The above will give you 0 if I9<100, minus of 2% of I9-100 if it is above 100

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"M Thompson" wrote:

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