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]

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
	A	B	C	D	E	F	G	H	I	J
1
2		Overage Amount	Taxable Amount	Tax Rate			Calculations
3							Amounts	Taxable Amt	Tax Rate
4		1500	1000	0.1			1100	1000	0.1
5		2000	2000	0.1			1400	1000	0.1
6		2500	2000	0.1			1500	1000	0.1
7		3000	2000	0.1			1700	1000	0.1
8							2000	2000	0.1
9							2400	2000	0.1
10							2500	2000	0.1
11							3000	2000	0.1
12							3100	2000	0.1
13
CindyAnn

Cell Formulas
Range	Formula
H4:H12	H4	=XLOOKUP(G4,$B$4:$B$7,$C$4:$C$7,MIN(G4,1000),-1,1)

 

[Cindyann]

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
	A	B	C	D	E	F	G	H	I	J
1
2		Overage Amount	Taxable Amount	Tax Rate			Calculations
3							Amounts	Taxable Amt	Tax Rate
4		1500	1000	0.1			1100	1000	0.1
5		2000	2000	0.1			1400	1000	0.1
6		2500	2000	0.1			1500	1000	0.1
7		3000	2000	0.1			1700	1000	0.1
8							2000	2000	0.1
9							2400	2000	0.1
10							2500	2000	0.1
11							3000	2000	0.1
12							3100	2000	0.1
13
CindyAnn

Cell Formulas
Range	Formula
H4:H12	H4	=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

 

[awoohaw]

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.

 

[Cindyann]

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%

 

[awoohaw]

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.

 

[awoohaw]

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]

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

 

[awoohaw]

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

 

[awoohaw]

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?