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" <(E-Mail Removed)> wrote in message
news:87CAF9E4-5F9E-4BB6-B0BA-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:87CAF9E4-5F9E-4BB6-B0BA-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:87CAF9E4-5F9E-4BB6-B0BA-(E-Mail Removed)...
> > > 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.
> >
> > .
> >