| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2007 Lookup
Hi, I am working on a spreadsheet to do with payroll and would like to know
how I set up the look up function to cater for the following: If pay is between $0 and $95 then deduct $0 plus $0 $96 and $345 then $0 plus 20 cents for every dollar over $96 $346 and $480 then $63 plus 25 cents for every dollar over $346 $481 and $672 then $96 plus 40 cents for every dollar over $481 $673 and $961 then $183 plus 47 cents for every dollar over $673 $962 and over then $308 plus 48 cents for every dollar over $96 I tried to do a lookup function along the following lines, but got myself in a mess, plus I don't know how to calculate the latter "plus" part. <=95 $0 95<=345 $0 + 20 cents for every dollar over $96 345<=480 $63 + 25 cents for every dollar over $346 480<=672 $96 + 40 cents for every dollar over $481 672<=961 $183 + 47 cents for every dollar over $673 962 $308 + 20 cents for every dollar over $962 Sorry to ask what is probably quite obvious to most! I look forward to a response. Many thanks. :-) -- Trish |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2007 Lookup
I set up a table like this:
Pay Band Withold Amt 0 0 0 95 20% 0 346 25% 63 481 40% 96 673 47% 183 962 48% 308 Pay Band is cell J14. The formula looks like this: =VLOOKUP(K22,Pay_table,3)+(K22-J17)*VLOOKUP(K22,Pay_table,2) -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Trish" wrote: Hi, I am working on a spreadsheet to do with payroll and would like to know how I set up the look up function to cater for the following: If pay is between $0 and $95 then deduct $0 plus $0 $96 and $345 then $0 plus 20 cents for every dollar over $96 $346 and $480 then $63 plus 25 cents for every dollar over $346 $481 and $672 then $96 plus 40 cents for every dollar over $481 $673 and $961 then $183 plus 47 cents for every dollar over $673 $962 and over then $308 plus 48 cents for every dollar over $96 I tried to do a lookup function along the following lines, but got myself in a mess, plus I don't know how to calculate the latter "plus" part. <=95 $0 95<=345 $0 + 20 cents for every dollar over $96 345<=480 $63 + 25 cents for every dollar over $346 480<=672 $96 + 40 cents for every dollar over $481 672<=961 $183 + 47 cents for every dollar over $673 962 $308 + 20 cents for every dollar over $962 Sorry to ask what is probably quite obvious to most! I look forward to a response. Many thanks. :-) -- Trish |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2007 Lookup
Thanks M Kan. That helps, but how do I factor in the additional xx cents per
$ over, ie the second half of the lines? Thanks again :-) -- Trish "M Kan" wrote: I set up a table like this: Pay Band Withold Amt 0 0 0 95 20% 0 346 25% 63 481 40% 96 673 47% 183 962 48% 308 Pay Band is cell J14. The formula looks like this: =VLOOKUP(K22,Pay_table,3)+(K22-J17)*VLOOKUP(K22,Pay_table,2) -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Trish" wrote: Hi, I am working on a spreadsheet to do with payroll and would like to know how I set up the look up function to cater for the following: If pay is between $0 and $95 then deduct $0 plus $0 $96 and $345 then $0 plus 20 cents for every dollar over $96 $346 and $480 then $63 plus 25 cents for every dollar over $346 $481 and $672 then $96 plus 40 cents for every dollar over $481 $673 and $961 then $183 plus 47 cents for every dollar over $673 $962 and over then $308 plus 48 cents for every dollar over $96 I tried to do a lookup function along the following lines, but got myself in a mess, plus I don't know how to calculate the latter "plus" part. <=95 $0 95<=345 $0 + 20 cents for every dollar over $96 345<=480 $63 + 25 cents for every dollar over $346 480<=672 $96 + 40 cents for every dollar over $481 672<=961 $183 + 47 cents for every dollar over $673 962 $308 + 20 cents for every dollar over $962 Sorry to ask what is probably quite obvious to most! I look forward to a response. Many thanks. :-) -- Trish |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2007 Lookup
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%) -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Trish" wrote: Thanks M Kan. That helps, but how do I factor in the additional xx cents per $ over, ie the second half of the lines? Thanks again :-) -- Trish "M Kan" wrote: I set up a table like this: Pay Band Withold Amt 0 0 0 95 20% 0 346 25% 63 481 40% 96 673 47% 183 962 48% 308 Pay Band is cell J14. The formula looks like this: =VLOOKUP(K22,Pay_table,3)+(K22-J17)*VLOOKUP(K22,Pay_table,2) -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Trish" wrote: Hi, I am working on a spreadsheet to do with payroll and would like to know how I set up the look up function to cater for the following: If pay is between $0 and $95 then deduct $0 plus $0 $96 and $345 then $0 plus 20 cents for every dollar over $96 $346 and $480 then $63 plus 25 cents for every dollar over $346 $481 and $672 then $96 plus 40 cents for every dollar over $481 $673 and $961 then $183 plus 47 cents for every dollar over $673 $962 and over then $308 plus 48 cents for every dollar over $96 I tried to do a lookup function along the following lines, but got myself in a mess, plus I don't know how to calculate the latter "plus" part. <=95 $0 95<=345 $0 + 20 cents for every dollar over $96 345<=480 $63 + 25 cents for every dollar over $346 480<=672 $96 + 40 cents for every dollar over $481 672<=961 $183 + 47 cents for every dollar over $673 962 $308 + 20 cents for every dollar over $962 Sorry to ask what is probably quite obvious to most! I look forward to a response. Many thanks. :-) -- Trish |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2007 Lookup
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 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 "M Kan" wrote: 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%) -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Trish" wrote: Thanks M Kan. That helps, but how do I factor in the additional xx cents per $ over, ie the second half of the lines? Thanks again :-) -- Trish "M Kan" wrote: I set up a table like this: Pay Band Withold Amt 0 0 0 95 20% 0 346 25% 63 481 40% 96 673 47% 183 962 48% 308 Pay Band is cell J14. The formula looks like this: =VLOOKUP(K22,Pay_table,3)+(K22-J17)*VLOOKUP(K22,Pay_table,2) -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "Trish" wrote: Hi, I am working on a spreadsheet to do with payroll and would like to know how I set up the look up function to cater for the following: If pay is between $0 and $95 then deduct $0 plus $0 $96 and $345 then $0 plus 20 cents for every dollar over $96 $346 and $480 then $63 plus 25 cents for every dollar over $346 $481 and $672 then $96 plus 40 cents for every dollar over $481 $673 and $961 then $183 plus 47 cents for every dollar over $673 $962 and over then $308 plus 48 cents for every dollar over $96 I tried to do a lookup function along the following lines, but got myself in a mess, plus I don't know how to calculate the latter "plus" part. <=95 $0 95<=345 $0 + 20 cents for every dollar over $96 345<=480 $63 + 25 cents for every dollar over $346 480<=672 $96 + 40 cents for every dollar over $481 672<=961 $183 + 47 cents for every dollar over $673 962 $308 + 20 cents for every dollar over $962 Sorry to ask what is probably quite obvious to most! I look forward to a response. Many thanks. :-) -- Trish |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pasting a chart from Excel 2007 to Word 2007 trouble | Charts and Charting in Excel | |||
| Excel 2007 Macro Help (Excel 2003 not working in 2007) | Excel Discussion (Misc queries) | |||
| Excel 2007 LOOKUP Problem? | Excel Worksheet Functions | |||
| Paste EXCEL 2007 sheet into WORD 2007 - objects move around | Excel Discussion (Misc queries) | |||
| Chart Selection - Excel 2007 copy to Powerpoint 2007 | Charts and Charting in Excel | |||
Linear Mode
