COURSE 1 – PREPARING DATA FOR ANALYSIS WITH MICROSOFT EXCEL

INTRODUCTION – Formulas and functions

SELF-REVIEW: CALCULATING PROFIT AND MARGIN

In the exercise Calculating profit and margin, you helped Adventure Works to calculate their annual profits and margins using the dataset in the worksheet Jan-Dec in the Microsoft Workbook Revenue figures.

Your final worksheet should resemble the following screenshot:

Now it’s time to review your understanding of the tasks you completed by answering the questions that follow. Don’t forget that you can revisit the previous learning items to recap the process steps.

1. When you created the calculation in cell G4, you were able to use the double-click shortcut to Autofill copies of the formula into the cells underneath. What data did Excel use as a visual reference to determine when to stop copying the formula?

2. In cell H4 you created a formula that worked out the total shipping cost. Cell P1 contained the $5 per item shipping charge. Column F contained multiple rows of entries. Which of the following was the correct syntax for the formula to ensure that it copied correctly when you used the Autofill shortcut?

3. In cell J4, you created a formula with parentheses to set a new retail price by adding numbers together and working out a percentage increase. What result did you get for the calculation? 

4. You are creating a formula in a worksheet called Summary. This formula also includes a reference to cell H2 in a worksheet called Products. How will this reference appear in the formula?

5. You are creating a calculation in your worksheet. You want Excel to subtract a figure held in C5 from a total held in H2 and then for the result to be divided by four. Which formula will calculate this correctly?

6. A Microsoft Excel worksheet contains the following formula:

=A2*B3/C2+D4

Which operator in this calculation will Excel process first?

KNOWLEDGE CHECK: FORMULAS IN EXCEL

1. You are creating a formula in cell C2 in your worksheet. The calculation only needs to appear in cell C2. You have typed the following formulas: 

A3*D15/45*F17

However, there is an error in the syntax of this formula. What part of the formula is missing?

2. Cell D2 of your worksheet contains the weekly sales total. Cell D7 contains the overall monthly sales. You need cell E2 to show what percentage the week one total is of the overall monthly total. You’ve already applied the percentage format to E2. Which of the following options is the correct syntax for the formula?

3. True or False: When Excel processes the following formula, it will first multiply cell A1 by cell C3 before it processes the addition.

=(B15+D45)-(A1*C3)

4. You have created the following formula in your worksheet:

=D4+$C$6-E4+F6

You use the Autofill feature to copy the formula down the column. Which of the cell references will update with new row numbers?

5. What mathematical sign does Excel process last when calculating the results of the following formula?

=D4+C3/N5*B3

SELF-REVIEW: PREPARING A MONTHLY SALES REPORT

In the ungraded exercise, you completed a series of tasks to prepare a monthly sales report for Adventure Works.

You first downloaded the file Monthly sales report.xlsx. You then created a series of calculations to determine the total sales amount for the month of April.

A screenshot of the worksheet is included for reference.

Now it’s time to review your understanding of the tasks you complete by answering the questions that follow. Don’t forget that you can refer to previous lesson items to recap your process steps.

1. Cell C34 in your worksheet is blank and cell C35 contains a formula. The cursor is on C36. If you use the AutoSum shortcut to add a SUM formula, which cell range does Excel automatically suggest as the argument for the function?

2. True or False: When you created the Total Revenue formula in C35, the results appeared as dollar amounts because the Accounting format had been applied to the cell in advance.

3. When you created the formula in C39 to count how many days there were in the month, what answer did the formula produce?

4. You need to access the full range of functions available in Excel. Which ribbon do you need to select to access the full library of functions?

5. You need to access the Insert function feature to create a function formula. What actions can you take to access this feature? Select all that apply:

KNOWLEDGE CHECK: GETTING STARTED WITH FUNCTIONS

1. Which of the following items are part of a function formula? Select all that apply:

2. You need to know how many cells in a cell range contain entries. The cells with entries contain a mixture of text and numeric entries. Which function should you use in this situation? 

3. Three cells in your worksheet contain numbers. Cell D7 holds the number 10, D8 contains the number 20, and D9 contains the number 30. What is the result of the following formula when added to cell D10? 

4. Cells B1 to B3 in your worksheet contain numbers. Cell B4 has a piece of text. You add an AVERAGE function formula to cell B5, which works out the average of cells B1 to B4. How will Excel work out the average?

5. Cell E4 in your worksheet contains the value 300, cell E5 the value 300, and cell E6 the value 0. What is the correct answer to the following formula when added to your worksheet?

=AVERAGE(E4:E6)

MODULE QUIZ: FORMULAS AND FUNCTIONS

1. You need to add a new figure in cell H52 of a workbook. However, you accidentally begin typing the number in cell H51, which already contains data. What key can you press to cancel your entry?

2. True or False: The following formula automatically recalculates and the result updates if new values are entered in any of the referenced cells.

=A2+F3-H3

3. Row one of your spreadsheet contains the following formula:    

=A1*C1/$B$1

If you use the Autofill feature to copy the formula down to the next row, what will the formula in row two be?

4. You have created a formula in your worksheet which reads:

=(300+30)/(10-7)

What would the answer be?

5. Column B of your worksheet contains stock item prices. In cell C2 you need a formula to calculate the discount on the current stock price in B2 using the 10% discount rate in cell F2. You also need to copy this formula down column B to apply it to the other stock items listed. Which formula would calculate the discount and allow for the formula to be copied? 

6. True or False: You are using the Insert Function feature to add a formula to your worksheet. You’ve switched to the category Math & Trig to find a function. The list of functions is organized in the order that they were recently used. 

7. As you type a function formula into a cell, a help message appears to remind you of the arguments required. How does the message display the optional arguments?

8. According to the order of precedence, which mathematical operators would Excel automatically assign a higher priority to than a minus symbol? Select all that apply: 

9. Cell C7 in your worksheet contains the value 300, and cell A4 has the entry 12. Which of the following calculations would correctly calculate 12% of the value in C7? Both A4 and the formula cell have the General format applied. Select all that apply.

10. Your team achieved sales of $35,000 last month and $45,000 this month. Which of the following calculations would work out the percentage they had increased their sales by? 

11. Cell E7 in your worksheet contains a value of 0. F7 contains a value of 100. Cell G7 contains a value of 200, and cell H7 contains a piece of text. What is the correct answer for the following formula?    

=COUNTA(E7:H7) 

12. Cell A1 of your spreadsheet contains the value 10. Cell B1 contains the value 5, and cell C1 contains the value 2. The formula you created in cell D1 reads:    

=A1*B1+$C$1

Row 2 of your spreadsheet is completely empty. If you were to copy the formula down to cell D2 what would the formula result be?

13. What mathematical symbol is processed first in the following formula? 

=((A3+H4)/G7)*5

14. You are creating a worksheet to track team performance. You know that the team increased their sales in May. The team’s April sales figures are in cell C5. Their sales figures for May are in cell D5. Which one of the following calculations would show you the percentage increase?

15. Cell F7 contains a value of 100. Cell G7 has a value of 200, and cell H7 is empty. What is the correct answer for the following formula?    

=AVERAGE(F7:H7) 

16. What information must be added to the Number 1 and Number 2 boxes when working with the Function Arguments dialog to add a SUM function formula using the Insert Function wizard?

17. You are using the Insert Function wizard to add a formula to your worksheet. When the Insert Function dialog opens, what Function category list is displayed?

18. The formulas in your worksheet are not recalculating. You discover that this is because the Automatic Calculation feature is turned off. Which ribbon can you access this feature from to turn it back on?

19. True or False: You are working with the Number 1 and Number 2 boxes in the Function Arguments dialog in the Insert Function wizard. The Number 2 entry is not bolded, which means that it’s optional.

20. Cell A4 in your worksheet contains the value 10. Cell A5 contains the value 100. Cell A6 contains the value 2. In cell A7 you have created the following formula: 

=10*100/2 

If you change the value in cell A6 to 4, then what would the result of this formula be?

21. As you type a function formula into a cell, a help message appears to remind you of the arguments required. How does the message display these required arguments?

22. What function does Excel use when the Count Number choice is selected from the AutoSum drop-down list?

CONCLUSION – Formulas and functions