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:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
your statement really confuses me. Can you build a chart like the one on the left below? And if that one is correct, then maybe the formula on the right will work for you:

Book2
ABCDEFGHIJ
1
2Overage AmountTaxable AmountTax RateCalculations
3AmountsTaxable AmtTax Rate
4150010000.1110010000.1
5200020000.1140010000.1
6250020000.1150010000.1
7300020000.1170010000.1
8200020000.1
9240020000.1
10250020000.1
11300020000.1
12310020000.1
13
CindyAnn
Cell Formulas
RangeFormula
H4:H12H4=XLOOKUP(G4,$B$4:$B$7,$C$4:$C$7,MIN(G4,1000),-1,1)
 
Upvote 0
your statement really confuses me. Can you build a chart like the one on the left below? And if that one is correct, then maybe the formula on the right will work for you:

Book2
ABCDEFGHIJ
1
2Overage AmountTaxable AmountTax RateCalculations
3AmountsTaxable AmtTax Rate
4150010000.1110010000.1
5200020000.1140010000.1
6250020000.1150010000.1
7300020000.1170010000.1
8200020000.1
9240020000.1
10250020000.1
11300020000.1
12310020000.1
13
CindyAnn
Cell Formulas
RangeFormula
H4:H12H4=XLOOKUP(G4,$B$4:$B$7,$C$4:$C$7,MIN(G4,1000),-1,1)
Sorry about that.

Your interpretation is spot on except I don't want a table which would mean having to update all potential numbers in the table because a salary can be 6253.23 which would then have to be rounded down to 6000, have 2000 removed and taxed at 10% and then have the remaining 4000 taxed at 28%.

This formula works with figures higher than 2000 and I cannot get it to be accurate if it's less than 2000

=IF(Y9>2000;2000;Y7-Y9)

1500 is taxable
1000 at 10%
500 at 28%

Y9 = 1500
Y7 = 1000

Cannot upload a mini, can provide a sheet shot, using Linux
 

Attachments

  • Screenshot from 2023-07-15 20-39-21.png
    Screenshot from 2023-07-15 20-39-21.png
    20.3 KB · Views: 9
Upvote 0
Well, i did not notice anywhere in the initial post that the balance is taxed at 28%.
Is Y9 the taxable amount and Y7 the gross amount?

I'm probably still confused on other issues, but I'll bang on it.
 
Upvote 0
Well, i did not notice anywhere in the initial post that the balance is taxed at 28%.
Is Y9 the taxable amount and Y7 the gross amount?

I'm probably still confused on other issues, but I'll bang on it.
Yes that is correct, Y11 is where the first 2000 is taxed at 10% and Y12 is at 28%
 
Upvote 0
In your example why is the exemption for $3000 an amount of $1500 instead of $2000?
Isn't the exemption the amount of the gross pay portion that is not subject to tax.

So for 3000, wouldn't there be an exemption of 2000, a tax of 10% on 1000, so 100?
And of 4000, an exmption of 2000, tax of 10% on 1000 of 100, and tax of 28% on 1000 of 280, so a total of 380?

If this is wrong please give some expected outcomes for a wide range of values.
 
Upvote 0
Mr excel questions 50.xlsm
JKLMNOW
19To help me understand, can you complete this:
20Gross Payexemption amountAmount taxed at 10%Tax at 10%Amount Taxed at 28%Tax at 28%Total Tax
211000
221250
231500
241750
252000
262250
272500
282750
293000
303250
313500
CindyAnn
 
Upvote 0
In your example why is the exemption for $3000 an amount of $1500 instead of $2000?
Isn't the exemption the amount of the gross pay portion that is not subject to tax.

So for 3000, wouldn't there be an exemption of 2000, a tax of 10% on 1000, so 100?
And of 4000, an exmption of 2000, tax of 10% on 1000 of 100, and tax of 28% on 1000 of 280, so a total of 380?

If this is wrong please give some expected outcomes for a wide range of values.Tax ia applied
Tax is applied to three different salary brackets, 3000 and above but before 6000, 6000 and above but before 10,000 and above 10000

So half of the salary bracket is always exempt and then the rest is taxed. So 3000 = 1500, 6000 = 3000 etc

So another example would be if the salary is 4000, then that person would fall in the '3000 and above but before the 6000' bracket. the first 1500 would be tax-free and the remainder applicable to taxes
 
Upvote 0
and can you post into table (not xl2bb) instead of a picture.
 
Upvote 0
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? :)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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