3 Preparing data for analysis using functions
3.1 Understanding Data Consistency in Analysis
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?
⬜ The dates are typed as numbers.
⬜ The dates are typed with forward slashes.
✅ The dates are typed as text.
3.2 Self-Review: Standardizing Text-Based Data
Question 1
You created formulas in column C that use the TRIM function with cell references from column B.
The entries in column B contained extra spaces before, after, and between words.
What did the formulas remove?
⬜ The spaces before the entries only.
⬜ The spaces before, after, and all spaces within the entries.
✅ The spaces before and after the entries, as well as extra spaces between words.
Question 2
True or False: You created formulas in cells K2 to K200 to combine the content from column G and column I.
After doing so, you immediately deleted columns G and I, since they were no longer needed.
✅ False
⬜ True
Question 3
In the CONCAT formula in K2, you added a third argument to indicate that two words needed a space between them.
What character did you input on either side of the space in the formula?
✅ A double quote.
⬜ An exclamation mark.
⬜ A parenthesis.
3.3 Knowledge Check: Using Functions to Clean or Standardize Text
Question 1
True or False: An Excel spreadsheet contains country names split over multiple columns.
Cell A2 contains the word United, and Cell B2 contains the word States.
The following formula generates the result United States:
=CONCAT(A2," ",B2)
✅ True
⬜ False
Question 2
A text entry in a spreadsheet contains the following text:
aDVENTURE wORKS rESELLERS
Which function should you use to convert this text to lowercase, with the first letter of each word capitalized?
✅ PROPER
⬜ LOWER
⬜ UPPER
Question 3
In the Adventure Works Reseller spreadsheet, the reseller names are listed in column D.
Cell D2 contains the text:
EastBike Shop
What is the result of this formula?
=MID(D2,5,4)
⬜ East
✅ Bike
⬜ Shop
Question 4
Cell A2 contains the following entry:
aceE6548
What result would the following formula generate when applied to the above entry?
=UPPER(A2)
✅ ACEE6548
⬜ Ace6548.
⬜ aCE6548
Question 5
Cell E4 contains the following city name:
North Miami Beach
What result would the following formula generate in your worksheet?
=RIGHT(E4,5)
⬜ North
✅ Beach
⬜ Miami
3.4 Understanding Date Calculations in Excel
The date 05/30/23 has a serial number of 45076.
What serial number will the date 05/31/23 have?
⬜ 45078
⬜ 45079
✅ 45077
3.5 Dynamic Date and Time Entries
How many arguments does the TODAY function require?
⬜ Two
⬜ One
✅ None
3.6 Self-Review: Calculating the Number of Working Days Remaining in the Year
Question 1
You used the MONTH function in the formula:
=MONTH(D5)
Cell D5 contained the date 07/02/23 in American format (MM/DD/YY).
What result did the formula return?
⬜ 2023
✅ 7
⬜ 2
Question 2
True or False: If you enter the TODAY function in cell B1, the result automatically updates every time formulas recalculate in the worksheet.
✅ False
⬜ True
Question 3
You used the NETWORKDAYS function in a formula to calculate the number of working days between two dates.
By default, what type of dates does the function exclude from its result?
⬜ Any holiday date
✅ Any weekend and holiday date
⬜ Any weekend date
3.7 Knowledge Check: Date and Time Functions
Question 1
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 one space.
✅ An opening and closing parenthesis only.
⬜ An opening and closing parenthesis and two spaces.
Question 2
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 date and time.
⬜ The time only.
Question 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?
⬜ DATEDIF
⬜ CONCAT
✅ DATE
Question 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.
✅ False
⬜ True
Question 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?
✅ 09/10/2025
⬜ 01/30/2023
⬜ 04/15/2020
3.8 Using the IF Function
Question
True or False: When a cell referenced in the logical test of an IF function formula does not meet the stated condition, Excel will instead perform the instruction detailed in the VALUE IF FALSE argument.
✅ True
⬜ False
3.9 Using Nested IF and IFS Function
Question
You have created a nested IF formula using three IF functions.
How many closing parentheses do you need at the end of the formula?
⬜ One
✅ Three
⬜ Two
3.10 Self-Review: Adding a Data Column Using the IFS Function
Question 1
You created an IF function formula in cell H7 that checks whether the value in G7 exceeds $10,000.
If true, Excel should display a 10% discount.
How should you enter the percentage value in the Value_if_true argument of the formula?
⬜ 10
⬜ “10%”
✅ 10%
Question 2
You needed to create a formula in cell L7 to check the delivery charge for a region.
Since the formula required more than two conditions, you used IFS instead of IF to make it more efficient.
✅ True
⬜ False
Question 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?
⬜ The criteria entry
✅ The criteria range
⬜ The SUM range
3.11 Knowledge Check: Logical Functions
Question 1
Your worksheet contains the following values:
A2 = 250
What is the result of the following formula when entered into a cell?
=IF(A2>300,10%,IF(A2>200,5%,0%))
⬜ 0%
⬜ 10%
✅ 5%
Question 2
You create a formula using the IFS function to test for different letters in column A.
What symbol should you use to enclose each text character in the formula?
⬜ Parentheses
⬜ Single quotation marks
✅ Double quotation marks
Question 3
Your worksheet contains the following values:
A2 = 100
B2 = 200
C2 = 400
What result will this formula return?
=IF(OR(A2>=200,B2>=200),“Result 1”,IF(C2>300,“Result 2”,“Result 3”))
✅ Result 1
⬜ Result 2
⬜ Result 3
Question 4
You create the following formula using the AVERAGEIF function:
=AVERAGEIF(A2:A50,“Chicago”,C2:C50)
What does the first argument represent?
⬜ The average range
⬜ The criteria
✅ The criteria range
Question 5
Your worksheet contains the following values:
A2 = 100
B2 = 200
C2 = 400
What result will this formula return?
=IFS(A2>200,“Rate 1”,B2>200,“Rate 2”,C2>200,“Rate 3”,TRUE,0)
⬜ Rate 1
⬜ Rate 2
✅ Rate 3
3.12 Module Quiz: Formulas and Functions
Question 1
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)
⬜ Date
✅ Text
⬜ Numeric
Question 2
In your customer details worksheet, cell A2 contains the entry mARY gOMEZ.
What is the result of the following formula?
=PROPER(A2)
✅ Mary Gomez
⬜ mary gomez
⬜ MARY GOMEZ
Question 3
Some of the information in your worksheet has been typed in the wrong columns.
The entry in C2 incorrectly reads: 32MainAvenueChicagoUSA
What is the result of the following formula?
=LEFT(C2,12)
✅ 32MainAvenue
⬜ 32MainAvenueChicago
⬜ 32Main
Question 4
True or False: A NOW function formula only generates a new time result every 24 hours.
⬜ True
✅ False
Question 5
Cell A2 contains the date 05/30/23 and cell B2 contains 06/01/23.
What is the result of the following formula?
=B2-A2+1
⬜ 1
✅ 3
⬜ 2
Question 6
Cell D2 contains the date 05/30/23 (MM/DD/YY).
What result does the following formula display?
=DAY(D2)
✅ 30
⬜ 5
⬜ 23
Question 7
You are working on a spreadsheet where columns A, B, and C are called Day, Month, and Year.
Each of these columns contains numbers.
What data format is the result generated in when the following formula is added to your spreadsheet?
=DATE(C2,B2,A2)
⬜ Numeric format
⬜ Text format
✅ Date format
Question 8
True or False:
Cell A2 contains a value of 100.
B2 contains a value of 200.
The formula below displays a result of TRUE:
=AND(A2>=100,B2>=250)
✅ False
⬜ True
Question 9
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 to complete this task?
✅ An IF function
⬜ An AND function
⬜ An OR function
Question 10
Cell A2 in your spreadsheet contains a value of 200.
Which result does the following formula display?
=IF(A2<>100,“FirstMessage”,IF(A2>300,“SecondMessage”,“Third Message”))
✅ FirstMessage
⬜ SecondMessage
⬜ Third Message
3.13 What features were new to you and which were most useful
I really enjoyed learning how Excel functions can clean, organize, and analyze data more effectively.
The text functions such as CONCAT() and PROPER() were new to me and helped me understand how to fix inconsistent or messy data.
I also found the date functions like TODAY() and NETWORKDAYS() very useful for calculating deadlines and tracking progress automatically.
The logical functions, especially IF() and IFS(), were the most valuable because they make worksheets smarter by applying conditions to data.
These lessons gave me a clearer understanding of how to make my spreadsheets more dynamic, accurate, and efficient for real-world data tasks.