• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Payroll Tax calculation using If

George-1947

New Member
Hello. I am working working on a payroll program for Australian PAYG taxation and downloaded the relevant Vlookup tables but they don't resemble any Vlookup tables I have seen before. The programming code to call them up is also way beyond what I know.
However, I successfully integrated them into my program with two exceptions.
Australia uses two scales - one for those on Tax-Free Threshold and one for those on No Tax-Free Threshold.
The Tax-Free Threshold works perfectly using external links, but the No Tax-Free Threshold produces, "False" instead of the required numeric value, however, if an amount is entered directly into its box the correct figure is given.

This is the original Fortnightly formula:
=ROUND((TRUNC((A5/2),0)+0.99)*(VLOOKUP((TRUNC((A5/2),0)),LU_Scale1,2))-VLOOKUP((TRUNC((A5/2),0)),LU_Scale1,3),0)*2

This is the original Monthly formula: =ROUND((ROUND((TRUNC((3/13)*(A5+IF(ISNUMBER(SEARCH(".33",A5)),0.01,0)),0)+0.99)*(VLOOKUP((TRUNC((3/13)*(A5+IF(ISNUMBER(SEARCH(".33",A5)),0.01,0)),0)),LU_Scale1,2))-(VLOOKUP((TRUNC((3/13)*(A5+IF(ISNUMBER(SEARCH(".33",A5)),0.01,0)),0)),LU_Scale1,3)),0)*(13/3)),0)

Both use Scale 1
This my modified formula: =IF($H4<1,"",IF('Employee List'!$D2="Weekly",ROUND((TRUNC(($H4),0)+0.99)*(VLOOKUP((TRUNC(($H4),0)),V15:X20,2))-VLOOKUP((TRUNC(($H4),0)),V15:X20,3),0),IF('Employee List'!$D2="Fortnightly_Tax",ROUND((TRUNC(($H4/2),0)+0.99)*(VLOOKUP((TRUNC(($H4/2),0)),V15:X20,2))-VLOOKUP((TRUNC(($H4/2),0)),V15:X20,3),0)*2,IF('Employee List'!$D2="Monthly_Tax",ROUND((ROUND((TRUNC((3/13)*($H4+IF(ISNUMBER(SEARCH(".33",$H4)),0.01,0)),0)+0.99)*(VLOOKUP((TRUNC((3/13)*($H4+IF(ISNUMBER(SEARCH(".33",$H4)),0.01,0)),0)),V15:X20,2))-(VLOOKUP((TRUNC((3/13)*($H4+IF(ISNUMBER(SEARCH(".33",$H4)),0.01,0)),0)),V15:X20,3)),0)*(13/3)),0)))))

I went in and rewrote the Vlookup Table, removing the named range.

This is the Vlookup Table:
SCALE 1
$ a b

0 0.1900 0.1900
72 0.2342 3.2130
361 0.3477 44.2476
932 0.3450 41.7311
1,380 0.3900 103.8657
3,111 0.4700 352.7888

The coloured scale is taken from the original, with each column giving the correct answer.

Can anyone please tell me where my If formula went wrong.

Regards....George
 

Attachments

Top