excel
Sheet1
| Construction Supply Company | Change | 2.5 | ||||||||||||
| Inventory, in cases | ||||||||||||||
| Pylons | Bricks | Tarps | Insulation | Total | What if? | |||||||||
| Storage 1 | 67 | 65 | 48 | 2 | 47 | 617.5 | ||||||||
| Storage 2 | 39 | 53 | 57 | 62 | 211 | 0 | ||||||||
| Storage 3 | 50 | 44 | 69 | 33 | 196 | ERROR:#VALUE! | ||||||||
| Storage 4 | 89 | 56 | 217 | 152522.5 | ||||||||||
| Storage 5 | 38 | 42 | 51 | 184 | ||||||||||
| 227 | 293 | 300 | 235 | |||||||||||
| 30% drop | 158.9 | 205.1 | 210 | 164.5 | ||||||||||
| Average | 45.4 | 58.6 | 60 | |||||||||||
| Maximum | 247 | |||||||||||||
| Minimum |
CS_150_Excel Module 2 Assignment 51 Instructions x
CS 150
Excel Module 2 Assignment 5 Instructions
1. First, download the data file for this assignment (named EX 2-2.xlsx).
1. Download it by right clicking on it and then save it in your flash drive.
2. You start the assignment by opening this file and saving it with the name listed in one of the first steps.
1. Download the file EA 5 Instructions -Skills Review x..
1.
After
finishing all steps, upload and submit for grading the resulting Excel file in Blackboard by clicking the link (EA 5) to this assignment. You have only 1 attempt, hence, make sure it is the right file name and correct file content you upload and submit.
EA 5 Instructions -Skills Review x
Module 2 Skills Review
1. Create a complex formula.
a. Open EX 2-2.xlsx from the location where you store your Data Files, then save it as EX 2-Construction Supply Company Inventory.
b. Select the range B4:B8, click the Totals tab in the Quick Analysis tool, then click the AutoSum button.
c. Use the fill handle to copy the formula in cell B9 to cells C9:E9.
d. In cell B11, create a complex formula that calculates a 30% decrease in the total number of cases of pylons.
e. Use the fill handle to copy this formula into cell C11 through cell E11.
f. Save your work.
2. Insert a function.
a. Use the AutoSum list arrow to create a formula in cell B13 that averages the number of cases of pylons in each storage area.
b. Use the Insert Function button to create a formula in cell B14 that calculates the maximum number of cases of pylons in a storage area.
c. Use the AutoSum list arrow to create a formula in cell B15 that calculates the minimum number of cases of pylons in a storage area.
d. Save your work.
3. Type a function.
a. In cell C13, type a formula that includes a function to average the number of cases of bricks in each storage area. (Hint: Use Formula AutoComplete to enter the function.)
b. In cell C14, type a formula that includes a function to calculate the maximum number of cases of bricks in a storage area.
c. In cell C15, type a formula that includes a function to calculate the minimum number of cases of bricks in a storage area.
d. Save your work.
4. Copy and move cell entries.
a. Select the range B3:F3.
b. Copy the selection to the Clipboard.
c. Open the Clipboard task pane, then paste the selection into cell B17.
d. Close the Clipboard task pane, then select the range A4:A9.
e. Use the drag-and-drop method to copy the selection to cell A18. (Hint: The results should fill the range A18:A23.)
f. Save your work.
5. Understand relative and absolute cell references.
a. Write a brief description of the difference between relative and absolute references.
b. List at least three situations in which you think a business might use an absolute reference in its calculations. Examples can include calculations for different types of worksheets, such as time cards, invoices, and budgets.
6. Copy formulas with relative cell references.
a. Calculate the total in cell F4.
b. Use the Fill button to copy the formula in cell F4 down to cells F5:F8.
c. Select the range C13:C15.
d. Use the fill handle to copy these cells to the range D13:F15.
e. Save your work.
7. Copy formulas with absolute cell references.
a. In cell H1, change the existing value to 1.575.
b. In cell H4, create a formula that multiplies F4 and an absolute reference to cell H1.
c. Use the fill handle to copy the formula in cell H4 to cells H5 and H6.
d. Use the Copy and Paste buttons to copy the formula in cell H4 to cells H7 and H8.
e. Change the amount in cell H1 to 2.5.
f. Save your work.
8. Round a value with a function.
a. Click cell H4.
b. Edit this formula to include the ROUND function showing zero decimal places.
c. Use the fill handle to copy the formula in cell H4 to the range H5:H8.
d. Enter your name in cell A25, then compare your work to
Figure 2-23
.
Figure 2-23
e. Save your work, preview the worksheet in Backstage view, then submit your work to your instructor as directed.
f. Close the workbook, then exit Excel.
2
EX 2-2.xlsx
Sheet1
Construction Supply Company Change 1.1
Inventory, in cases
Pylons Bricks Tarps Insulation Total What if?
Storage 1 67 65 67 48
Storage 2 39 53 57 62
Storage 3 50 44 69 33
Storage 4 33 89 56 39
Storage 5 38 42 51 53
Total
30% drop
Average
Maximum
Minimum