Exercise 4 -
Additional FUNCTIONS – Minimum, Maximum and Count
Custom Draperies is interested in calculating the total sales per salesperson, average total sales, lowest total sales, highest total sales and total number of salespersons.
Instructions: 1. Enter the spreadsheet values and labels using cells A1:D15. Format the values and labels as shown. Select a variety of font styles and sizes. A
4 5 6 7 8 9 10 11 12 13 14 15
C
D
Custom Draperies Ltd
1 2 3
B
Name of Salesperson Justice, Elsa Kingston, Daine Mangrum, Darryl Oliphant, Long Reynolds, Tony Lum, Minh Omar, Gohbi
Yards Sold 100 150 200 250 300 350 400
Unit Price
Total Sales
$22.50 $20.50 $18.50 $16.50 $14.50 $12.50 $10.50
Average Total Sales Lowest Total Sales Highest Total Sales Total # of Salespeople
2. Add borders to the spreadsheet. The outside border should differ from the inside borders.
3. In cell D4, add a formula to multiply Yards Sold by Unit Price for the first salesperson. Set both cell addresses as relative [=B4*C4]. Copy the formula down. In cell D12, use the average function to average the Total Sales in cells D4 through D10 In cell D13, use the min function to show the Lowest Total Sales Amount for cells D4 through D10. Use the arrow down button beside the AutoSum command to show the lowest sales amount. In cell D14, use the max function to show the Highest Total Sales Amount for cells D4 through D10. Use the arrow down button beside the AutoSum command to show the highest sales amount. In cell D15, use the count function to show the Total Number of Salespeople working at Custom Draperies Ltd. The count function requires values in the cells that you wish to count. Use the arrow down button beside the AutoSum command to show the highest sales amount for cells D4 through D10. If ########’s fill the column that means that you need to increase the column width to show the values and the formatting. 4. Add your drawing toolbar using View menu, toolbars to draw an arrow from D14 to the name of the salesperson that achieved the highest sales amount.
5. Save the spreadsheet as ex04 on your home drive. Add a footer