Help with IF statement on taxes

[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

[Peo Sjoblom]

You can adapt this

http://www.mcgimpsey.com/excel/variablerate.html

there is one for marginal tax rates

--
Regards,

Peo Sjoblom

Portland, Oregon




"JamieLPD" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> JamieLPD
> ------------------------------------------------------------------------
> JamieLPD's Profile:
> http://www.excelforum.com/member.php...o&userid=31386
> View this thread: http://www.excelforum.com/showthread...hreadid=510792
>

[JamieLPD]

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

Jamie

[JamieLPD]

So far I have:

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

[JamieLPD]

Got it, I think:

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

Jamie

[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

"JamieLPD" wrote:

>
> 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
>
>
> --
> JamieLPD
> ------------------------------------------------------------------------
> JamieLPD's Profile: http://www.excelforum.com/member.php...o&userid=31386
> View this thread: http://www.excelforum.com/showthread...hreadid=510792
>
>

[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


"JamieLPD" wrote:

>
> 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
>
>
> --
> JamieLPD
> ------------------------------------------------------------------------
> JamieLPD's Profile: http://www.excelforum.com/member.php...o&userid=31386
> View this thread: http://www.excelforum.com/showthread...hreadid=510792
>
>