T
Trish
Hi, I posted a question last week and M Kan was helpful. However, I asked
further questions and this person has not responded. Can anyone else help
me? ..... all the previous stuff is below ...
Thanks again, M Kan. I am still very confused ... sorry! I still can't
get my head around these lookup tables. Using my own cell references, this
is what I have ...
a1 Name Hours worked Hourly rate Gross Pay Less Tax
a2 Adam Green 40 14.6 584
a3 Kelly Kong 38 10.45 397.1
a4 Harry Schmidt 31 10.45 323.95
a5 Lily Chan 38 17.5 665
a6 John Van Don 40 23.5 940
a7 Olive Bliss 40 37.5 1500
THE LOOKUP FUNCTION NEEDS TO GO IN THE GROSS PAY, IE CELL D2
a12 Pay Band Withold Amt
a13 0 0 0
a14 95 20% 0
a15 346 25% 63
a16 481 40% 96
a17 673 47% 183
a18 962 48% 308
I need to use a lookup function to calculate the tax. Please could you
assist me again, using my own cell references. Your assistance is very much
appreciated. By the way, the "Notify me of replies" does not appear to be
working, as I was hoping for a response ... luckily I checked the site again
.... there you were! Thanks. Trish
--
Trish
further questions and this person has not responded. Can anyone else help
me? ..... all the previous stuff is below ...
Thanks again, M Kan. I am still very confused ... sorry! I still can't
get my head around these lookup tables. Using my own cell references, this
is what I have ...
a1 Name Hours worked Hourly rate Gross Pay Less Tax
a2 Adam Green 40 14.6 584
a3 Kelly Kong 38 10.45 397.1
a4 Harry Schmidt 31 10.45 323.95
a5 Lily Chan 38 17.5 665
a6 John Van Don 40 23.5 940
a7 Olive Bliss 40 37.5 1500
THE LOOKUP FUNCTION NEEDS TO GO IN THE GROSS PAY, IE CELL D2
a12 Pay Band Withold Amt
a13 0 0 0
a14 95 20% 0
a15 346 25% 63
a16 481 40% 96
a17 673 47% 183
a18 962 48% 308
I need to use a lookup function to calculate the tax. Please could you
assist me again, using my own cell references. Your assistance is very much
appreciated. By the way, the "Notify me of replies" does not appear to be
working, as I was hoping for a response ... luckily I checked the site again
.... there you were! Thanks. Trish
--
Trish
Was this post helpful to you?M Kan said:This piece looks up the base amount (e.g., the $63
=VLOOKUP(K22,Pay_table,3)
+(K22-J17)*VLOOKUP(K22,Pay_table,2)
This piece takes the difference between the actual amount and the floor
(e.g., 73-63 =10 and then multiplies the difference by the withholding
percentage (e.g., 25%)