Formula

[Guest]

I am trying to create a formula where I can put a number (taxable income) in one cell and and have the tax payable appear in another cell by way of formula's

e

If number in CELL <= 6000 then SHOW 0 in another cel
If number in CELL <=21600 then SHOW 0.185 x (21600 - 6000) in another cel
If number in CELL <=52000 then SHOW 0.315 x(52000 - 21600) + 2976 in another cel
If number in CELL < =62500 then SHOW 0.485 x (62500 - 52000) + 12552 in another cel
If number in CELL > 62500 then SHOW 0.485 x (CELL - 62500) + 17119.5 in another cel

I would appreciate any help

Thanks

 

[Leo Heuser]

Tony

Here´s one way, assuming taxable income in A1
and tax payable in B1.

In B1 enter this formula:

=IF(A1<=6000,0,IF(A1<=21600,0.185*(21600-6000),
IF(A1<=52000,0.315*(52000 - 21600) + 2976,
IF(A1<=62500,0.485*(62500 - 52000) + 12552,
0.485*(A1 - 62500) + 17119.5))))

or you could calculate it beforehand:

=IF(A1<=6000,0,IF(A1<=21600,2886,IF(A1<=52000,12552,
IF(A1<=62500,17644.5,0.485*A1-13193))))



--
Best Regards
Leo Heuser

Followup to newsgroup only please.

I am trying to create a formula where I can put a number (taxable income)

in one cell and and have the tax payable appear in another cell by way of
formula's.

 

[Guest]

agian you can use the vba function: "Worksheet_Change"
each use you change CELL this function will be activated
and put in the target cell the output.
For example if CELL is A1 and the target is A2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim val
val = Sheet1.Cells(1, 1)

If Target.Address = "$A$1" Then
If val <= 6000 Then
Sheet1.Cells(1, 2) = 0
Else
If val <= 21600 Then
Sheet1.Cells(1, 2) = 0.185 * (21600 - 6000)
Else
If val <= 52000 Then
Sheet1.Cells(1, 2) = 0.315 * (52000 -
21600) + 2976
Else
If val <= 62500 Then
Sheet1.Cells(1, 2) = 0.485 *
(62500 - 52000) + 12552
Else
Sheet1.Cells(1, 2) = 0.485 * (val -
62500) + 17119.5
End If
End If
End If
End If
End If

End Sub

check that i don't have mistakes in my logic.

Good Luck, Tal.

-----Original Message-----
I am trying to create a formula where I can put a number

(taxable income) in one cell and and have the tax payable
appear in another cell by way of formula's.

eg

If number in CELL <= 6000 then SHOW 0 in another cell
If number in CELL <=21600 then SHOW 0.185 x (21600 - 6000) in another cell
If number in CELL <=52000 then SHOW 0.315 x(52000 - 21600) + 2976 in another cell
If number in CELL < =62500 then SHOW 0.485 x (62500 - 52000) + 12552 in another cell
If number in CELL > 62500 then SHOW 0.485 x (CELL -

62500) + 17119.5 in another cell