IF Formula help.

[surajdaichung]

Hello Everyone,

this is first time for me I am using the IF formula and i cant seem to get it right.

here is my query:

PAYE Tax should use a function to test the Gross Income and calculate the amount of PAYE Tax. (If Gross Income is less than $65,000 – calculate 0% tax, if Gross Income is less than $100,000 – calculate 5% tax, else calculate 10% tax)

			Month Deductions
Applicant ID	Applicant First Name	Applicant Last Name	Gross Income	PAYEE TAX	Health Surcharge	Union Dues	TOTAL DEDUCTIONS

Any help would be greatly appreciated. Also I need to do the IF formula for the TOTAL DEDUCTIONS.

 

[Joe4]

Welcome to the Board!

Is this a graduated tax?
So, if your Gross Income is between $65,000 and $100,000 (say it is $80,000), is the 5% tax applied on the WHOLE amount ($80,000), or just on the amount over $65,000 (which would be $15,000)?

 

[surajdaichung]

Welcome to the Board!

Is this a graduated tax?
So, if your Gross Income is between $65,000 and $100,000 (say it is $80,000), is the 5% tax applied on the WHOLE amount ($80,000), or just on the amount over $65,000 (which would be $15,000)?

Hello Mr. Joe4,

Below 65,000 would be zero percent i.e no tax. Over 65,000 but less than 100,000 is % on the difference between 65,000 and 100,000 which is 35,000 x 5%. Over 100,000 will attract 10% following the same rule i.e 105,000-65,000 x 10%.

 

[Joe4]

Below 65,000 would be zero percent i.e no tax. Over 65,000 but less than 100,000 is % on the difference between 65,000 and 100,000 which is 35,000 x 5%. Over 100,000 will attract 10% following the same rule i.e 105,000-65,000 x 10%.

Hmmm... You seem to have two conflicting things there. If it truly is a graduated tax, then for a Gross Income over $100,000, it would typically tax:
0% on the amount up to $65,000
5% on the amounts between $65,000 and $100,000
10% on the amount over $100,000

So, if someone made $105,000, their total tax would be:
($35,000 * 5%) + ($5,000 * 10%)

If that is correct, then the tax formula for a Gross Income in cell D2 would look like:

=(MIN(MAX(0,D2-65000),35000)*0.05)+(MAX(0,D2-100000)*0.1)

 

[surajdaichung]

okay but the question is asking to use the IF function to calculate the value of the taxes.

 

[Joe4]

okay but the question is asking to use the IF function to calculate the value of the taxes.

So, this is some sort of assignment/homework then.
It would not be ethical for me to do your assignment for you, but I will give you a hint and point you in the correct direction.
Take a look at using Nested IFs, where you check the less of Gross Income (at the different break points) and then calculate the amount based on that.
See this link to see how to set up Nested IF statements: Excel formula: Nested IF function example | Exceljet

 

[surajdaichung]

So, this is some sort of assignment/homework then.
It would not be ethical for me to do your assignment for you, but I will give you a hint and point you in the correct direction.
Take a look at using Nested IFs, where you check the less of Gross Income (at the different break points) and then calculate the amount based on that.
See this link to see how to set up Nested IF statements: Excel formula: Nested IF function example | Exceljet

Hey Thanks for the TIP! Much appreciated, I will follow the directions and see how it goes.

 

[Joe4]

You are welcome.

One other tip, I would recommend nesting from the top down, i.e.
=IF(D2>100000,...)

That way you do not need to check if it is "in-between" anything, just if it is greater than (i.e. if it is greater than 1000000, do this calculation, if it is greater than 65000, do that calculation, else return 0).