Excel Skills: VLookUp, Validation Controls 1. Download the article Make Excel a Little Smarter from EMU...

Transcribed Image Text:

Excel Skills: VLookUp, Validation Controls 1. Download the article "Make Excel a Little Smarter from EMU Online 2. Read the section titled "Data in Different Places" a. Create the Bonus Table below on one worksheet and name this worksheet "Salary Table". 25 File Paste 19 1 2 WN 3 4567 CODIN345 8 9 10 11 12 13 14 15 Home Insert Page Layout Formulas Data Cut 11A A Copy- Format Painter Clipboard B Calibri BIU-BA- Thomas LT Lois Jill Nicole Jenna Salesperson Sales Autumn McKenzie Skylar Caitlin Kimberly Salary Table sssss $ 2,200 6,300 1,110 9,900 $ Font $ C ss 1,110 4,200 2,121 8,900 6,890 6,850 8,000 D Bonus Review Bonus Table Sheet3 Sheet2 * E She b. Create the Table below on another worksheet and name this worksheet "Bonus Table". Paste L7 1 2 3 4 5 6 7 BOSCH 23 9 Home Insert Page Layout Formulas Data Review View X Cut -11-AA BIU---A-E 11 10 Clipboard 12 Copy Format Painter 13 A . $ SSSSSSS $ $ $ $ 5 Calibri X ✓ B Sales Font fr Amount of Bonus $ 2,000 $ 5,000 $ 6,000 $ 7,000 $ 8,000 $ 9,000 $ 10,000 $ 50.00 100.00 500.00 700.00 1,000.00 1,400.00 1,900.00 Aligome Salary Table Bonus Table Sheet3 Sheet2 Sheet4 c. In the column "Bonus" on the Salary Table tab-use the VLookUp function to calculate the amount of bonus each employee should receive. i. -vlookup(lookup_value, table_array,col_index_num,range_lookup) Download the article Block that Spreadsheet Error Read the article, especially the section "Raising the Flag" Add the following data validation controls to your spreadsheets, including explanatory input and error messages. a. Sales on the Salary Table tab must be positive and cannot excel $10,000 b. Amount of bonus on the Bonus Table tab must be nonnegative and cannot exceed 20% of the sales on that same table. Excel Skills: VLookUp, Validation Controls 1. Download the article "Make Excel a Little Smarter from EMU Online 2. Read the section titled "Data in Different Places" a. Create the Bonus Table below on one worksheet and name this worksheet "Salary Table". 25 File Paste 19 1 2 WN 3 4567 CODIN345 8 9 10 11 12 13 14 15 Home Insert Page Layout Formulas Data Cut 11A A Copy- Format Painter Clipboard B Calibri BIU-BA- Thomas LT Lois Jill Nicole Jenna Salesperson Sales Autumn McKenzie Skylar Caitlin Kimberly Salary Table sssss $ 2,200 6,300 1,110 9,900 $ Font $ C ss 1,110 4,200 2,121 8,900 6,890 6,850 8,000 D Bonus Review Bonus Table Sheet3 Sheet2 * E She b. Create the Table below on another worksheet and name this worksheet "Bonus Table". Paste L7 1 2 3 4 5 6 7 BOSCH 23 9 Home Insert Page Layout Formulas Data Review View X Cut -11-AA BIU---A-E 11 10 Clipboard 12 Copy Format Painter 13 A . $ SSSSSSS $ $ $ $ 5 Calibri X ✓ B Sales Font fr Amount of Bonus $ 2,000 $ 5,000 $ 6,000 $ 7,000 $ 8,000 $ 9,000 $ 10,000 $ 50.00 100.00 500.00 700.00 1,000.00 1,400.00 1,900.00 Aligome Salary Table Bonus Table Sheet3 Sheet2 Sheet4 c. In the column "Bonus" on the Salary Table tab-use the VLookUp function to calculate the amount of bonus each employee should receive. i. -vlookup(lookup_value, table_array,col_index_num,range_lookup) Download the article Block that Spreadsheet Error Read the article, especially the section "Raising the Flag" Add the following data validation controls to your spreadsheets, including explanatory input and error messages. a. Sales on the Salary Table tab must be positive and cannot excel $10,000 b. Amount of bonus on the Bonus Table tab must be nonnegative and cannot exceed 20% of the sales on that same table.

Answer rating: 100% (QA)

Formula is Text format Sales Person Sales Bonus Autumn 220... View the full answer