The amounts in columns H, J, L, N, and 0 should be computed using formulas. FICA is computed as 7.65% of gross pay. Local Taxes are 1.75% of gross pay. The formula for gross pay in column H must correctly calculate pay whether or not the employee worked any overtime. (Hint: that means you need an =IF function.) Overtime is paid at 1.5 times the regular wage rate for any hours worked over 40.

Next, you are going to use Excels vertical lookup function to determine the amounts in columns I and K (federal and state taxes). Below is a payroll tax table. You can type the table or copy and paste it from the Payroll Tax Tables file found in your elearn course. To find the tax amounts for each employee, multiply their weekly gross pay by the tax % associated with their pay from the table.

Weekly Gross Pay Range-From:	To:	Federal Tax %	State Tax %
$0	$100	0%	0%
$101	$200	5.00%	1.00%
$201	$300	7.00%	1.25%
$301	$400	8.00%	1.50%
$401	$500	9.00%	1.75%
$501	$600	10.00%	2.00%
$601	$700	11.00%	2.25%
$701	$800	12.00%	2.50%
$801	$900	13.00%	2.75%
$901	$1000	14.00%	3.00%
$1001	Above $1001	15.00%	3.25%

In column P, display the gross pay of any employee that is in the sales department and show a "0" for anyone in the administrative department. Do the opposite in column Q. Enter formulas to add up columns H through Q. Also put in a formula to verify that the total of columns P & Q equals the total of the gross pay column.

A D E F G H | J K L M N MI 1 EMPLOYEE LIST 2 Last First 3 Name Name 4 Arkin Joan E 5 Burns Alan P 6 Dawson Loren M 7 Dortley George W 8 Green Amanda L 9 Harris Susan T 10 Jacobson Aaron J 11 Shaeffer Robert P 12 Taylor Jeanne S 13 Young Elizabeth Y 14 15 Hourly Misc. S.S. # Dept. Wage Ra Deduct. 234-66-98 Admin 12.5 17 345-78-99. Sales 16 17 452-99-87 Admin 9.75 0 229-46-90. Admin 11.75 8.75 340-55-66 Sales 9 8.75 987-00-77. Sales 18.25 17 345-90-77 Sales 6.75 0 567-89-02 Sales 11.25 17 342-99-77: Sales 7.5 0 996-71-34: Admin 6.5 0 16 17 18 19 20 D E F G H 1 K L M N P Q R Federal FICA Taxes State Taxes Local Taxes Misc. Total Net Deduct. Deduct. Pay 17 Sales Admin. Payroll Payroll 17 0 A B C 1 PAYROLL REGISTER 2 3 Last First 4 Name Name MI 5 Arkin Joan E 6 Burns Alan P 7 Dawson Loren M 8 Dortley George W 9 Green Amanda L 10 Harris Susan T 11 Jacobson Aaron J 12 Shaeffer Robert P 13 Taylor Jeanne S 14 Young Elizabeth Y 15 8.75 Hourly S.S. # Dept. 234-66-98 Admin 345-78-99. Sales 452-99-87 Admin 229-46-90. Admin 340-55-66: Sales 987-00-77. Sales 345-90-77 Sales 567-89-02 Sales 342-99-77: Sales 996-71-34: Admin Hours Gross Wage Ral Worked Pay 12.5 48 16 41 9.75 35 11.75 34 9 34 18.25 42 6.75 24 11.25 39 7.5 22 6.5 18.5 8.75 17 0 17 0 0 16 17 18 19 20 A F G H 1 4 6 in oo Oy 7 B D E Weekly Gross Pay Range- From: To: Federal Tax % State Tax % $0 $100 0.00% 0.00% $101 $200 5.00% 1.00% $201 $300 7.00% 1.25% $301 $400 8.00% 1.50% $401 $500 9.00% 1.75% $501 $600 10.00% 2.00% $601 $700 11.00% 2.25% $701 $800 12.00% 2.50% $801 $900 13.00% 2.75% $901 $1,000 14.00% 3.00% $1,001 Above $1001 15.00% 3.25% 10 0 11 12 13 14 15 16 17 18 19 20 21 22 A D E F G H | J K L M N MI 1 EMPLOYEE LIST 2 Last First 3 Name Name 4 Arkin Joan E 5 Burns Alan P 6 Dawson Loren M 7 Dortley George W 8 Green Amanda L 9 Harris Susan T 10 Jacobson Aaron J 11 Shaeffer Robert P 12 Taylor Jeanne S 13 Young Elizabeth Y 14 15 Hourly Misc. S.S. # Dept. Wage Ra Deduct. 234-66-98 Admin 12.5 17 345-78-99. Sales 16 17 452-99-87 Admin 9.75 0 229-46-90. Admin 11.75 8.75 340-55-66 Sales 9 8.75 987-00-77. Sales 18.25 17 345-90-77 Sales 6.75 0 567-89-02 Sales 11.25 17 342-99-77: Sales 7.5 0 996-71-34: Admin 6.5 0 16 17 18 19 20 D E F G H 1 K L M N P Q R Federal FICA Taxes State Taxes Local Taxes Misc. Total Net Deduct. Deduct. Pay 17 Sales Admin. Payroll Payroll 17 0 A B C 1 PAYROLL REGISTER 2 3 Last First 4 Name Name MI 5 Arkin Joan E 6 Burns Alan P 7 Dawson Loren M 8 Dortley George W 9 Green Amanda L 10 Harris Susan T 11 Jacobson Aaron J 12 Shaeffer Robert P 13 Taylor Jeanne S 14 Young Elizabeth Y 15 8.75 Hourly S.S. # Dept. 234-66-98 Admin 345-78-99. Sales 452-99-87 Admin 229-46-90. Admin 340-55-66: Sales 987-00-77. Sales 345-90-77 Sales 567-89-02 Sales 342-99-77: Sales 996-71-34: Admin Hours Gross Wage Ral Worked Pay 12.5 48 16 41 9.75 35 11.75 34 9 34 18.25 42 6.75 24 11.25 39 7.5 22 6.5 18.5 8.75 17 0 17 0 0 16 17 18 19 20 A F G H 1 4 6 in oo Oy 7 B D E Weekly Gross Pay Range- From: To: Federal Tax % State Tax % $0 $100 0.00% 0.00% $101 $200 5.00% 1.00% $201 $300 7.00% 1.25% $301 $400 8.00% 1.50% $401 $500 9.00% 1.75% $501 $600 10.00% 2.00% $601 $700 11.00% 2.25% $701 $800 12.00% 2.50% $801 $900 13.00% 2.75% $901 $1,000 14.00% 3.00% $1,001 Above $1001 15.00% 3.25% 10 0 11 12 13 14 15 16 17 18 19 20 21 22