Task

In this lesson we will utilize the 5.04 Worksheet you used when creating functions. You are going to modify the spreadsheet by inserting new columns and you will be able to see how the formulas adjusted to reflect the new column cell reference names. Instead of keying the formulas like we did in the previous lesson, you will use the AutoSum button to help your create the formula functions. This feature will help you simplify creating your worksheet. Excel offers many features like these to help make creating worksheets easy. You will still be using the following functions, but remember, you are going to use the AutoSum button.

  • SUM — to total a range of cells
  • AVERAGE— to average a range of cells
  • COUNT— to determine how many items are in a column or row
  • MAX — to determine the largest number in a row or column
  • MIN — to determine the smallest number in a row or column

You will be adding to the spreadsheet you just submitted for the 5.04 Functions Dropbox Assignment. Complete these instructions. Submit your completed worksheet to the 5.05 AutoSum Dropbox.

Note: You will use this file again, so make sure to complete this assignment and save it for later use!



→

Imagine that you are currently employeed by a retail store and your salary is commission based. Commission is a salary paid to an employee(s) based on a percentage of their weekly or monthly sales. Jobs that pay employees a commission are some retail jobs, car salesmen, insurance salesmen, and stockbrokers to name a few. In this worksheet the employees will be paid a 10% commission on their weekly sales. Please enter the following data into the specified cells.

  • Cell A1— Employee Commission
  • Cell A3 — Rate
  • Cell B3 — 10%
  • Cell A5— Employee
  • Cell B5 — Sales
  • Cell C5 — Commission
  • Cell A6— John
  • Cell A7 —Paul
  • Cell A8—Susan
  • Cell A9— Mary
  • Cell A10— Tyler
  • Cell B6— $4,325.50
  • Cell B7— $3,250.75
  • Cell B8— $2,670.00
  • Cell B9— $5,350.30
  • Cell B10—$4,133.06

Now you will need to create a formula in cell C6 that multiples the sales amount listed for each employee by the commission rate. Make sure the cell reference that contains the data for the commission rate is an absolute cell reference. You can type the formula, highlight the cell reference that should be the absolute cell reference and hold F4. Pressing F4 on the highlight cell reference will automatically place the dollar signs $ in the appropriate place. If you do not want to create the formula that way then as you key the cell reference that should be absolute enter the $ before the column name and again before the row name.

An example of a similar formula would be =A2*$B$2 (not the formula for this problem just an example).

Once you have created the formula, use the fill handle to copy the formula down the cells in the Commission column. Click on one of the cells that contains the copied formula and make sure the absolute cell reference has stayed the same. Make sure you formate your Sales and Commission columns to have a comma in the numbers and two decimal places.

Submit your completed worksheet to the 5.05 Commission Dropbox.



←

 

 

""