Rounding Down, IF and MAX function

[Cindyann]

Hello,

I've been searching and trying different formulas for some time and to no avail.

I want help with a ROUNDDOWN and maybe a MAX formula to calculate tax. So basically, the first $2,000 is taxed at 10%. $2,000.00 is the max that can be taxed at 10%.

e.g. if the tax overage is $1,500, then only $1,000 will be taxed at 10%, if its $2,500, only $2,000 will be taxed at 10%, if its $3,000, then only $2,000 will be taxed at 10%

So the formula would essentially, if the total is more than 2000, MAX 2000 but if it's less than 2000, then round down to $1000. Im using FLOOR and MAX but have no idea how to bring them together.

=IF(Y9<2000;IF(FLOOR(Y9;1000))) or =IF(Y9>2000;2000;Y7-Y9)

 

[Cindyann]

Here you go.

Mr excel questions 50.xlsm
	J	K	L	M	N	O								W
19	To help me understand, can you complete this:
20	Gross Pay	exemption amount	Amount taxed at 10%	Tax at 10%	Amount Taxed at 28%	Tax at 28%								Total Tax
21	1000
22	1250
23	1500
24	1750
25	2000
26	2250
27	2500
28	2750
29	3000
30	3250
31	3500
CindyAnn

 

[Cindyann]

and can you post into table (not xl2bb) instead of a picture.

Unfortunately, Linux is recognizing the add on as corrupt and not allowing me to install it

 

[awoohaw]

so, is the bracket for
[3000 to 6000) 10%
[6000 to 10000) 28%
what is [10000 and up] ?

in your 4000 example, it would be .5 of 4000 is 2000, .... how is that in the [3000-6000) bracket, or are you assigning the bracket before the 50% tax free

Here you go.

thanks, i don't see any 2000 exemptions.

 

[awoohaw]

Unfortunately, Linux is recognizing the add on as corrupt and not allowing me to install it

it isn't an add on to PASTE, highlight cells and copy (CTRL-C on windows)

 

[Cindyann]

So, It isn't fixed ranges but 1/2 of gross, and where that fits into a tranch (of sorts).
Any other things in the calculations that need to be stated?

No that it, but it's half of the tax bracket and not half of the gross

 

[awoohaw]

well, before I build a big formula i want to get it fleshed out in the table format I have above.

Where do the 2000 exemptions start. And what is the bracket % above 10000?

 

[Cindyann]

so, is the bracket for
[3000 to 6000) 10%
[6000 to 10000) 28%
what is [10000 and up] ?

in your 4000 example, it would be .5 of 4000 is 2000, .... how is that in the [3000-6000) bracket, or are you assigning the bracket before the 50% tax free

thanks, i don't see any 2000 exemptions.

That's because anything under 3000 isn't taxable. The taxes are calculated on the remaining gross balance after the exemption is removed. The salary brackets just let you know what the exemption amount should be.

 

[Cindyann]

Tax Brackets are as follows

0-2999 - No tax applied
3000-6000 = 1500 tax exemption
60001-10000 = 3000 tax exemption
10000 and above = 5000 tax exemption

Once the exemption is removed, 10% is taxed on the 1st 2000 and 28% on the remaining

 

[awoohaw]

does this match what your results should be:

Mr excel questions 50.xlsm
	J	K	L	M	N	O								W	X	Y	Z
20	Gross Pay	Bracket	exemption amount	Amount taxed at 10%	Tax at 10%	Amount Taxed at 28%									Amt Taxed at 48%		Total Tax
21	1000	0%	500.00	0.00	0.00	0.00								0.00	0.00	0.00	0.00
22	1250	0%	625.00	0.00	0.00	0.00								0.00	0.00	0.00	0.00
23	1500	0%	750.00	0.00	0.00	0.00								0.00	0.00	0.00	0.00
24	1750	0%	875.00	0.00	0.00	0.00								0.00	0.00	0.00	0.00
25	2000	0%	1,000.00	0.00	0.00	0.00								0.00	0.00	0.00	0.00
26	2250	0%	1,125.00	0.00	0.00	0.00								0.00	0.00	0.00	0.00
27	2500	0%	1,250.00	0.00	0.00	0.00								0.00	0.00	0.00	0.00
28	2750	0%	1,375.00	0.00	0.00	0.00								0.00	0.00	0.00	0.00
29	3000	10%	1,500.00	1,500.00	150.00	0.00								0.00	0.00	0.00	150.00
30	3250	10%	1,625.00	1,625.00	162.50	0.00								0.00	0.00	0.00	162.50
31	3500	10%	1,750.00	1,750.00	175.00	0.00								0.00	0.00	0.00	175.00
32	3750	10%	1,875.00	1,875.00	187.50	0.00								0.00	0.00	0.00	187.50
33	4000	10%	2,000.00	2,000.00	200.00	0.00								0.00	0.00	0.00	200.00
34	4250	10%	2,125.00	2,125.00	212.50	0.00								0.00	0.00	0.00	212.50
35	4500	10%	2,250.00	2,250.00	225.00	0.00								0.00	0.00	0.00	225.00
36	4750	10%	2,375.00	2,375.00	237.50	0.00								0.00	0.00	0.00	237.50
37	5000	10%	2,500.00	2,500.00	250.00	0.00								0.00	0.00	0.00	250.00
38	5250	10%	2,625.00	2,625.00	262.50	0.00								0.00	0.00	0.00	262.50
39	5500	10%	2,750.00	2,750.00	275.00	0.00								0.00	0.00	0.00	275.00
40	5750	10%	2,875.00	2,875.00	287.50	0.00								0.00	0.00	0.00	287.50
41	6000	28%	3,000.00	3,000.00	300.00	0.00								0.00	0.00	0.00	300.00
42	6250	28%	3,125.00	3,000.00	300.00	125.00								35.00	0.00	0.00	335.00
43	6500	28%	3,250.00	3,000.00	300.00	250.00								70.00	0.00	0.00	370.00
44	6750	28%	3,375.00	3,000.00	300.00	375.00								105.00	0.00	0.00	405.00
45	7000	28%	3,500.00	3,000.00	300.00	500.00								140.00	0.00	0.00	440.00
46	7250	28%	3,625.00	3,000.00	300.00	625.00								175.00	0.00	0.00	475.00
47	7500	28%	3,750.00	3,000.00	300.00	750.00								210.00	0.00	0.00	510.00
48	7750	28%	3,875.00	3,000.00	300.00	875.00								245.00	0.00	0.00	545.00
49	8000	28%	4,000.00	3,000.00	300.00	1,000.00								280.00	0.00	0.00	580.00
50	8250	28%	4,125.00	3,000.00	300.00	1,125.00								315.00	0.00	0.00	615.00
51	8500	28%	4,250.00	3,000.00	300.00	1,250.00								350.00	0.00	0.00	650.00
52	8750	28%	4,375.00	3,000.00	300.00	1,375.00								385.00	0.00	0.00	685.00
53	9000	28%	4,500.00	3,000.00	300.00	1,500.00								420.00	0.00	0.00	720.00
54	9250	28%	4,625.00	3,000.00	300.00	1,625.00								455.00	0.00	0.00	755.00
55	9500	28%	4,750.00	3,000.00	300.00	1,750.00								490.00	0.00	0.00	790.00
56	9750	28%	4,875.00	3,000.00	300.00	1,875.00								525.00	0.00	0.00	825.00
57	10000	40%	5,000.00	3,000.00	300.00	2,000.00								560.00	0.00	0.00	860.00
58	10250	40%	5,125.00	3,000.00	300.00	2,000.00								560.00	125.00	60.00	920.00
59	10500	40%	5,250.00	3,000.00	300.00	2,000.00								560.00	250.00	120.00	980.00
60	10750	40%	5,375.00	3,000.00	300.00	2,000.00								560.00	375.00	180.00	1,040.00
61	11000	40%	5,500.00	3,000.00	300.00	2,000.00								560.00	500.00	240.00	1,100.00
CindyAnn

Cell Formulas
Range	Formula
K21:K61	K21	=XLOOKUP(J21,$H$21:$H$24,$G$21:$G$24,"",-1)
L21:L61	L21	=0.5*J21
M21:M61	M21	=IF(J21<3000,0,IF(J21<6000,J21-L21,3000))
N21:N61	N21	=M21*0.1
O21:O61	O21	=IF(J21<6000,0,IF(J21<10000,J21-L21-M21,2000))
W21:W61	W21	=O21*0.28
X21:X61	X21	=IF(J21<10000,0,J21-L21-M21-O21)
Y21:Y61	Y21	=X21*0.48
Z21:Z61	Z21	=N21+W21+Y21

 

[awoohaw]

Tax Brackets are as follows

0-2999 - No tax applied
3000-6000 = 1500 tax exemption
60001-10000 = 3000 tax exemption
10000 and above = 5000 tax exemption

Once the exemption is removed, 10% is taxed on the 1st 2000 and 28% on the remaining