{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

V_Lookup(1)

# Write a lookup formula using the vlookup function to

This preview shows page 1. Sign up to view the full content.

This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: function to retrieve the proper bonus amount. Change sales values, and ensure that the expected bonus amount is computed. Now, delete the values in the To column and ensure the lookup formula continues to work as expected. Bonus Calculation Sales Bonus \$- Bonus Table From \$\$10,000 \$50,000 \$100,000 \$200,000 \$500,000 To Bonus \$9,999 \$49,999 \$99,999 \$199,999 \$499,999 \$999,999 \$\$1,000 \$2,500 \$5,000 \$7,250 \$10,000 Exercise 8 Please perform the following: Convert the Fiscal Quarters range into a Table named tbl_qtrs. Write a lookup formula in the Quarter column of the Transactions listing that retrieves the proper fiscal quarter from tbl_qtrs. Fiscal Quarters Start Qtr 7/1/2013 Q1 10/1/2013 Q2 1/1/2014 Q3 4/1/2014 Q4 Transactions RepID GSL TPS GSL DMK BYG JUB GSL DMK BEO DMK GSL BYG BYG DMK GSL TPS BEO JUB DMK TPS BEO BEO GSL BYG Date 6/17/2014 8/8/2013 6/10/2014 10/19/2013 3/28/2014 5/14/2014 6/15/2014 5/9/2014 7/27/2013 8/11/2013 12/28/2013 4/6/2014 5/13/2014 1/11/2014 8/24/2013 10/15/2013 3/27/2014 7/27/2013 8/28/2013 9/17/2013 6/27/2014 12/11/2013 10/28/2013 9/19/2013 Amount 16,964 596 18,355 15,937 8,242 16,068 221 13,422 10,775 15,759 10,880 17,073 16,373 3,933 9,643 5,662 13,047 19,527 19,190 15,604 17,208 9,662 12,089 2,125 Quarter Exercise 9: HLOOKUP Please perform the following: Write a lookup formula in the Shipping column of the chart that retrieves the proper Zone percentage. Data ORDER SUMMARY Order ID Subtotal 1 \$100.00 2 \$200.00 3 \$300.00 4 \$200.00 5 \$300.00 SHIPPING RATES Zone North 5% Region West North South West South South 4% Shipping East 2% Total \$100.00 \$200.00 \$300.00 \$200.00 \$300.00 West 3% On Your Own Please perform the following: Write a formula in the Marginal tax rate cell below to retrieve the marginal tax rate for a given taxable income value. Settings Taxable Income \$50,000 Results Marginal tax rate Tax Table - Single - 2012 Income Marginal Rate \$\$8,701 \$35,351 \$85,651 \$178,651 \$388,351 10% 15% 25% 28% 33% 35% On Your Own 2 Please perform the following: Using the Tax Table data below, (tbl_tx_tbl) use a lookup formula to populate the Less: Income not in bracket cell. Use a lookup formula to populate the Marginal rate cell. Use a l...
View Full Document

{[ snackBarMessage ]}

Ask a homework question - tutors are online