Module 2: Clean Data for More Accurate Insights Answers (Part 2: Q16–30)

This is Part 2 of the Module 1 quiz answers for “Introducing Data Analytics and Analytical Thinking” from the Google Data Analytics Professional Certificate on Coursera.

Here, we’ll walk through questions 16 to 30 with detailed explanations to support your learning.

To find answers to the remaining questions, check out the full module breakdown below:

  • Part 1 (Q1–15)
  • Part 2 (Q16–30)
  • Part 3 (Q31–46)

16. A data analyst at an e-commerce company is working with a spreadsheet containing last month's sales. The most expensive product their company sells costs $49.99, so they want to quickly confirm that all of the data in the Sales column is $49.99 or less. What function can they use?

  • SUMIF
  • COUNTIF ✅
  • COUNT 
  • SUM

Explanation:
COUNTIF can count how many times values are within the desired range.

17. A data analyst wants to search for a certain value in a column, then return a corresponding piece of information. Which function should they use?

  • VALUE
  • VLOOKUP ✅
  • MATCH
  • FIND

Explanation:
VLOOKUP finds a value in one column and returns a related value from another.

18. A data analyst needs to combine two datasets. Each dataset comes from a different system, and the systems store data in different ways. What can the data analyst do to ensure the data is compatible?

  • Use a data visualization
  • Map the data ✅
  • Apply a data structure
  • Merge the data

Explanation:
Mapping means aligning fields from two datasets so they match correctly.

19. In their spreadsheet, a data analyst makes cells stand out for more efficient analysis. What spreadsheet tool is used to do this?

  • Cell filtering
  • Conditional ranking
  • Conditional formatting ✅
  • Cell querying

Explanation:
Highlights cells based on rules, like “highlight all over $1000”.

20. A data analyst uses the SPLIT function to divide a text string around a specified character and put each fragment into a new, separate cell. What is the specified character separating each item called?

  • Unit
  • Delimiter ✅
  • Partition
  • Substring

Explanation:
A delimiter is a character like a comma, space, or underscore used to split text.

21. A data analyst is using a function in a spreadsheet. For the function to work correctly, they follow the function’s syntax. What does this entail?

  • It is the function’s name and placement.
  • It is how the function can be used in a program.
  • It is the function’s required information and its proper placement. ✅
  • It is the purpose of the function and its use.

Explanation:
A function’s syntax specifies how to structure the function, including the order and type of arguments needed for the function to work correctly.

22. In a spreadsheet, what is the correct function for extracting the first two characters of the string located in cell A7?

  • =LEFT(A7,2) ✅
  • =LEFT(2,A7)
  • =RIGHT(A7,2)
  • =RIGHT(2,A7)

Explanation:
LEFT pulls a number of characters from the beginning of a string.

23. Fill in the blank: In a spreadsheet, the function VLOOKUP is used to _____ information in a column based on a specified data value.

  • return ✅
  • replace
  • transform
  • delete

Explanation:
It returns a value from another column, based on your search input.

24. What describes syntax?

  • It is the function’s required information and its proper placement. ✅
  • It is how the function can be used in a program.
  • It is the purpose of the function and its use.
  • It is the function’s name and placement.

Explanation:
It’s the correct way to structure a formula or function.

25. A data analyst in a human resources department is working with the following selection of a spreadsheet:

They want to create employee identification numbers (IDs) in column D. The IDs should include the last four digits of the employee’s Social Security Number(SS#) plus the year hired. What function will create the ID 19392020 for the employee in row 4?

  • =CONCATENATE(B4+A4)
  • =CONCATENATE(B4,A4) ✅
  • =CONCATENATE(A4+B4)
  • =CONCATENATE(A4!B4)

Explanation:
Combines B4 (last 4 digits) with A4 (year) into one string like “19392020”.

26. An analyst is cleaning a new dataset. They want to determine how many of the cells in column F have a value of 0. However, they only want rows 7 to 120 to be considered. Which COUNTIF function syntax can be used to answer this question?

  • =COUNTIF(F2:F1250, 0)
  • =COUNTIF(F7:F120, =0)
  • =COUNTIF(F7:F120,”0″) ✅
  • =COUNTIF(F7:F120,”=0”)

Explanation:
You need quotes when checking for specific text or numbers.

27. A data analyst needs to combine two datasets. Each dataset comes from a different system, and the systems store data in different ways. What can the data analyst do to ensure the data is compatible prior to analyzing the data?

  • Use a data visualization
  • Map the data ✅
  • Spot check for null values
  • Apply a data structure

Explanation:
This involves aligning column names, formats, or field meanings before merging.

28. A data analyst is working on a spreadsheet in which one of the columns contains name data. This data is formatted as lastname_firstname. The analyst splits this data at the underscore so that each piece—firstname and lastname—are contained in their own columns.

In this context, what is the underscore acting as?

  • Partition
  • Delimiter ✅
  • Substring
  • MID function

Explanation:
A delimiter is a character used to separate data in a string. In this case, the underscore _ separates lastname from firstname. When using functions like SPLIT(), this character tells the program where to divide the string.

29. A data analyst is using a function in a spreadsheet. When they input the function, they follow a predetermined structure that includes all required information for the function and its proper placement. What aspect of a function does this describe?

  • The specified value of the function
  • The syntax of the function ✅
  • The length of the function
  • The number of characters in the function

Explanation:
Syntax refers to the correct format or structure of a function—how the arguments are ordered and used. For example, in =SUM(A1:A10), the syntax includes the function name and the range inside parentheses.

30. You are working with the following selection of a spreadsheet:

In order to extract the five-digit postal code from Brandon, FL, what is the correct function?

  • =RIGHT(5,B4)
  • =RIGHT(B4,5) ✅
  • =LEFT(B4,5)
  • =LEFT(5,B4)

Explanation:
The RIGHT() function extracts characters from the end of a string. =RIGHT(B4,5) grabs the last 5 characters from cell B4, which is likely the zip code.

Hope this helped! Use the buttons below to move to the previous or next part.

Part 1 (Q1–15)Part 3 (Q31–46)

Leave a Reply