Use a vlookup in I5 to identify and calculate the federal whthholding tax use the tax rate D21:E25. Vlookup function returns the applicable tax rate which you must then multiply by the taxable pay.

Metropolitan Zoo Gift Shop Department No. of Hourly Hours Dependents Wage Worked Regular Pay Pay Overtime Federal Net P $9.95 48 $9.55 48 $11.75 50 11.75 35 S10.00 40 $9.55 44 $15.75 40 $12.25 20 $13.35 38 S10.00 45 $9.95 15 $11.75 41 5174 S 417.40 496.6 S 446.60 6 Acosta 382 1146 470 176.25 646.25 S 496.25 411.25 411 25 S361.25 400 S350.00 439.3 S 389.30 630 $ 480.00 245 S 145.00 507.3 S 357.30 475 S 375.00 99.25 487 625 S 387 63 9 lanziti 10 Jauss 57.3 630 245 507.3 13 Riley 14 Sabey 149.25 470 149 25 S 16 We 17 Totals 17 625 Pay T 21 Base Work Hours 23 FICA rate 24 Deduct per Depend S Highest Lowest $250 5300 S450 S525 22% 25% 28% 31% 1.5 7 66% 5000 k is 40 hours Regular pay is based on hourly wage and hours worked up to 40 29 2. Overtime pay is based on overtime hours, the hourly wage, and the overtime rate 0 3 The gross pay is the total of the regular pay and overtime pay 4. The taxable pay is the difference between the gross pay | and the product of the number of dependents and deduction per dependent on the taxable pay based on the tax table and FICA