Rounding Down, IF and MAX function

Cindyann

New Member
Joined
Apr 24, 2014
Messages
42
Office Version
  1. 365
Platform
  1. Windows
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)
 
Last edited:
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.
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Here is the chart above but with the 28% bracket everything 6000 and above.
Mr excel questions 50.xlsm
JKLMNOWX
20Gross PayBracketexemption amountAmount taxed at 10%Tax at 10%Amount Taxed at 28%28% TaxTotal Tax
2110000%500.000.000.000.000.000.00
2212500%625.000.000.000.000.000.00
2315000%750.000.000.000.000.000.00
2417500%875.000.000.000.000.000.00
2520000%1,000.000.000.000.000.000.00
2622500%1,125.000.000.000.000.000.00
2725000%1,250.000.000.000.000.000.00
2827500%1,375.000.000.000.000.000.00
29300010%1,500.001,500.00150.000.000.00150.00
30325010%1,625.001,625.00162.500.000.00162.50
31350010%1,750.001,750.00175.000.000.00175.00
32375010%1,875.001,875.00187.500.000.00187.50
33400010%2,000.002,000.00200.000.000.00200.00
34425010%2,125.002,125.00212.500.000.00212.50
35450010%2,250.002,250.00225.000.000.00225.00
36475010%2,375.002,375.00237.500.000.00237.50
37500010%2,500.002,500.00250.000.000.00250.00
38525010%2,625.002,625.00262.500.000.00262.50
39550010%2,750.002,750.00275.000.000.00275.00
40575010%2,875.002,875.00287.500.000.00287.50
41600028%3,000.003,000.00300.000.000.00300.00
42625028%3,125.003,000.00300.00125.0035.00335.00
43650028%3,250.003,000.00300.00250.0070.00370.00
44675028%3,375.003,000.00300.00375.00105.00405.00
45700028%3,500.003,000.00300.00500.00140.00440.00
46725028%3,625.003,000.00300.00625.00175.00475.00
47750028%3,750.003,000.00300.00750.00210.00510.00
48775028%3,875.003,000.00300.00875.00245.00545.00
49800028%4,000.003,000.00300.001,000.00280.00580.00
50825028%4,125.003,000.00300.001,125.00315.00615.00
51850028%4,250.003,000.00300.001,250.00350.00650.00
52875028%4,375.003,000.00300.001,375.00385.00685.00
53900028%4,500.003,000.00300.001,500.00420.00720.00
54925028%4,625.003,000.00300.001,625.00455.00755.00
55950028%4,750.003,000.00300.001,750.00490.00790.00
56975028%4,875.003,000.00300.001,875.00525.00825.00
571000028%5,000.003,000.00300.002,000.00560.00860.00
581025028%5,125.003,000.00300.002,125.00595.00895.00
591050028%5,250.003,000.00300.002,250.00630.00930.00
601075028%5,375.003,000.00300.002,375.00665.00965.00
611100028%5,500.003,000.00300.002,500.00700.001,000.00
CindyAnn
Cell Formulas
RangeFormula
K21:K61K21=XLOOKUP(J21,$H$21:$H$24,$G$21:$G$24,"",-1)
L21:L61L21=0.5*J21
M21:M61M21=IF(J21<3000,0,IF(J21<6000,J21-L21,3000))
N21:N61N21=M21*0.1
O21:O61O21=IF(J21<6000,0,J21-L21-M21)
W21:W61W21=O21*0.28
X21:X61X21=N21+W21
 
Upvote 0
Solution
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top