Formula?

[Malc]

Is there a formula to allow me to calculate a deduction
from a sum up to a certain limit then change the deduction
to another amount once this limit has been reached?

Example would be
To deduct 5% of sum up to a max of £170
then 4% thereafter

Thanks

 

[Lady Layla]

IF(sum(range)>170,total*.05,total*.04)
Is there a formula to allow me to calculate a deduction
from a sum up to a certain limit then change the deduction
to another amount once this limit has been reached?

Example would be
To deduct 5% of sum up to a max of £170
then 4% thereafter

Thanks

 

[Lady Layla]

oops make the > a < for less than


: IF(sum(range)>170,total*.05,total*.04)
: : Is there a formula to allow me to calculate a deduction
: from a sum up to a certain limit then change the deduction
: to another amount once this limit has been reached?
:
: Example would be
: To deduct 5% of sum up to a max of £170
: then 4% thereafter
:
: Thanks
:
:

 

[Sandy Mann]

I read it slightly differently. Borrowing from one of Harlan's posts:

=MIN(N(SUM(A1:A10)),170)*0.95+(SUM(A1:A10)>=170)*(SUM(A1:A10)-170)*0.96

Regards

Sandy

 

[Sandy Mann]

No I was wrong - I don't have to borrow from Harlan at all. I worked out
the formula using a single cell holding the result of the sum instead of the
SUM function itself. This brought up a wrong result if the SUM result cell
was empty so I used the N function to take care of that.

I then noticed that Lady Layla used the SUM function and re-read the
question and decided to put the SUM function in the formula:

=MIN(SUM(A1:A10),170)*0.95+(SUM(A1:A10)>=170)*(SUM(A1:A10)-170)*0.96

 

[CLR]

=IF(A1*0.05>170,A1-(A1*0.04),A1-(A1*0.05))

Vaya con Dios,
Chuck, CABGx3



Is there a formula to allow me to calculate a deduction
from a sum up to a certain limit then change the deduction
to another amount once this limit has been reached?

Example would be
To deduct 5% of sum up to a max of £170
then 4% thereafter

Thanks