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)

 

[awoohaw]

I'm at a loss. You are going back to fixed exemptions where you had 50% of it before.

I've asked for expected calculations and I don't know why I have to keep guessing.

 

[awoohaw]

Here is the chart above but with the 28% bracket everything 6000 and above.

Mr excel questions 50.xlsm
	J	K	L	M	N	O								W	X
20	Gross Pay	Bracket	exemption amount	Amount taxed at 10%	Tax at 10%	Amount Taxed at 28%								28% Tax	Total Tax
21	1000	0%	500.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
23	1500	0%	750.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
25	2000	0%	1,000.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
27	2500	0%	1,250.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
29	3000	10%	1,500.00	1,500.00	150.00	0.00								0.00	150.00
30	3250	10%	1,625.00	1,625.00	162.50	0.00								0.00	162.50
31	3500	10%	1,750.00	1,750.00	175.00	0.00								0.00	175.00
32	3750	10%	1,875.00	1,875.00	187.50	0.00								0.00	187.50
33	4000	10%	2,000.00	2,000.00	200.00	0.00								0.00	200.00
34	4250	10%	2,125.00	2,125.00	212.50	0.00								0.00	212.50
35	4500	10%	2,250.00	2,250.00	225.00	0.00								0.00	225.00
36	4750	10%	2,375.00	2,375.00	237.50	0.00								0.00	237.50
37	5000	10%	2,500.00	2,500.00	250.00	0.00								0.00	250.00
38	5250	10%	2,625.00	2,625.00	262.50	0.00								0.00	262.50
39	5500	10%	2,750.00	2,750.00	275.00	0.00								0.00	275.00
40	5750	10%	2,875.00	2,875.00	287.50	0.00								0.00	287.50
41	6000	28%	3,000.00	3,000.00	300.00	0.00								0.00	300.00
42	6250	28%	3,125.00	3,000.00	300.00	125.00								35.00	335.00
43	6500	28%	3,250.00	3,000.00	300.00	250.00								70.00	370.00
44	6750	28%	3,375.00	3,000.00	300.00	375.00								105.00	405.00
45	7000	28%	3,500.00	3,000.00	300.00	500.00								140.00	440.00
46	7250	28%	3,625.00	3,000.00	300.00	625.00								175.00	475.00
47	7500	28%	3,750.00	3,000.00	300.00	750.00								210.00	510.00
48	7750	28%	3,875.00	3,000.00	300.00	875.00								245.00	545.00
49	8000	28%	4,000.00	3,000.00	300.00	1,000.00								280.00	580.00
50	8250	28%	4,125.00	3,000.00	300.00	1,125.00								315.00	615.00
51	8500	28%	4,250.00	3,000.00	300.00	1,250.00								350.00	650.00
52	8750	28%	4,375.00	3,000.00	300.00	1,375.00								385.00	685.00
53	9000	28%	4,500.00	3,000.00	300.00	1,500.00								420.00	720.00
54	9250	28%	4,625.00	3,000.00	300.00	1,625.00								455.00	755.00
55	9500	28%	4,750.00	3,000.00	300.00	1,750.00								490.00	790.00
56	9750	28%	4,875.00	3,000.00	300.00	1,875.00								525.00	825.00
57	10000	28%	5,000.00	3,000.00	300.00	2,000.00								560.00	860.00
58	10250	28%	5,125.00	3,000.00	300.00	2,125.00								595.00	895.00
59	10500	28%	5,250.00	3,000.00	300.00	2,250.00								630.00	930.00
60	10750	28%	5,375.00	3,000.00	300.00	2,375.00								665.00	965.00
61	11000	28%	5,500.00	3,000.00	300.00	2,500.00								700.00	1,000.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,J21-L21-M21)
W21:W61	W21	=O21*0.28
X21:X61	X21	=N21+W21

 

[awoohaw]

if any of the two above are incorrect. Then I will need you to find a way to PASTE a table of your expected results.

 

[Cindyann]

This is fine. I initially didn't want to do a table but it provides the same result so thank you for your time today.

 

[awoohaw]

This is fine. I initially didn't want to do a table but it provides the same result so thank you for your time today.

this can be combined into one formula. i just did this to get your logic down. since you have 365 it will also be easy to put into a LET() function.