preparing data for analysis with microsoft excel week 3 coursera answers

Self-Review: Calculating profit and margin

In the Standardizing text-based data exercise, you created calculations to standardize the data in the Adventure Works spreadsheet. You also used the functions TRIM, UPPER, PROPER, LEFT, MID, RIGHT, and CONCAT to clean the data.

The results you produced should be similar to those in the following screenshot:

Answer the questions that follow to test your understanding of the processes. Remember that you can refer to previous lesson items if required.

1. In your worksheet, you created formulas in column C that included the TRIM function and cell references from column B as arguments. The entries in column B contained a lot of unnecessary spaces though the entries contain only a single space between each word. What did the formulas remove?

  • The spaces before, after, and in the entries.
  • The spaces before the entries only.
  • The spaces before and after the entries.

2. True or False: You created formulas in cells K2 to K200 to combine the content from column G and column l. You were then able to delete columns G and I immediately, as they were no longer required.

  • True
  • False

3. In the CONCAT formula in K2, you added a third argument to indicate that the two words needed space between them. What character did you input on either side of the space in the formula?

  • An exclamation mark.
  • A parenthesis.
  • A double quote.

Knowledge check: Using functions to clean or standardize text

4. True or False: An Excel spreadsheet contains country names split over multiple columns. Cell A2 contains the word United. Cell B2 contains the word States. When executed, the following formula generates the result United States:

=CONCAT(A2," ",B2)

  • True
  • False

5. One of the employees at Adventure Works has made some typing errors in a spreadsheet. A text entry in cell contains the following text:

aDVENTURE wORKS rESELLERS

Which function should you use in a formula to copy this entry so that it is in lowercase with a capital letter at the beginning of each word?

  • UPPER
  • LOWER
  • PROPER

6. In the Adventure Works Reseller spreadsheet, the reseller names are listed in column D. Cell D2 contains the following text, which is left-aligned in the cell with no redundant spaces:

EastBike Shop

What is the result of this formula?

  • Bike
  • Shop
  • East

7. Cell A2 contains the following entry:

aceE6548.

What result would the following formula generate when applied to the above entry?

=UPPER(A2)

  • Ace6548.
  • aCE6548
  • ACEE6548

8. Cell E4 contains the city name North Miami Beach. What result would the following formula generate in your worksheet?

=RIGHT(E4,5)

  • North
  • Beach
  • Miami

Self-review: Calculating the number of working days remaining in the year

In the exercise Calculating the number of working days remaining in the year, you created calculations to add new date and time information to a worksheet called USA Launch Dates in a workbook named Advertising Campaign USA. In the formulas you created, you used the functions TODAY, NETWORKDAYS, MONTH, and YEAR to calculate the required information.

Your final workbook should resemble the following screenshot:

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

9. In the spreadsheet, you were asked to create a formula using the MONTH function. The formula was:

=MONTH(D5)

Cell D5 contained the entry 07/02/23. This date is in American format. What was the result of this calculation?

  • 2023
  • 2
  • 7

10. True or False: If you were to use a TODAY function in a formula in cell B1, the result would change each time the formulas in the worksheet recalculate.

  • True
  • False

11. You created a formula using the NETWORKDAYS function. You added two arguments for the function, which were the start date, and the end date. What data did the formula automatically exclude from the result?

  • Any holiday date
  • Any weekend date
  • Any weekend and holiday date

Knowledge check: Date and time functions

12. You have created a formula in your spreadsheet using the TODAY function. What must you include after the word TODAY in your formula?

  • An opening and closing parenthesis and two spaces.
  • An opening and closing parenthesis and one space.
  • An opening and closing parenthesis only.

13. You have created a formula in your spreadsheet using the NOW function. By default, what will the formula display in its result?

  • The date only.
  • The time only.
  • The date and time.

14. You are working on a spreadsheet that contains three columns called Day, Month and Year. In another column, you would like to combine these entries so that it shows a complete date. Which function should you use to achieve this?

  • CONCAT
  • DATEDIF
  • DATE

15. True or False: You can use the NETWORKDAYS.INTL function to calculate the number of working days between two dates while excluding national holidays and weekends because it has built-in knowledge of public holidays.

  • True
  • False

16. You have entered the following three dates in your spreadsheet in the month, day and year format. Which of these dates has the largest serial number?

  • 09/10/2025
  • 04/15/2020
  • 01/30/2023

Self-Review: Adding a data column using the IFS function

In the exercise Adding a data column using the IFS function, you created calculations to generate two columns of new data in the Order Details spreadsheet in the workbook named Contoso Bikes. You generated this data using the IFS and IFS functions. You also used the function SUMIF to generate regional totals in the spreadsheet.

The results you produced should be like those in 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.

17. You created an IF function formula in cell H7 that was designed to display the correct percentage discount if the amount in G7 was greater than $10,000. In what format did you enter the percentage in the Value if true section of the formula?

  • “10%”
  • 10%
  • 10

18. True or False: When you created a formula in cell L7 to check and display the delivery charge for the region, you used IFS rather than IF because you needed to run more than two tests but also create the most concise formula.

  • True
  • False

19. You created a formula in H2 which used SUMIF to obtain a total for Region A entries only. What did you need to specify as the first argument in your formula?

  • The SUM range
  • The criteria entry
  • The criteria range

Knowledge check: Logical functions

20. Cell A2 of your worksheet contains a value of 250. What is the result of the following formula when added to your worksheet

=IF(A2>300,10%,IF(A2>200,5%,0%))

  • 0%
  • 5%
  • 10%

21. You create a formula using the IFS function to test for a series of alphabet characters. When typing the criteria to test for, what symbols should you add around each text character?

  • Single quotation marks.
  • Double quotation marks.
  • Parentheses.

22. In your worksheet, cell A2 contains a value of 100. Cell B2 contains a value of 200, and C2 contains a value of 400. What is the result of the following formula when added to your worksheet?

=IF(OR(A2>=200,B2>=200),"Result 1",IF(C2>300,"Result 2","Result 3"))

  • Result 2
  • Result 3
  • Result 1

23. You create the following formula using the AVERAGEIF function:

=AVERAGEIF(A2:A50,"Chicago”,C2:C50)

What does the first argument of this function represent?

  • The average range.
  • The criteria range.
  • The criteria.

24. In your worksheet, cell A2 contains the value 100. Cell B2 contains a value of 200, and C2 contains a value of 400. What is the result of the following formula when added to your worksheet?

=IFS(A2>200,"Rate 1",B2>200,"Rate 2",C2>200,"Rate 3",TRUE,0)

  • Rate 1
  • Rate 2
  • Rate 3

Module quiz: Formulas and functions

25. In your worksheet, cell A2 contains the word Super. Cell B2 contains the word Cycles. You add the following formula to your worksheet:

=CONCAT(A2," ",B2," ", "Inc.")

What is the result of this formula?

  • Super CyclesInc
  • SuperCyclesInc
  • Super Cycles Inc.

26. In your customer details worksheet, you’ve noticed that cell A2 contains the entry mARY gOMEZ. What is the result of the following formula which references this cell?

=PROPER(A2)

  • mary gomez
  • Mary Gomez
  • MARY GOMEZ

27. Some of the information in your worksheet has been typed in the wrong columns. The entry in C2 incorrectly reads as:

32MainAvenueChicagoUSA

What is the result of the following formula which references this entry?

=LEFT(C2,12)

  • 32MainAvenueChicago
  • 32MainAvenue
  • 32Main

28. When typing time entries into a cell, what character can you use to separate the hours and minutes?

  • A comma.
  • A semi-colon.
  • A colon.

29. You have typed the date 05/30/23 directly in cell A2 of your worksheet. The date is in the MM/DD/YY format. What date will this cell display when you open the worksheet again two days from now?

  • 05/30/23
  • 06/01/23
  • 05/31/23

 

30. You have written the following formula in your worksheet. What result is displayed every time you re-open the file? =TODAY()+1

  • Yesterday’s date.
  • Tomorrow’s date.
  • Today’s date.

31. You need to create a formula in your worksheet which calculates the number of weekdays between two dates. Which one of the following functions can you use to complete this task?

  • DATEDIF
  • NETWORKDAYS
  • DATE

32. In your worksheet, cell A2 contains a value of 150. Cell B2 contains a value of 200. Cell C2 contains a value of 300.

The following formula, which contains three logical tests, has returned a result of FALSE. Which of these logical tests failed?

=AND(A2>100,B2>150,C2>350)

  • The second logical test.
  • The third logical test.
  • The first logical test.

33. You must create an IF formula that checks the values in three cells and displays a “value if true” message of “target met” if any of the three values is over 1,000. Which function can you “nest” inside the IF function of your formula to complete this task?

  • An IF function.
  • An AND function.
  • An OR function.

34. Cell A2 in your spreadsheet contains a value of 200. When executed, what message does the following formula display? =IFS(A2=50,"First Message",A2=100,"Second Message",A2=150,"Third Message",TRUE,"No Message")

  • First Message
  • Second Message
  • Third Message
  • No Message

35. Columns A, B and C of your worksheet contain numeric entries. The columns are called Day, Month and Year. What type of data does the following formula generate when added to your worksheet?

=CONCAT(A2,B2,C2)

  • Text
  • Numeric
  • Date

36. You are working on a spreadsheet that contains a column of customer names. You notice that there are a lot of extra spaces both before and after the entries. Which function could you use to tidy and standardize the customer name entries?

  • PROPER
  • RIGHT
  • TRIM

37. Cell A2 of your spreadsheet contains the date 05/30/23. Cell B2 contains the date 06/01/23. Both dates are in the MM/DD/YY format. Tip: If your machine is set to a different region, entering dates in the MM/DD/YY format will generate an error. Enter the dates in the format required for your region. For example, if you live in Europe the format would be DD/MM/YY.

What is the result of the following formula which references these cells?

=B2-A2+1

  • 2
  • 1
  • 3 

 

38. Cell D2 in your spreadsheet contains the date 05/30/23. The date is in MM/DD/YY format. What result does the following formula display?

=DAY(D2)

  • 30
  • 23
  • 5 

39. You need to create an IF formula that runs a series of tests. If one test fails, the IF formula must move to the next test. Which function do you need to “nest” inside the IF function in this formula to complete this task?

  • An IF function.
  • An AND function.
  • An OR function.

 

40. Cell A2 in your spreadsheet contains a value of 150. Which message does the following formula display when executed? =IFS(A2=50,"First Message",A2=100,"Second Message",A2=150,"Third Message",TRUE,"No Message")

  • No Message
  • Second Message
  • First Message
  • Third Message

41. Cell B2 of your worksheet contains the following entry:

mOUNTAIN Bike

What is the result of the following formula which references this cell?

=LOWER(B2)

  • mountain bike
  • Mountain Bike
  • MOUNTAIN BIKE

42. Some of the information in your worksheet has been typed in the wrong columns. For example, the entry in C2 incorrectly reads as:

32MainAvenueChicagoUSA

Which one of the following functions can you use to extract the city name Chicago from this entry and display it in a separate column?

  • RIGHT
  • MID
  • LEFT

43. True or False: You can display the serial number behind a date by changing the cell format to General.

  • True
  • False

 

44. Cell A2 of your spreadsheet contains the date 05/30/23. Cell B2 contains the date 06/01/23. Both dates are in the MM/DD/YY format. Tip: If your machine is set to a different region, entering dates in the MM/DD/YY format will generate an error. Enter the dates in the format required for your region. For example, if you live in Europe the format would be DD/MM/YY.

What is the result of the following formula which references these cells?

=B2-A2

  • 3
  • 1
  • 2 

45. Cell D2 in your spreadsheet contains the date 05/30/23. The date is in MM/DD/YY format. What result does the following formula display?

=YEAR(D2)

  • 5
  • 2023
  • 30 

46. You are working on a spreadsheet that contains employee information. Column A lists the date staff members started with the company. Column B lists the date they left the company. Cell A2 lists an employee’s start date as 05/30/16, and cell B2 lists their end date as 05/30/23.

What result does the following formula display?

=DATEDIF(A2,B2,"Y")

  • 7
  • 6
  • 5 

47. True or False: Cell A2 contains a value of 100. B2 contains a value of 200. When executed, the following formula which references these cells, displays the result TRUE. =OR(A2>=100,B2>=250)

  • True
  • False 

48. You must create an IF formula that checks the values in three cells. It must display a “value if true” message of “target met” if all three values exceed 1,000. Which function should you “nest” inside the IF function in this formula to complete this task?

  • An OR function.
  • An IF function.
  • An AND function.

 

Leave a Reply