I am trying to create a work sheet that can do the following;


C29 is $245,000.00 Purchas price.

A 32 0- $5,000 take B32 25 % C32 $
1,250.
A33 $5001. to $50,000 take B33 10% C33 $ 5,000
A 34 $50,001 to 1,000,000. Take B34 5 % C34 $ 9,500
Total
C 35 15,750.00
..


Each time I take a % i I need to deduct the Maxium range amount from the
245,000.00.
Purchase Price until there is nothing to deduct.
Example

$ 245,000.00
-5,000.00 Max B32 25% C32 $1,200 (need
Formula)
-50,000.00 Max B33 10% C33 $5,000 (need
Formula
-190,000.00 MAX (that is left ) B34 5% C34 $9,500(need Formula

Does anyone know how to write this formula. I have been working on this for
hours I know I am missing a step.

I need a formula for C32_ C35
I am not sure if I need another cell to calculate the deducations.from the
Purchas price.

Thank you

VERN C;216358 Wrote:
I am trying to create a work sheet that can do the following;


C29 is $245,000.00 Purchas price.

A 32 0- $5,000 take B32 25 % C32
$
1,250.
A33 $5001. to $50,000 take B33 10% C33 $
5,000
A 34 $50,001 to 1,000,000. Take B34 5 % C34 $
9,500
Total
C 35 15,750.00
..


Each time I take a % i I need to deduct the Maxium range amount
from the
245,000.00.
Purchase Price until there is nothing to deduct.
Example

$ 245,000.00
-5,000.00 Max B32 25% C32 $1,200
(need
Formula)
-50,000.00 Max B33 10% C33 $5,000
(need
Formula
-190,000.00 MAX (that is left ) B34 5% C34 $9,500(need
Formula

Does anyone know how to write this formula. I have been working on this
for
hours I know I am missing a step.

I need a formula for C32_ C35
I am not sure if I need another cell to calculate the
deducations.from the
Purchas price.

Thank you

If I understand correctly and your purchase price being in A1 ( b32 33
and 34 contain the percentage correct?)
c32 =min(5000,a1)*b32
c33=if(a1-50000,min(a1-5000,50000)*b33;0)
c34=if(a1-500000,(a1-50000)*b34,0)

You can also have the total result in one go
=sumproduct((a1{0,5000,50000})*(a1-{0,5000,50000})*(b32,b33-b32,b34-b33))

HTH


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=59425