Course 5 – Analyze Data to Answer Questions
Week 2: Formatting and Adjusting Data Quiz Answers
GOOGLE DATA ANALYTICS PROFESSIONAL CERTIFICATION
Complete Study Guide
Organizing Data to Begin Analysis INTRODUCTION
Organizing and formatting data is a critical part of the data analysis process. In this section of Coursera’s Google Data Analytics Professional Certificate program, you’ll learn how to use SQL queries to combine different sets of data, as well as other methods for organizing and formatting your data.
You’ll also get all the support and feedback you need from your peers, which can be invaluable in helping you understand new concepts that can be applied to your own work. By the end of this course, you will understand how to organize and format data to be used in the analysis, enabling you to utilize the power of analytics fully.
- Demonstrate an understanding of what is involved in the conversion and formatting of data
- Demonstrate an understanding of the use of spreadsheets and SQL queries to combine multiple pieces of data
- Discuss the importance of seeking feedback and support from others
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?
- John QuincyAdams
- John Quincy Adams (Correct)
Correct: The function =CONCATENATE(A7, ” “, B7) would return “John Quincy Adams.” The correct function to use here would be CONCATENATE, which you can use to combine strings. Using CONCAT with these arguments would resolve this correctly in Microsoft Excel, but return an error in Google Sheets. Going forward, you can use this distinction to write proper spreadsheet functions.
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?
- =CONVERT(-22, C, F)
- =CONVERT(-22, F, C)
- =CONVERT(-22, “C”, “F”) (Correct)
- =CONVERT(-22, “F”, “C”)
Correct: =CONVERT(-22, “C”, “F”) will display -22 °C 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?
- Deny Help Text
- Reject Invalid Inputs (Correct)
- Remove Validation
- Forbid Entry
Correct: To ensure spreadsheet formulas continue to run correctly, even if someone enters the wrong data by mistake, select Reject Invalid Inputs to flag that data as invalid.
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?
- Data validation
- Conditional formatting (Correct)
Correct: The data analyst is using conditional formatting. Conditional formatting is a spreadsheet tool that changes how cells appear when values meet specific conditions.
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.
- CONCAT (Correct)
Correct: In SQL, CONCAT can be used to combine strings from multiple tables in order to create a create 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?
The Movies playlist appears in row 2 of your query result.
- Movies (Correct)
- TV Shows
Correct: The clause LIMIT 4 will return only the first 4 playlists. The complete query is SELECT * FROM playlist LIMIT 4. The LIMIT clause sets a limit on the number of rows your query returns.
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?
- =LEN(20, B8)
- =LEN(B8) (Correct)
- =LEN(B8, 20)
Correct: The function =LEN(B8) will display the number of characters in cell B8. The LEN function returns the length of a string of text by counting the number of characters it contains.
GOOGLE DATA ANALYTICS COURSERA ANSWERS AND STUDY GUIDE
Liking our content? Then don’t forget to add us to your bookmarks so you can find us easily!
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?
- Format as Currency (Correct)
- Format as Pounds
Correct: Format as Currency can be used to select the right currency 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?
- Conditional formatting
- Data validation (Correct)
- Pop-up menus
Correct: Data validation can be used to add drop-down lists with predetermined options for each team member’s name.
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?
- Data validation
- Add color
- Conditional formatting (Correct)
Correct: You are using conditional formatting. Conditional formatting changes how cells appear when values meet specific conditions.
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?
- CONCAT (Correct)
Correct: You can use CONCAT, which enables you to join multiple text strings from multiple sources.
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?
- LIMIT 12 (Correct)
- LIMIT = 12
Correct: To return only the first 12 records, type LIMIT 12.
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.
- characters (Correct)
Correct: They use the LEN function to count the number of characters in a text string.
7. Spreadsheet cell E13 contains the text string “Database”. To return the substring “Data”, what is the correct syntax?
- =LEFT(E13, 4) (Correct)
- =RIGHT(E13, 4)
Correct: The function =LEFT(E13, 4) will return “Data” The LEFT function returns a set number of characters from the left side of a text string. In this case, it returns a four-character substring from the end of the string in E13, starting from the left.
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.
- True (Correct)
Correct: Find is case-sensitive, so it’s necessary to input the substring exactly how it appears.
Organizing Data to Begin Analysis CONCLUSION
As you move closer to analyzing your data, it is important to have the data formatted and ready to go. In this part of the course, you will learn all about converting and formatting data, including how SQL queries can help you combine data.
You will also find out the value of feedback and support from your colleagues and how it can lead to new learning that you can apply to your work. Joining the Coursera community will allow you to access these resources and put them into practice in your own work.
Subscribe to our site
Get new content delivered directly to your inbox.
Quiztudy Top Courses
Popular in Coursera
- Meta Marketing Analytics Professional Certificate.
- Google Digital Marketing & E-commerce Professional Certificate.
- Google UX Design Professional Certificate.
- Meta Social Media Marketing Professional Certificate
- Google Project Management Professional Certificate
- Meta Front-End Developer Professional Certificate
Liking our content? Then, don’t forget to ad us to your BOOKMARKS so you can find us easily!