Excel Formula Help/ Question

[emdog]

Hello,

I need some help with creating a formula, there has to be an easy way to do this. I have been trying to figure this out for hours now and I need some help. I am trying to create a formula for this table below. If the number is within the range of numbers it needs to be multiplied by the certain percentage.

This is easy to do but this is issue I am coming into: (I will be using the 30,000 as an example)

If the amount is greater than 3000 then the first 3,000 is multiplied by 1% then the next 3,000 is multiplied by 2% and so on. I need help with a formula for this. Is this even possible?

 

[Coyotex3]

Hi! Welcome to Mr.Excel!

Should the total amount due for this be $1,194.85?

 

[Excel Novice 1]

I'm certain there's a better way but this is my 10 minute novice answer. Row 3 is the $0-$3,000 line; Column A = Min Income; E3 is the Taxable Income amount.

=IF(MATCH(E3,$B$3:$B$7,1)>=1,(INDEX($B$3:$B$7,1)-INDEX($A$3:$A$7,1))*INDEX($C$3:$C$7,1),0)+IF(MATCH(E3,$B$3:$B$7,1)>=2,(INDEX($B$3:$B$7,2)-INDEX($A$3:$A$7,2))*INDEX($C$3:$C$7,2),0)+IF(MATCH(E3,$B$3:$B$7,1)>=3,(INDEX($B$3:$B$7,3)-INDEX($A$3:$A$7,3))*INDEX($C$3:$C$7,3),0)+IF(MATCH(E3,$B$3:$B$7,1)>=4,(INDEX($B$3:$B$7,4)-INDEX($A$3:$A$7,4))*INDEX($C$3:$C$7,4),0)+IF(MATCH(E3,$B$3:$B$7,1)>=5,(INDEX($B$3:$B$7,4)-INDEX($A$3:$A$7,4))*INDEX($C$3:$C$7,4),0)+(E3-OFFSET($B$2,MATCH(E3,$B$3:$B$7,1),))*INDEX($C$3:$C$7,MATCH(E3,$B$3:$B$7,1)+1)

 

[dreid1011]

Book1
	A	B	C	D	E	F	G
1	Tax Table
2	Min. Income	Max Income	Tax			Taxable Income	30,000
3	-	3,000	1.0%			Amount Due	1194.90
4	3,001	6,000	2.0%
5	6,001	11,000	3.0%
6	11,001	25,000	5.0%
7	25,001	100,000,000	5.1%
Sheet1

Cell Formulas
Range	Formula
G3	G3	=IF(G2>B6,((G2-B6)*C7)+939.9,IF(AND(G2<A7,G2>B5),((G2-B5)*C6)+239.95,IF(AND(G2<A6,G2>B4),((G2-B4)*C5)+89.98,IF(AND(G2<A5,G2>B3),((G2-B3)*C4)+30,G2*C3))))

 

[emdog]

Hello,

I need some help with creating a formula, there has to be an easy way to do this. I have been trying to figure this out for hours now and I need some help. I am trying to create a formula for this table below. If the number is within the range of numbers it needs to be multiplied by the certain percentage.
View attachment 104311

This is easy to do but this is issue I am coming into: (I will be using the 30,000 as an example)

If the amount is greater than 3000 then the first 3,000 is multiplied by 1% then the next 3,000 is multiplied by 2% and so on. I need help with a formula for this. Is this even possible?

The answer that I should be getting is 1,235.

 

[dreid1011]

The answer that I should be getting is 1,235.

Can you explain why it should be 1,235?

 

[Eric W]

Here's another option. The table in columns I:J is only there to check the result of the formula in G2.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Threshold	Cap	Rate	Delta		Amount	Tax		Amount in each tier	Amt*Rate of that tier
2	0	3000	1.00%	1.00%		$ 30,000.00	$ 1,195.00		3000	30.00
3	3000	6000	2.00%	1.00%					3000	60.00
4	6000	11000	3.00%	1.00%					5000	150.00
5	11000	25000	5.00%	2.00%					14000	700.00
6	25000	100000000	5.10%	0.10%					5000	255.00
7
8									30000	$ 1,195.00
Sheet3

Cell Formulas
Range	Formula
G2	G2	=SUMPRODUCT(--(F2>=A2:A6),(F2-A2:A6),D2:D6)
J2:J6	J2	=I2:I6*C2:C6
D2:D6	D2	=C2-N(C1)
I8:J8	I8	=SUM(I2:I6)
Dynamic array formulas.