Thanks Harry,

As this formula is only a small part of a much bigger spreadsheet how do I
make the I,J,K, and L workings invisable so they work behind the scenes.

I have space for two applicants on the spread sheet:

Appl 1: Gross figure=k43 Tax paid = N43 and net income = Q43
Appl 2: Gross figure=k44 Tax paid = N44 and net income = Q44

Is there a way i can show you the spreadsheet instead of clumsily trying to
explain what i am trying to acheive. I have Skype if that helps.Skype
address: Scoober1

Cheers Scott


"HaSt2307" wrote:

Scoober

A1 = gross yearly wage
A2 = IF(AND($A$1 $I$2,$A$1<$J$2),$A$1*$K$2,IF(AND($A$1
$I$3,$A$1<$J$3),($A$1*$K$3)+$M$3,IF(AND($A$1
$I$4,$A$1<$J$4),($A$1*$K$4)+$M$4,IF(AND($A$1
$I$5,$A$1<$J$5),($A$1*$K$5)+$M$5,"No Match"))))
A3 = A1-B1

You will still need the data column I, J, K and L.

I uploaded a sample file at http://freefilehosting.net/download/42jj4

Regards
Harry

Scoober wrote:
Thanks for your help harry,

Excuse my ignorance but can you tell me which code to post in which box
useing my A1 A2 A3 example?

Cheers Scott

"HaSt2307" wrote:

Scoober,

For a little more complicated way than has been posted here is the
algebra behind what you are asking for:

12.5% - .125x where x is the dollars less than 17500

21% - is the max tax 17500 and below plus the tax on the amount over
17500. So .125(17500) + .21(x - 17500) = -1487.50 + .21x

33% - is the max tax 17500 and below plus the max tax for 17501-40000
times 33% So .125(17500) + .21(40000-17500) + .33(x - 40000) =
-6287.50 + .33x

39% - is max tax of the three previous bands + .39x. So .125(17500) +
..21(40000-17500) + .33(75000-40000) + .39(x - 75000) = -10787.5 + .39x

=IF(AND($A$1 I2,$A$1<J2),$A$1*$K2,
IF(AND($A$1 I3,$A$1<J3),($A$1*$K3)+L3,
IF(AND($A$1 I4,$A$1<J4),($A$1*$K4)+L4,
IF(AND($A$1 I5,$A$1<J5),($A$1*$K5)+L5,"No Match"))))

A1 = amount entered
I J K L
1 Low High Tax Dif Rate
2 0 17500 12.50% 0.125
3 17501 40000 21% -1487.50
4 40001 75000 33% -6287.50
5 75001 10^10 39% -10787.50
Big Num
L2 .125
L3 =K2*J2+K3*-J2
L4 =(K2*J2)+K3*(J3-J2)+K4*(-J3)
L5 =(K2*J2)+K3*(J3-J2)+K4*(J4-J3)+K5*(-J4)

Regards
Harry


Scoober wrote:
Can someone show me the correct forumla to provide a net mothly figure

e.g
if a gross yearly wage figure is entered into A1 how can i show the tax paid
in A2 and the resulting nett monthly figure in A3

The formula has to work within the following thresholds:
0-$17,500 taxed at 12.5%
$17,501 - $40,000 taxed at 21%
$40,001 - $75,000.00 taxed at 33%
$75,001 and above taxed at 39%