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
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