Running Totals and Formulas

[excely121]

Hello,

I have a problem I'm hoping one of you guys can solve.. so I have a running total column (B) that sums col A values up and another column (col C) that calculates a remittance amount based on the running total (col B). My current formula is =IF(B2>100000),A2*10%,IF(B2<=100000),A2*15%). However, the formula doesnt quite work when the change occurs at 100K because the amount needs to be split out. For example of the $4,224.76 below, the calculation would be $152.55*15% (the amount under 100K) + $4,072.21*10% (the amount over 100K). Any ideas as to what my the formula should be..? Can't seem to figure it out.

Thanks

 

[mikerickson]

Instead of your IF try
=MIN(B2,100000)*15%+MAX(B2-100000, 0)*10%

 

[JamesCanale]

MrExcelPlayground14.xlsx
	A	B	C
2	$0.00	$0.00	$0.00
3	$907.52	$907.52	$136.13
4	$74.83	$982.35	$11.22
5	$98,710.46	$99,692.81	$14,806.57
6	$152.55	$99,845.36	$22.88
7	$2.09	$99,847.45	$0.31
8	$4,224.76	$104,072.21	$430.10
9	$2,000.00	$106,072.21	$200.00
10		$99,000.00
11	$1,000.00	$100,000.00	$150.00
12	$1,000.00	$101,000.00	$100.00
Sheet12

Cell Formulas
Range	Formula
C11:C12,C2:C9	C2	=LET(a,100000-B1,b,B2-100000,c,SIGN(a),d,SIGN(b),IF(AND(c=1,d=1),a*0.15+b*0.1,IF(B2>100000,A2*0.1,A2*0.15)))
B3:B9	B3	=A3+B2

 

[excely121]

MrExcelPlayground14.xlsx
	A	B	C
2	$0.00	$0.00	$0.00
3	$907.52	$907.52	$136.13
4	$74.83	$982.35	$11.22
5	$98,710.46	$99,692.81	$14,806.57
6	$152.55	$99,845.36	$22.88
7	$2.09	$99,847.45	$0.31
8	$4,224.76	$104,072.21	$430.10
9	$2,000.00	$106,072.21	$200.00
10		$99,000.00
11	$1,000.00	$100,000.00	$150.00
12	$1,000.00	$101,000.00	$100.00
Sheet12

Cell Formulas
Range	Formula
C11:C12,C2:C9	C2	=LET(a,100000-B1,b,B2-100000,c,SIGN(a),d,SIGN(b),IF(AND(c=1,d=1),a*0.15+b*0.1,IF(B2>100000,A2*0.1,A2*0.15)))
B3:B9	B3	=A3+B2

Works perfectly. Thank you!
Will need to learn how this part of the formula works. LET(a,100000-B1,b,B2-100000,c,SIGN(a),d,SIGN(b),IF(AND(c=1,d=1)

 

[JamesCanale]

Here is my 'unpacked' table:

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I
2	$0.00	$0.00	$0.00
3	$907.52	$907.52	$136.13
4	$74.83	$982.35	$11.22
5	$98,710.46	$99,692.81	$14,806.57
6	$152.55	$99,845.36	$22.88	$307.19	-$154.64	1	-1	$46.08	-$15.46
7	$2.09	$99,847.45	$0.31	$154.64	-$152.55	1	-1	$23.20	-$15.26
8	$4,224.76	$104,072.21	$430.10	$152.55	$4,072.21	1	1	$22.88	$407.22
9	$2,000.00	$106,072.21	$200.00	-$4,072.21	$6,072.21	-1	1	-$610.83	$607.22
10		$99,000.00						$0.00	$0.00
11	$1,000.00	$100,000.00	$150.00	$1,000.00	$0.00	1	0	$150.00	$0.00
12	$1,000.00	$101,000.00	$100.00	$0.00	$1,000.00	0	1	$0.00	$100.00
Sheet12

Cell Formulas
Range	Formula
C11:C12,C2:C9	C2	=LET(a,100000-B1,b,B2-100000,c,SIGN(a),d,SIGN(b),IF(AND(c=1,d=1),a*0.15+b*0.1,IF(B2>100000,A2*0.1,A2*0.15)))
B3:B9	B3	=A3+B2
D6:D9,D11:D12	D6	=100000-B5
E6:E9,E11:E12	E6	=B6-100000
F6:G9,F11:G12	F6	=SIGN(D6)
H6:H12	H6	=D6*0.15
I6:I12	I6	=E6*0.1

 

[excely121]

Here is my 'unpacked' table:

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I
2	$0.00	$0.00	$0.00
3	$907.52	$907.52	$136.13
4	$74.83	$982.35	$11.22
5	$98,710.46	$99,692.81	$14,806.57
6	$152.55	$99,845.36	$22.88	$307.19	-$154.64	1	-1	$46.08	-$15.46
7	$2.09	$99,847.45	$0.31	$154.64	-$152.55	1	-1	$23.20	-$15.26
8	$4,224.76	$104,072.21	$430.10	$152.55	$4,072.21	1	1	$22.88	$407.22
9	$2,000.00	$106,072.21	$200.00	-$4,072.21	$6,072.21	-1	1	-$610.83	$607.22
10		$99,000.00						$0.00	$0.00
11	$1,000.00	$100,000.00	$150.00	$1,000.00	$0.00	1	0	$150.00	$0.00
12	$1,000.00	$101,000.00	$100.00	$0.00	$1,000.00	0	1	$0.00	$100.00
Sheet12

Cell Formulas
Range	Formula
C11:C12,C2:C9	C2	=LET(a,100000-B1,b,B2-100000,c,SIGN(a),d,SIGN(b),IF(AND(c=1,d=1),a*0.15+b*0.1,IF(B2>100000,A2*0.1,A2*0.15)))
B3:B9	B3	=A3+B2
D6:D9,D11:D12	D6	=100000-B5
E6:E9,E11:E12	E6	=B6-100000
F6:G9,F11:G12	F6	=SIGN(D6)
H6:H12	H6	=D6*0.15
I6:I12	I6	=E6*0.1

Makes sense now. Thanks for the help.