Cell function inputs based on tax bracket

[Icarus69]

Hi guys,
I want a formula that will output how much of my weekly tax is witheld based on my weekly earnings. I'm not an accountant or anything, just someone whose **** about budgeting.

The ATO equation to calculate it is:
y = ax - b.
Where:
y = weekly tax witheld
x = gross weekly income + 99 cents
a and b = coefficients that change according to what bracket my weekly income falls into.

I have a 2 tables: tax-free threshold claimed and not claimed,with different threshold and coefficients. They look something like this:
1 2 3 4 5 6 7
A x> x< a b x y
B 0 500 0.10 10 $1200 ??
C 500 1000 0.20 50
D 1000 1500 0.30 100
E 1500 2000 0.40 150
F 2000 2500 0.45 180
G ...

Lets say my gross weekly earnings 1 week is $1200 (B6), I know that y (B7) can be evaluated using a formula like this:
=IF(AND(B6>B1,B6<=B2), B3*(B6+0.99) - B4, IF(AND(B6>C1,B6<=C2), C3*(B6+0.99) - C4, IF(AND(B6>D1,B6<=D2), D3*(B6+0.99) - D4, IF( ... and so on ))))

However, this method is extremely tedious, especially if the table exists on a separate worksheet and I have more than 1 tax table. Is there a more robust and direct method, ie treating the columns as arrays and using array operations. I also don't see the point in repeating the y=ax-b formula for every range that x could fall into as it is fundamentally the same for every tax bracket.
Any ideas? Thanks

[teylyn]

Hi and welcome to the forum,

this would be a lot easier if you could post your workbook.

cheers,

[Icarus69]

Sorry teylyn,

Here is the file to give you a better idea. Ive highlighted on the spreadsheet the cell containing my original formula (abbreviated), its more like:
=IF(AND(L6>A7,L6<=B7),C7*(L6+0.99)-D7,IF(AND(L6>A8,L6<=B8),C8*(L6+0.99)-D8,IF(AND(L6>A9,L6<=B9),C9*(L6+0.99)-D9,IF(AND(L6>A10,L6<=B10),C10*(L6+0.99)-D10,IF(AND(L6>A11,L6<=B11),C11*(L6+0.99)-D11,IF(AND(L6>A12,L6<=B12),C12*(L6+0.99)-D12,IF(AND(L6>A13,L6<=B13),C13*(L6+0.99)-D13,IF(AND(L6>A14,L6<=B14),C14*(L6+0.99)-D14,0))))))))

where:
column A is xmin of each tax bracket
column B is xmax of each tax bracket
column C is coefficient 'a' of each tax bracket
column D is coefficient 'b' of each tax bracket
cell L6 is x

Cheers

[teylyn]

Hello,

I don't live in the same country as you do, so I may not understand the terminology. But looking at your data, with brackets

Please Login or Register  to view this content.

where would a value of 259 sit? In the >x for row 1 or in the <x for row 2? It either needs to be

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Which one is it?

Judging by your formula, it is the second option (and you can subsequently remove column B, since it is no longer required. With column A set up in this way, the brackets are already defined and can be used for a lookup formula)

Take this one for a spin in M10

=LOOKUP($L$6,$A$7:$A$22,$C$7:$C$22)*($L$6+0.99)-LOOKUP($L$6,$A$7:$A$22,$D$7:$D$22)

Lookup will try to find an exact match of L6 in column A. If an exact match for L6 is not found in column A, it will find the next smallest number in column A. In this format, Lookup will return the value of column C in the same row. The first lookup finds the a of the formula ax-b , the second lookup finds the b (from column D).

cheers, teylyn

[Icarus69]

Thanks Teylyn,

Thats exactly the formula i wanted.

By the way, to clarify, i meant for the table headings to be like this:
| x> | x<= | a | b |

Cheers