preparing data for analysis with microsoft excel week 2 coursera answers

Self-Review: Calculating profit and margin

1. 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?

  • The block of information in row 201.
  • The block of information in column F.
  • The block of information in column K.

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?

  • =$F$4*P1
  • =F4*P1
  • =F4*$P$1

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?

  • $222.16
  • $333.24
  • $224.66

Knowledge check: Formulas in Excel

4. 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?

  • Parentheses
  • An Equals Sign
  • Dollar Signs

5. 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?

  • =D7/D2
  • =D2/D7*100
  • =D2/D7

6. 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)

  • True
  • False

7. 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?

  • C6, E4, F6
  • D4, C6, E4
  • D4, E4, F6

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

=D4+C3/N5*B3

  • The plus sign.
  • The Division sign.
  • The Multiplication sign.

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.

9. 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?

  • C35
  • C36
  • C34

10. 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.

  • True
  • False

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

  • 30
  • 31
  • 0

Knowledge check: Getting started with functions

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

  • Parentheses

  • An equals sign

  • Periods
  • The function name

13. 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?

  • COUNTA
  • COUNTBLANK
  • COUNT

14. 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?

=MAX(D7:D8)

  • 20
  • 10
  • 30

15. 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?

  • Total the numbers in B1 to B3 and divide by 4.
  • Total the numbers in B1 to B3 and divide by 3.
  • Total the numbers in B1 to B3 and divide by 5.

16. 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)

  • 200
  • 300
  • 100

Module quiz: Formulas and functions

17. 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?

  • The Formula Ribbon.
  • The Data Ribbon.
  • The Home Ribbon.

18. 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?

  • 750
  • 250
  • 500

19. 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?

  • 50
  • 2
  • 52

 

20. 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:

  • Multiplication

  • Division

  • Addition

 

21. 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?

  • =B2*F2
  • =B2*$F$2
  • =B2/$F$2

22. The Percentage format has been applied to cell E4. What is the result of the following formula when typed in cell E4?

=150/200*100

  • 7500%
  • 75%
  • 75

 

23.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)

  • 4
  • 3
  • 2

 

24. 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.

  • True
  • False

25. 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.

  • True
  • False

26. You enter the following function formula in your spreadsheet only to be met with an error message indicating that it contains a typo. What element of the formula is incorrect?

=sum(a7;d7)

  • The formula should not include parentheses.
  • The punctuation symbol is incorrect.
  • The function name must be in upper case.

27. 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

  • True
  • False

28. 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?

  • =(35000-45000)/35000
  • =(45000-35000)/35000
  • =(45000-35000)/45000

29. 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)

  • 150
  • 100
  • 200

30. 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?

  • The name of the function.
  • The function parentheses.
  • The function arguments.

31. 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?

  • All
  • Math & Trig
  • Most Recently Used

 

32. 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

  • True
  • False

33. 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?

  • All
  • Math & Trig
  • Most Recently Used

 

Leave a Reply