Week 2: Sparkling-clean data

1. As part of the data-cleaning process, a data analyst creates a rule to highlight any empty cells in a bright blue color. This is an example of data visualization.

  • True
  • False

2.A data analyst at a nonprofit organization is working with the following spreadsheet, which contains member name data in column C. They want to divide this data using the underscore as a delimiter, so that first names are stored in one column and last names in another. Which tool should the analyst use?

  • Conditional formatting
  • Pivot table
  • SPLIT function
  • MID function

 

3.Fill in the blank: When describing a SUM function, the _____ is =SUM(value 1 through value 2).

  • syntax
  • standard
  • structure
  • script

 

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

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

  • =RIGHT(B3,5)
  • =RIGHT(5,B3)
  • =LEFT(5,B3)
  • =LEFT(B3,5)

 

5. 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 year hired plus the last four digits of the employee’s Social Security Number (SS#). What function will create the ID 20093208 for the employee in row 5?

  • =CONCATENATE(A5!B5)
  • =CONCATENATE(A5*B5)
  • =CONCATENATE(A5+B5)
  • =CONCATENATE(A5,B5)

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

21. 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 32082009 for the employee in row 5?

  • =CONCATENATE(B5,A5)
  • =CONCATENATE(A5!B5)
  • =CONCATENATE(A5+B5)
  • =CONCATENATE(B5+A5)

22. Before analyzing a dataset, an analyst maps the data. What is the reason for doing this?

  • The analyst wants to know what attributes the data has.
  • The analyst thinks the dataset might have some null values.
  • The dataset has no visualizations.
  • The dataset contains data from different sources.

23. A data analyst suspects that there are many blank cells in their spreadsheet corresponding to missing information. What spreadsheet tool can they use to identify only those cells containing the null values?

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

24. A data analyst is working on a spreadsheet in which one of the columns is name data. This data is formatted as lastname, firstname. The analyst chooses to divide this data into two new columns, one containing the firstname data and the other containing the lastname data. What spreadsheet tool would they use to do this?

  • The MID function
  • The SPLIT function
  • Substring formatting
  • Conditional formatting

25. Fill in the blank: The function _____ is used to return information in a column that contains a specified value.

  • VALUE
  • MATCH
  • VLOOKUP
  • FIND

26. In a spreadsheet, what function would you use to extract the last three characters of the string located in row 4, column C?

  • =RIGHT(3,C4)
  • =LEFT(C4,3)
  • =LEFT(3,C4)
  • =RIGHT(C4,3)

27. Fill in the blank: In order to make your spreadsheet easier to analyze, you choose to alter the way cells appear if their values meet certain conditions. The spreadsheet tool that you use to do this is called _____.

  • conditional formatting
  • cell querying
  • cell filtering
  • conditional ranking

28. An analyst is cleaning a new dataset. They want to make sure the data contained from cell B2 through cell B100 does not contain a number smaller than 10. Which COUNTIF function syntax can be used to answer this question?

  • =COUNTIF(B2:B100,”<9″)
  • =COUNTIF(B2:B100,”>=10”)
  • =COUNTIF(B2:B100,>50)
  • =COUNTIF(B2:B200, ”<=50”)

29. A data analyst is adding new data to the dataset. This data comes from a different source than the current data. What should the data analyst do to ensure the compatibility of the current and new data?

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

30. A delimiter is a character that indicates the beginning or end of a data item. The split text to columns tool uses a delimiter to accomplish what task?

  • To specify where to split a text string
  • To split duplicate substrings
  • To format a string to numeric
  • To change the format of a column of text

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

In order to extract the five-digit postal code from North Wales, PA, what is the correct function?

  • =LEFT(5,B2)
  • =LEFT(B2,5)
  • =RIGHT(5,B2)
  • =RIGHT(B2,5)

32. 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 year hired plus the last four digits of the employee’s Social Security Number(SS#). What function will create the ID 20142683 for the employee in row 3?

  • =CONCATENATE(A3*B3)
  • =CONCATENATE(A3,B3)
  • =CONCATENATE(A3+B3)
  • =CONCATENATE(A3!B3)

33. To evaluate how well two or more data sources work together, data analysts use data mapping.

  • True
  • False

34. An analyst is cleaning a new dataset containing 500 rows. They want to make sure the data contained from cell B2 through cell B300 does not contain a number greater than 50. Choose the statement that includes the correct syntax for this COUNTIF function?

  • =COUNTIF(B2:B300,”>50″)
  • =COUNTIF(B2:B300,”<50”)
  • =COUNTIF(B2:B300,>50)
  • =COUNTIF(B2:B300,<=50)

35. Fill in the blank: Data mapping is the process of _____ fields from one data source to another.

  • matching
  • extracting
  • inserting
  • transforming

Leave a Reply