EXCELL FORMULA

[VERN C]

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

 

[Pecoflyer]

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,20
(need
Formula)
-50,000.00 Max B33 10% C33 $5,00
(need
Formula
-190,000.00 MAX (that is left ) B34 5% C34 $9,500(nee
Formula

Does anyone know how to write this formula. I have been working on thi
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 th
deducations.from the
Purchas price.

Thank you

If I understand correctly and your purchase price being in A1 ( b32 3
and 34 contain the percentage correct?)
c32 =min(5000,a1)*b32
c33=if(a1-5000>0,min(a1-5000,50000)*b33;0)
c34=if(a1-50000>0,(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))

HT

--
Pecoflye

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

*Adding your XL version* to your post helps finding solution faste