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.

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)

--
Rick (MVP - Excel)



"RENEE" wrote in message
...
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.

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" wrote:
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.

Thanks, a little complex, but great.

"Joe User" wrote:



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" wrote:
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.

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

"Rick Rothstein" wrote:

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)

--
Rick (MVP - Excel)



"RENEE" wrote in message
...
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.

.

On Wed, 28 Apr 2010 15:33:02 -0700, RENEE
wrote:

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.

"Rick Rothstein" wrote:

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)

--
Rick (MVP - Excel)



"RENEE" wrote in message
...
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.

.

"RENEE" wrote:
Tried that one out and it works perfectly.

I don't think so.


"Rick Rothstein" wrote:
=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 -----

"RENEE" wrote:
Tried that one out and it works perfectly. Thanks so much!!!!

"Rick Rothstein" wrote:

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)

--
Rick (MVP - Excel)



"RENEE" wrote in message
...
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.

.