Excel Assignment 1 PDF

Preview

Summary

excel...

Description

Excel Problem #1 Learning VLookUP and Validation Controls 10 minutes prior to the start of class 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” and create the spreadsheet illustrated in Exhibit 6. a. Create the VLookUp Bonus Calculation Table on one worksheet and name your worksheet VLookUP b. Create the Bonus Table on another worksheet and name this worksheet Bonus c. In the column “Bonus” – use the VLookUp function to calculate the amount of bonus i. =vlookup(lookup_value, table_array,col_index_num,range_lookup) 3. Download the article Block that Spreadsheet Error Read the article, especially the section “Raising the Flag” 4. Add the following data validation controls to your spreadsheet, including explanatory error messages. a. Sales must be positive in the bonus calculation table b. Sales cannot exceed 125 in the bonus calculation table c. Amount of bonus must be nonnegative in the bonus table d. Amount of bonus cannot exceed 20% of unit sales in the bonus table 5. As presentation is always important to success of anything that you do, format the presentation of your spreadsheet so that it looks professional, neat and appealing (i.e. add boarders, colors, shading, headings, remove cell lines, etc.) 6. Save your spreadsheet and call it “yourlastnameExcel1” 7. Upload your completed spreadsheet into EMU Online by 10 minutes prior to the start of class: 8. The 15 points for this assignment will be allocated as follows: a. Correct Answer 2 b. Correct use of VLookUp Function 3 c. Correct use of Validation Tools 3 d. Error message with Validation tools 2 e. Presentation 2 f. Use of two worksheets 2 g. Correct Name 1...