Sunday, December 26, 2021

TEACHING EXCEL FOR DATA ANALYSIS: HANDS ON 11 Getting started with formulas

 You have been asked to complete a pricing sheet that incorporates packaging costs and discounts, but you notice that some of the formula are missing.

1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.

https://github.com/alcadelina/Excel-

https://github.com/alcadelina/Excel-

2. Note that the formula to calculate the subtotal in cell E6 is missing:

Excel workbook open, Cell E6 selected, Formula field empty

3. Enter the formula “=B6+C6*D6” in cell E6, then press Enter:

Excel workbook open, Formula field typed "=B6+C6*D6"

4. Notice that the result, $21.99, does not accurately reflect the total value of the three items. According to the precedence of the order of operations, the formula first calculates the multiplication of cell C6 * D6 (3 * 3 = 9), then adds the result to the value of cell B6 (12.99 + 9 = 21.99). To correctly calculate the subtotal, insert parentheses around the operation you want to calculate first, “=(B6+C6)*D6”, then press Enter:

Workbook open, Cell E6 selected $47.97 result appears in the field, formula field typed “=(B6+C6)*D6"

5. Now that you have corrected the formula, select cell E6, press Ctrl + C to copy the formula, then select cells E7 through E9 and press Ctrl + V to paste the formula:

Excel workbook open, Cells select from E7 to E9, with the result from the formula that was inserted before

6. Click Formulas → Show Formulas to show formulas rather than values in the cells that contain them. Note that when you copied the formula to the cells below, the cell references automatically change relative to the cell that contains the formula:

Excel workbook open, Formulas tab selected, Show Formulas option is chosen, the cells show the formulas that each contain

7. Now select cell F6, press Ctrl + C to copy the formula, then select cells F7 through F9 and press Ctrl + V to paste the formula:

Excel workbook open, Cells F7 to F9 selected with the final result from the formula that was pasted

8. Click Formulas → Show Formulas to return to the default view of values rather than formulas:

Excel workbook open, Formulas tab selected, Show Formulas option selected

9. Notice that the results in F7 through F9 are incorrect. This is because the formula in cell F6 uses a relative reference for the discount value in cell B3. When you copy the formula to the cells below F6 the reference is automatically adjusted. Select cell F7 to show the cell references in the formula. The reference to the discount amount has been adjusted to cell B4:

Excel workbook open, Cell F7 selected, formula shows in that Cell

10. To correct this, select cell F6 and add a dollar sign ($) before the row reference to the discount amount. “=E6*B$3”:

Excel worbook open, F6 Cell selected the dollar sign was added in the cell

11. Press Ctrl + C to copy the adjusted formula, select cells F7 through F9, then press Ctrl + V to paste the formula:

Excel workbook open, the Cell F7 through F9 was selected, the formula was pasted in that cells

12. Click Formulas → Show Formulas. Note that the references to the subtotal cells have changed but the absolute references to the discount amount are the same:

Excel workbook open, Formulas tab selected, Show Formulas option selected

13. You can now click Formulas → Show Formulas to return to the default view. To finalize the workbook, select cell G6, press Ctrl + C to copy the formula, then select cells G7 through G9 and press Ctrl + V to paste the formula:

Excel workbook open, Formulas tab selected, Show Formulas option selected, the G7 through G9 selected, the formulas pasted

14. Save your workbook as Activity 2-1 Complete. Close Microsoft 365 Excel to complete this activity.

15.Now, you can check out an example of a completed document in the link below:

https://github.com/alcadelina/Excel-

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)