Retain value according to month

[Broken Calculator]

I have a sheet that I track monthly amounts (A1:A12) that amount is then taxed (B1:B12). At the moment in order to keep the tax amount that corresponds to the monthly amount I use a separate calculation for each month. Is there a way to use the monthly tax calculation to retain each month in cells B1:B12). If I use the cell reference that gives me the tax amount it changes everything above it according to the last month entered.

 

[Dave Patton]

N.B.
I do not know what county you live in. I do not know your tax system or the rules and regulations.
You can calculate the tax with a formula.
You could calculate based on annualized numbers.
You could consider the personal deductions in the calculation.
There are many ways to construct the formula.

Commission Cumulative Sales.xlsm
	A	B	C
1	Bracket/ Hurdle	Rate	Rate Differential
2	0.00	0%	0%
3	1,047.50	19%	19%
4	1,239.67	20%	1%
5	2,213.42	21%	1%
6	3,638.50	42%	21%
7	6,250.00	45%	3%
8	10,428.33	48%	3%
9
10
11		2,000.00	188.58
12		2,100.00	208.58
13		3,100.00	417.44
14
15
1c

Cell Formulas
Range	Formula
C2:C8	C2	=B2-N(B1)
C11:C13	C11	=SUMPRODUCT(--(B11>$A$2:$A$8),B11-$A$2:$A$8,$C$2:$C$8)

 

[Broken Calculator]

Thanks for the reply, will try this and see if so can get it to work.

 

[Dave Patton]

Thanks for the reply.

You can download the information to a clean sheet.
Select the icon below the f(x) in the heading, move to cell A1 of your sheet, and paste.