A. In cell E13, retrieve the income limit from the appropriate tax table. Use a nested IF function with Vlookup inside the IF functions to retrieve the first column from the appropriate tax rate table. The formula is identical to the formula inside the Marginal Tax Rate cell (D13) except for retrieving the first column. Extend the formula to cells E14:E17. Format the cells as Currency with zero decimal digits.

B.. In cell F13, retrieve the base tax from the appropriate tax table. Use a nested IF function with Vlookup inside the IF functions to retrieve the third column from the appropriate tax rate table. The formula is identical to the formula inside the Marginal Tax Rate cells except for retrieving the third column. Extend the formula to cells F14:F17. Format the cells as Currency with zero decimal digits.

C.. In cell G13, compute the tax amount as the base tax amount plus the marginal tax rate times the marginal income (income greater than income limit). The marginal income is the income minus the income limit. Extend thex to cells G14:G17. Format the cells as Currency with zero decimal digits.

Hint: Use the existing Vlookup function from Column D and change it a little for columns E and F.

Allgen CULTY Allalysis D13 fr =IF(B13=A$2,VLOOKUP(C13,$A$3:$C$9,2,TRUE),IF(B13=E$2,VLOOKUP(C13,$E$3:$G$9,2,TRUE),IF(B13=I$2,VLOOKUP(C13,$I$3:$K$9,2,TRUE), VLOOKUP(C13,$N$3:$P$9,2,TRUE)))) D H Q R S E F G Tax Table for Head of Household HH Tax rate Base Tax $0 10% SO $13,851 12% $1,385 $52,851 22% $6,065 $84,201 24% $12,962 $160,701 32% $31,322 $204,001 35% $45,210 $510,301 37% $152,380 1 K L Tax Table for Married Filing Jointly MFJ Tax rate Base Tax $0 10% SO $19,401 12% $1,904 $78,952 22% $9,086 $168,401 24% $28,765 $321,451 32% $65,497 $408,201 35% $93,257 $612,351 37% $164,709 M . N Tax Table for Married Filing Separately MES Tax rate Base Tax $0 10% $0 $9,701 12% $970 $39,476 22% $4,543 $84,201 24% $14,382 $160,726 32% $32,748 $204,101 35% $46,628 $306,176 37% $82,355 Income Limit Base Tax Tax Amount A B 1 Tax Table for Single 2 S Tax rate Base Tax 3 $0 10% $0 4 $9,701 12% $970 5 $39,476 22% $4,543 6 $84,201 24% $14,382 7 $160,726 32% $32,748 8 $204,101 35% $46,628 9 $510,301 37% $153,796 10 11 12 Taxpayer Id Filing status Income 13 111223333 S $165,899 14 222334444 HH $85,444 15 222445555 MFS $401,550 16 333556666 MFJ $15,010 17 444556666 S $160,726 18 19 20 21 22 23 24 25 26 Marginal Tax Rate 32% 24% 37% 10% 32% Allgen CULTY Allalysis D13 fr =IF(B13=A$2,VLOOKUP(C13,$A$3:$C$9,2,TRUE),IF(B13=E$2,VLOOKUP(C13,$E$3:$G$9,2,TRUE),IF(B13=I$2,VLOOKUP(C13,$I$3:$K$9,2,TRUE), VLOOKUP(C13,$N$3:$P$9,2,TRUE)))) D H Q R S E F G Tax Table for Head of Household HH Tax rate Base Tax $0 10% SO $13,851 12% $1,385 $52,851 22% $6,065 $84,201 24% $12,962 $160,701 32% $31,322 $204,001 35% $45,210 $510,301 37% $152,380 1 K L Tax Table for Married Filing Jointly MFJ Tax rate Base Tax $0 10% SO $19,401 12% $1,904 $78,952 22% $9,086 $168,401 24% $28,765 $321,451 32% $65,497 $408,201 35% $93,257 $612,351 37% $164,709 M . N Tax Table for Married Filing Separately MES Tax rate Base Tax $0 10% $0 $9,701 12% $970 $39,476 22% $4,543 $84,201 24% $14,382 $160,726 32% $32,748 $204,101 35% $46,628 $306,176 37% $82,355 Income Limit Base Tax Tax Amount A B 1 Tax Table for Single 2 S Tax rate Base Tax 3 $0 10% $0 4 $9,701 12% $970 5 $39,476 22% $4,543 6 $84,201 24% $14,382 7 $160,726 32% $32,748 8 $204,101 35% $46,628 9 $510,301 37% $153,796 10 11 12 Taxpayer Id Filing status Income 13 111223333 S $165,899 14 222334444 HH $85,444 15 222445555 MFS $401,550 16 333556666 MFJ $15,010 17 444556666 S $160,726 18 19 20 21 22 23 24 25 26 Marginal Tax Rate 32% 24% 37% 10% 32%