Help with IF statement on taxes

J

JamieLPD

How do I create an IF statement that will display:

£ 0 to£4,745 = "No tax"
£4,746 to£6,765 = "10%"
£6,766 to£36,145 = "22%"
£36,146 + = "40%"

The figure it is to look at will be C365.

Jamie
 
J

JamieLPD

Thanks but I am struggling on changing the tax bands - am not very goo
at excel.

Jami
 
J

JamieLPD

So far I have:

=SUMPRODUCT(--(C365>{4745;6765;36145;36146}),
(C365-{4745;6765;36145;36146}), {0.17;0.13;0.12;0.05})
 
J

JamieLPD

Got it, I think:

=SUMPRODUCT(--(C365>{4745;6765;36145}), (C365-{4745;6765;36145}),
{0.1;0.12;0.18})

Jamie
 
M

Michael M

Hi Jamie
If your input data is in A1, this will do the trick

=IF(A1<=4745,"No
Tax",IF(AND(A1>=4746,A1<=6765),"10%",IF(AND(A1>=6766,A1<=36145),"22%","40%")))

Do you really want the tax rate to appear though ??, or do you want the tax
rate calculated ??

If so, replace "10%", "22%", etc with A1*.10, A1*.22, etc.

HTH
Michael
 
A

Ashish Mathur

Hi Jamie,

Income slab Rate

- 0%
50,000.00 10%
150,000.00 20%
320,000.00 30%
600,000.00 40%

Suppose you have the aboce data in range B2:C8 (including the column heading
of "income slab and "rate"). Now in cell B12, enter the income on which you
want to compute the tax. In cell C12, enter the following array formula
(Ctrl+Shift+Enter)

=SUM(IF($B$12<B4:B8,0,IF(B5:B9-B4:B8>$B$12,$B$12-B4:B8,IF(B5:B9-B4:B8>0,IF(B5:B9-B4:B8>$B$12-B4:B8,$B$12-B4:B8,B5:B9-B4:B8),$B$12-B4:B8)))*(C4:C8))

Please change the income slabs and rate as desired.

Regards,

Ashish Mathur
 
Top