COURSE 1 – PREPARING DATA FOR ANALYSIS WITH MICROSOFT EXCEL

INTRODUCTION – Preparing data for analysis using functions

SELF-REVIEW: STANDARDIZING TEXT-BASED DATA

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?

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.

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?

4. Jamie wants to perform a time analysis of Adventure Works’ sales data. However, a time analysis isn’t possible because of how the date is currently formatted. What error might have caused this formatting issue?

KNOWLEDGE CHECK: USING FUNCTIONS TO CLEAN OR STANDARDIZE TEXT

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

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

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

=MID(D2,5,4)

4. Cell A2 contains the following entry:

aceE6548.

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

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

=RIGHT(E4,5)

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.

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

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

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

4. The date 05/30/23 has a serial number of 45076. What serial number will the date 05/31/23 have?

5. How many arguments does the TODAY function require?

KNOWLEDGE CHECK: DATE AND TIME FUNCTIONS

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

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

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

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

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

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.

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

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

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

4. You have created a nested IF formula using three IF functions. How many closing parentheses do you need at the end of the formula?

KNOWLEDGE CHECK: LOGICAL FUNCTIONS

1. 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%))

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

3. 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″))

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

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

What does the first argument of this function represent?

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

MODULE QUIZ: FORMULAS AND FUNCTIONS

1. True or False: In your worksheet, cell A2 contains the word United. B2 contains the word States. You add the following formula to your worksheet:

=CONCAT(A2,B2)

When executed, the result of this formula is:

United States

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

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

=MID(C2,3,10)

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

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

6. 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. 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 a result of TRUE:

=AND(A2>=100,B2>=250)

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

9. Cell A2 in your spreadsheet contains a value of 200. Which result does the following formula, which references this cell, display?

=IF(A2<>100,”FirstMessage”,IF(A2>300,”SecondMessage”,”Third Message”))

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

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

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

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

14. 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”)

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

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

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

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

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

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

21. True or False: A NOW function formula only generates a new time result every 24 hours.

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

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

CONCLUSION – Preparing data for analysis using functions