complex formula

[RENEE]

Could someone please!!! help me to set up a complex formula which I will use
many times over.

I have an amount - say $7,417. I need to take 66.7% of the first $2,500
(which comes to $1,667.50). This leaves me with $4,917 of the original
amount and I need to take 50% of the next $3,500 ($1,750) - leaving me with
$1,417 of the original number which I need to take 40% of ($566.80) Then, I
add all the percentages up to come to $3,984.30. I have to do this a lot and
could certainly use an easier way other than a calculator and a large sheet
of paper. Thanks.

 

[Rick Rothstein]

Assuming your $7,417 value is in A1, I think this formula does what you
want...

=0.667*MIN(A1,2500)+0.5*MIN(MIN(A1-2500,3500),6000)+0.4*MAX(A1-6000,0)

 

[Joe User]

You could take a look at www.mcgimpsey.com/excel/variablerate.html. In some
ways, it is easier to maintain (change as needed), although I prefer to use
VLOOKUP.

Alternatively, try:

=MIN(A1*66.7%, (A1-2500)*50%+1667.5,
(A1-6000)*40%+3417.5)

For dollars-and-cents results, it would be prudent to round that, viz.:

=ROUND(MIN(A1*66.7%, (A1-2500)*50%+1667.5,
(A1-6000)*40%+3417.5), 2)

The number 6000 is the amount corresponding to "the next 3500"; that is,
2500+3500.

The numbers 1667.5 and 3417.5 are the max amounts (tax?) corresponding to
the previous bracket. You can bootstrap these amounts as follows. First,
compute MIN(A1*66.7%) with A1=2500 (1667.5). Then compute
MIN(A1*66.7%,(A1-2500)*50%+1667.5) with A1=6000 (3417.5). Etc.


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

 

[RENEE]

Thanks, a little complex, but great.

You could take a look at www.mcgimpsey.com/excel/variablerate.html. In some
ways, it is easier to maintain (change as needed), although I prefer to use
VLOOKUP.

Alternatively, try:

=MIN(A1*66.7%, (A1-2500)*50%+1667.5,
(A1-6000)*40%+3417.5)

For dollars-and-cents results, it would be prudent to round that, viz.:

=ROUND(MIN(A1*66.7%, (A1-2500)*50%+1667.5,
(A1-6000)*40%+3417.5), 2)

The number 6000 is the amount corresponding to "the next 3500"; that is,
2500+3500.

The numbers 1667.5 and 3417.5 are the max amounts (tax?) corresponding to
the previous bracket. You can bootstrap these amounts as follows. First,
compute MIN(A1*66.7%) with A1=2500 (1667.5). Then compute
MIN(A1*66.7%,(A1-2500)*50%+1667.5) with A1=6000 (3417.5). Etc.


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

 

[RENEE]

Tried that one out and it works perfectly. Thanks so much!!!!

 

[Prof Wonmug]

Tried that one out and it works perfectly. Thanks so much!!!!

Personally, I would not do this with an inline formula. I would write
a little UDF. Then, instead of a complicated formula that mau not make
any sense to you in 6 months, each cell would have something like:

=VarPC(A1)

You can put as much commentary as you like in the UDF and if you ever
decide to change any of the parameters, you just change one function
and all of the cells are updated. You can also add other arguments if
you need any variability.

 

[Joe User]

Tried that one out and it works perfectly.

I don't think so.

=0.667*MIN(A1,2500)
+0.5*MIN(MIN(A1-2500,3500),6000)
+0.4*MAX(A1-6000,0)

The middle term looks suspicious. For any value in A1 less than 2500,
MIN(MIN(A1-2500,3500),6000) returns a negative number.

For example, test with A1=2000. The result should be 1334 (2000*66.7%).
Rick's formula returns 1084, viz. 2000*66.7% + (-500*50%).

We can correct Rick's formula, befitting his style, to wit:

=0.667*MIN(A1,2500)
+0.5*MAX(0,MIN(MIN(A1-2500,3500),6000))
+0.4*MAX(0,A1-6000)

But the MIN(MIN(...)) construct seems superfluous: the inner MIN is no more
than 3500, which is always less than 6000.

(Note that if 3500 were replaced with a larger number X, 6000 would also be
replaced with a larger number, 2500+X, which is always larger than X.)

So Rick's corrected formula can be simplified to:

=0.667*MIN(A1,2500)
+0.5*MAX(0,MIN(A1-2500,3500))
+0.4*MAX(0,A1-6000)

PS: You can continue to write 66.7%, 50% and 40% instead of the decimal
fraction equivalents.


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

 

[Prof Wonmug]

Could someone please!!! help me to set up a complex formula which I will use
many times over.

I have an amount - say $7,417. I need to take 66.7% of the first $2,500
(which comes to $1,667.50). This leaves me with $4,917 of the original
amount and I need to take 50% of the next $3,500 ($1,750) - leaving me with
$1,417 of the original number which I need to take 40% of ($566.80) Then, I
add all the percentages up to come to $3,984.30. I have to do this a lot and
could certainly use an easier way other than a calculator and a large sheet
of paper. Thanks.

Here's a little UDF to do the calculation:


'*********************************************************************
' Variable % Function
'
' Syntax: VarPC(amount)
'
' Result = 66.7% of first $2,500
' + 50.0% of next $3,500
' + 40.0% of rest

'*********************************************************************
Function VarPC(ByVal Amount As Single) As Single
Const Part1PC As Single = 0.667: Const Part1Amt As Single = 2500
Const Part2PC As Single = 0.5: Const Part2Amt As Single = 3500
Const Part3PC As Single = 0.4

Dim AmtLeft As Single

AmtLeft = Amount
VarPC = 0

If AmtLeft <= Part1Amt Then
VarPC = VarPC + AmtLeft * Part1PC
Exit Function
Else
VarPC = VarPC + Part1Amt * Part1PC
AmtLeft = AmtLeft - Part1Amt
End If

If AmtLeft <= Part2Amt Then
VarPC = VarPC + AmtLeft * Part2PC
Exit Function
Else
VarPC = VarPC + Part2Amt * Part2PC
AmtLeft = AmtLeft - Part2Amt
End If

VarPC = VarPC + AmtLeft * Part3PC

End Function


And here are some results:

Amount Result
$3.00 $2.00
$30.00 $20.01
$300.00 $200.10
$2,500.00 $1,667.50
$3,000.00 $1,917.50
$3,500.00 $2,167.50
$6,000.00 $3,417.50
$7,000.00 $3,817.50
$7,417.00 $3,984.30


If you set Part1PC to 1/3, instead of .667, $300.00 will give exactly
$200.00