Organizing Data to Begin Analysis INTRODUCTION

Hands-On Activity: Combine multiple pieces of data

1. In an empty cell in the CONCAT function exercise spreadsheet, type the function =CONCATENATE(A7, ” “, B7). What does this function return?

Test your knowledge on converting and formatting data

1. A spreadsheet cell contains the coldest temperature ever recorded in New Zealand: -22 °Celsius. What function will display that temperature in Fahrenheit?

2. A data analyst wants to ensure spreadsheet formulas continue to run correctly, even if someone enters the wrong data by mistake. Which data-validation menu option should they select to flag data entry errors?

3. A data analyst clicks on the Format Cells in drop-down menu and selects the option Text Is Exactly November. This changes the color of all the cells that contain the word November. What spreadsheet tool is the analyst using?

Test your knowledge on combine multiple datasets

1. Fill in the blank: In SQL, _____ can be used to combine strings from multiple tables in order to create a new string.

2. You are working with a database table that contains data about playlists for different types of digital media. You are only interested in the first 4 playlists.

You write the SQL query below. Add a LIMIT clause that will return only the first 4 playlists.

What playlist appears in row 2 of your query result?

3. What function can be used to return the number of characters in cell B8 so you can confirm that it contains exactly 20 characters?

GOOGLE DATA ANALYTICS COURSERA ANSWERS AND STUDY GUIDE

Weekly Breakdown | Google Study Guides | Back to Top

Analyze Data to Answer Questions Weekly Challenge 2

1. An analyst working for a British school system just downloaded a dataset that was created in the United States. The numerical data is correct but it is formatted as U.S. dollars, and the analyst needs it to be in British pounds. What spreadsheet tool can help them select the right format?

2. You are preparing a project tracker spreadsheet. Next to each project task, you need to add the name of the team member responsible. What spreadsheet tool will save you time by enabling you to create a drop-down list with team members’ names as the possible options?

3. You are using a spreadsheet to keep track of your newspaper subscriptions. You add color to indicate if a subscription is current or has expired. Which spreadsheet tool changes how cells appear when values meet each expiration date?

4. You are analyzing data about the capitals of different countries. In your SQL database, you have one column with the names of the countries and another column with the names of the capitals. What function can you use in your query to combine the countries and capitals into a new column?

5. You are querying a database of keynote speakers to determine who has expertise in zoology. For your project, you only need the first 12 records. What clause should you add to the following SQL query?

6. Fill in the blank: A data analyst is working with a spreadsheet that has very long text strings. They use the LEN function to count the number of _____ in the text strings.

7. Spreadsheet cell E13 contains the text string “Database”. To return the substring “Data”, what is the correct syntax?

8. When working with a spreadsheet, data analysts use the find function to locate specific characters in a string. Find is case-sensitive, so it’s necessary to input the substring exactly how it appears.

9. Fill in the blank: A data professional at a junior college uses the spreadsheet _____ function to locate specific characters from a listing of course codes.

10. A data analyst works with a spreadsheet containing product information that often has very long text strings. To check for consistency, they use a function to count the number of characters in cell P12. What is the correct syntax of the function?

11. You prepare a project tracker spreadsheet. Next to each project is the name of the team member responsible. What spreadsheet tool will create a drop-down list with team member names to save you time when assigning the projects?

12. An analyst works with a dataset of financial data that is formatted in U.S. dollars. However, it should be formatted as Japanese yen. What spreadsheet tool should they use to correct the format?

13. You use a SQL database to evaluate rideshare efficiency. The database table contains one column of pick-up locations and another column of drop-off locations. What function will combine the pick-up and drop-off locations into a new column? 

14. A data professional uses a spreadsheet to track the total purchases made by each member of the company’s procurement department. What spreadsheet tool can they use to change how the cells appear when someone has made more than $5,000 in purchases?

15. Fill in the blank: A junior data analyst working with interest rate data uses the SQL function _____ to adjust 6.23456789012345678901234567890% to just one decimal place. 

16. Spreadsheet cell L2 contains the text string Function. To return the substring Fun, what is the correct syntax?

17. A data analyst works with a spreadsheet containing medical record numbers that must be exactly 30 digits long. To ensure accuracy, they use a function to count the number of characters in cell H10. What is the correct syntax of the function?

18. You use a spreadsheet to help you with an upcoming gift exchange. Every time you find an interesting gift idea, you add it to the spreadsheet. What spreadsheet tool will create a drop-down list with Need to Purchase and Purchased as the possible options?

19. An analyst works with financial data that is formatted as Euros, but it should be formatted as pesos. What spreadsheet tool should they use to correct the format?

20. You use a SQL database to analyze airline flight routes in Africa. The database table contains one column of departure airports and another column of arrival airports. What function will combine the departure and arrival airport names into a new column?

21. A data analyst at a symphony orchestra uses a spreadsheet to track how many concerts require more than 65 musicians. What spreadsheet tool can they use to change how cells appear when values equal 65 or more?

22. Spreadsheet cell H9 contains the text string Tableau. To return the substring Tab, what is the correct syntax?

23. Fill in the blank: A data analyst at a logistics company uses the spreadsheet _____ function to locate specific characters from a shipping confirmation email.

24. You use a spreadsheet to organize a list of upcoming tasks at work. Column A contains the list of tasks, and column B notes their priority: high, medium, or low. What spreadsheet tool will create a drop-down list of the priorities in column B?

25. A data analyst in human resources uses a spreadsheet to track employee work anniversaries. What spreadsheet tool can they use to add color to the cells containing the name of any employee who has worked for the business for at least five years? 

26. Fill in the blank: A data analyst analyzing the boiling points of various liquids uses the SQL function _____ to adjust 356.727801 °C to just three decimal places. 

27.  You use a spreadsheet to help you with an upcoming gift exchange. Every time you find an interesting gift idea, you add it to the spreadsheet. What spreadsheet tool will create a drop-down list with Need to Purchase and Purchased as the possible options?

28. An analyst notes that the “85” in cell A3 is formatted as text, but it should be Australian dollars. What spreadsheet tool should they use to correct the format?

29.  Fill in the blank: A data professional analyzing gasoline prices uses the SQL function _____ to adjust the number $3.5698726 to just two decimal places. 

Organizing Data to Begin Analysis CONCLUSION