## Complete Coursera Study Guide

Enroll in Coursera Google Data Analytics

## pERFORMING DATA CALCULATIONS – INTRODUCTION

Google Data Analytics with Coursera provides learners the opportunity to learn about performing data calculations. This part of the course will cover formulas, functions and pivot tables in Google Spreadsheets as well as SQL queries for managing temporary tables. With this knowledge, students will be able to gain a better understanding of how to perform data calculations effectively.

Google Data Analytics with Coursera also provides learners the chance to gain insights into the benefits of using SQL to manage temporary tables, which can be an integral part of data analysis. Google Data Analytics with Coursera is a great way to learn how to perform data calculations and understand the importance of SQL in data analytics.

Let’s get started!

## Learning Objectives

• Describe the use of functions to conduct basic calculations on data in spreadsheets
• Discuss the use of pivot tables to conduct calculations on data in spreadsheets
• Demonstrate an understanding of the use of SQL queries to complete calculations
• Explain the importance of the data-validation process for ensuring accuracy and consistency in analysis
• Discuss the use of SQL queries to manage temporary tables
• Reflect on how conditional statements can be used to create complex queries and functions
• Generate multiple points of summary based on a wide variety of conditions using COUNTIF, SUMIF, MAXIF, and AVERAGEIF

## Hands-On Activity: Working with conditions

### In this activity you tested the query =countif(B2:B21, “NY”, C2:C21, “1”)

``in this activity you tested the query =countif(B2:B21, "NY", C2:C21, "1")``
• 1
• 5
• 4 (Correct)
• 3

Correct: The query =COUNTIFS(B2:B21, “NY”, C2:C21, “1”) returns the value 4, since there are 4 salespeople in New York that have only one client. To find this information, you used the COUNTIFS function with additional constraints. Going forward, you can use other conditional functions to find specific information from your data, which will help you carry out more complex analyses.

## Test your knowledge on Data Calculation

### 1. What is the correct spreadsheet formula for multiplying 50 and 233?

• 50×233
• =50*233 (Correct)
• =50×233
• 50*233

Correct: =50*233 is the correct formula for multiplying 50 and 233. Formulas begin with an equal sign (=). This is followed by the values to be computed. An asterisk (*) is the multiplication operator in spreadsheets.

### You are trying to determine what percentage of your monthly income is spent on big-ticket items, such as rent and groceries. To add together only the values from Column B that cost more than \$150, what is the correct syntax?

• =SUMIF(B2:B12,”<150″)
• =SUMIF(B2:B12,”>150″) (Correct)
• =SUMIF(B2:B12,<150)
• =SUMIF(B2:B12,>150)

Correct: To add together only the values from Column B that cost more than \$150, the correct syntax is =SUMIF(B2:B12,”>150″). B2:B12 is the range, and more than 150 (>150) is the criteria.

### Which of the following is an example of an array in this spreadsheet?

• Cells D7 and D14
• All cells with values greater than 100
• The values in cells B2 through B31 (Correct)
• All cells with number values

Correct: The values in cells B2 through B31 together are an example of an array. An array is a collection of values in spreadsheet cells.

## Hands-on activity: Explore movie data with pivot tables

### 1. Which movie genre generates the most profit on average?

• Thriller
• Fantasy
• Comedy

Correct: To find out which movie genre generates the most profit on average, you created a pivot table with a calculated field and sorted the data accordingly. Going forward, you will be able to use pivot tables to quickly summarize data to draw insights and even create visualizations directly in your spreadsheet. This will help you in the future as you continue to work with spreadsheets as a data analyst.

## Test your knowledge on using pivot tables

### What is the purpose of the pivot table in this spreadsheet?

• To organize all of the data into a smaller format
• To calculate the sum of individual prices for each product type (Correct)
• To find the average price of each product
• To summarize data about each product

Correct: The purpose of the pivot table is to calculate the sum of individual purchase prices for each product type. It also shows a grand total for all of the product purchases.

### 2. How could the pivot table be adjusted to show the same data, but only for products categorized as beige?

• Sort the current row by product color
• Summarize the values by product
• Add a new column labeled beige
• Add a filter to show only beige products (Correct)

Correct: To show the same data, but only for products categorized as beige, add a filter to show only beige products.

### 3. Which spreadsheet tool should you use if you want to find an average value using values generated within a pivot table?

• Data validation
• A calculated field (Correct)
• A filter
• Conditional formatting

Correct: To find an average value using values generated within a pivot table, use a calculated field. A calculated field is a new field within a pivot table that carries out certain calculations based on the values of other fields.

## hands-on activity: Calculations in sql

### 1. Using the same average formula as the last query you wrote, write a query to find the average weekly ridership from the years 2016-2018. What is the average weekly ridership for the Atlantic Av – Barclays Ctr station in this timeframe?

• 49255
• 42672.33 (Correct)
• 13212.67
• 4903.67

Correct: The average weekly ridership for the Atlantic Av – Barclays Ctr from the years 2016-2018 is 42672.33. To find the station’s total average weekly ridership, you would have to use a SQL calculation to add the three columns ridership_2016, ridership_2017, and ridership_2018, then divide the sum by 3. Going forward, you can use SQL to perform mathematical calculations with data and analyze patterns in real-world situations.

## Test your knowledge on SQL Calculations

### What total appears in row 1 of your query result?

• 7.92
• 1.98
• 3.96
• 0.99 (Correct)

Correct: You add the statement unit_price * quantity AS line_total to calculate the total price for each invoice and store it in a new column as line_total. The complete query is SELECT invoice_line_id, invoice_id, unit_price, quantity, unit_price * quantity AS line_total FROM invoice_items LIMIT 5. The AS command gives a temporary name to the new column.

The total 0.99 appears in row 1 of your query result.

### 2. In a SQL query, which calculation does the modulo (%) operator perform?

• It converts a decimal to a percent
• It finds the square root of a number
• It returns the remainder of a division calculation (Correct)
• It applies an exponent to a value

Correct: The modulo operator returns the remainder of a division calculation when included in a SQL query.

### 3. You are working with a dataset with the column name “firstquarterexpenses.” How can you rename this column to make it more readable?

• first_quarter_expenses (Correct)
• Firstquarterexpenses
• first quarter expenses
• first+quarter+expenses

Correct: You can rename the column first_quarter_expenses. Using underscores between words helps avoid potential issues while keeping the names readable.

Liking our content? Then, don’t forget to ad us to your bookmarks so you can find us easily!

## hands-on Activity: From spreadsheet to bigquery

### 1. What is the earliest year included in this dataset?

• 2017 (Correct)
• 2018
• 2019
• 2020

Correct: 2017 is the earliest year included in this dataset. To find the date range of this dataset, you used MIN and MAX functions in SQL to determine the earliest and latest years. You were able to pull this observation without actually scrolling through all of the data manually, which is a key skill when working with larger datasets.

## Test your knowledge on data validation

### 1. What are the goals of checking and rechecking the quality of your data during data validation? Select all that apply.

• Data is consistent (Correct)
• Data is secure (Correct)
• Data is complete and accurate (Correct)
• Data is sorted and filtered

Correct: Checking and rechecking the quality of your data during data validation process helps ensure the data is complete, accurate, secure and consistent.

### 2. You’re analyzing patient data for a health care company. During the data-validation process, you notice that the first date of service for some of the patients is later than the most recent date of service. Which type of data-validation check are you completing?

• Data structure
• Data type
• Data consistency (Correct)
• Data range

Correct: This is a check for data consistency. During a data consistency check, you confirm that the data makes sense in the context of other related data.

### 3. During analysis, you complete a data-validation check for errors in customer identification (ID) numbers. Customer IDs must be eight characters and can contain numbers only. Which of the following customer ID errors will a data-type check help you identify?

• IDs that are repeated
• IDs with more than eight characters
• IDs in the wrong column
• IDs with text (Correct)

Correct: Completing a data-type check will help you identify customer IDs that contain text. The data type for IDs should be numeric only.

## hands-on activity: create temporary tables

### 1. In a past activity, you learned about the importance of using the right type of join. In this activity, you wrote a query with an INNER JOIN to join your temporary table with the original bikeshare_trips table. Which station ID would your query return if you used a FULL JOIN instead of an INNER JOIN?

• 3798 (Correct)
• 2758
• 2575
• 3575

Correct: If you used a FULL JOIN instead of an INNER JOIN to join your temporary table to the bikeshare_trips table, you would return the station ID 3798. This is because the temp table you create has to be joined to the original in a specific way in order to return the correct answer. Going forward, you can use temp tables to perform more complex queries like this, which will help you analyze complex data throughout your career.

## Test your knowledge on using SQL with temporary tables

### 1. When are temporary tables automatically deleted?

• After ending the session in a SQL database (Correct)
• After running a query in your SQL database
• After running a report from the table
• After completing all calculations in the table

Correct: Temporary tables are automatically deleted after ending the session in a SQL database.

### What data will appear in the temporary table created through this query?

• A random subset of bike trips
• Bike trips that lasted exactly 60 minutes (Correct)
• The total number of bike trips
• Bike trips equal to or more than one hour

Correct: This temporary table will show bike trips that lasted exactly 60 minutes. The name of the table is “1_hr_trips” and the query includes the condition that trips in the table equal one hour.

### 3. What benefit does a CREATE TABLE statement add to a temporary table?

• Access for anyone to use the table (Correct)
• Automated calculations
• Specific naming conventions

Correct: A CREATE TABLE statement provides access for anyone to use the temporary table. The SELECT INTO statement is better suited for one person.

## Analyze Data to Answer Questions Weekly Challenge 4

### The analyst inputs a function to find the number of product prices that are less than \$150.00. Which formula will return that result?

• =COUNTIF(G2:G30, “>=150”)
• =COUNTIF(G2:G30, “<150”) (Correct)
• =SUMIF(G2:G30, “>150”)
• =SUMIF(G2:G30, “<150”)

Correct: The COUNTIF formula =COUNTIF(G2:G30, “<150”) will allow the analyst to count all product price values in Column G that are less than \$150.

### Which part of this formula is the criteria or condition?

• =SUMIF
• A1:A25
• C1:C25
• ”<10” (Correct)

Correct: The criteria or condition for this SUMIF formula is “<10”. This means that if any values in the range A1 through A25 are less than 10, their corresponding values in the range C1 through C25 will be added together.

### How does the SUMPRODUCT function calculate the cell ranges identified in the parentheses?

• It adds the ranges, then multiplies them by the last value in the second array.
• It multiplies the ranges, then adds the sum of the products of the two ranges. (Correct)
• It adds the values in the first range, then adds the values in the second range.
• It multiplies the values in the first range, then multiplies the values in the second range .

Correct: =SUMPRODUCT(A2:A10,B2:B10) calculates the cell ranges by multiplying each value in the first range by its corresponding value in the second range (the results are the products). Then, the formula adds those products together.

Liking our content? Then, don’t forget to ad us to your bookmarks so you can find us easily!

### In order to find out how much box office revenue each genre earned, you would use the SUM function in the Values menu to summarize the data.

• True (Correct)
• False

Correct: You would use the SUM function to figure out how much box office revenue each genre earned. In the pivot table, the SUM function would add the total revenue separately for each genre.

### 5. A data analyst uses the following query to perform basic calculations on their data. The variables in the query have the following values: yes_responses = 10, no_responses = 12, total surveys = 22. What is the value of the Responses_Per_Survey variable?

• 44
• 11
• 22
• 1 (Correct)

Correct: The value of the Responses_Per_Survey variable is 1. In this query, the analyst first calculates the sum of the “yes” and “no” responses, then divides the sum by the total surveys.

### What is the duration in seconds of the track with Id number 3408?

• 193
• 120 (Correct)
• 307
• 153

Correct: You add the statement milliseconds / 1000 AS secs to calculate the duration in seconds for each track and store it in a new column as secs. The complete query is SELECT track_id, track_name, composer, milliseconds, milliseconds / 1000 AS secs FROM track WHERE composer = “Johann Sebastian Bach”. The AS command gives a temporary name to the new column.

The duration of the track with Id number 3408 is 120 seconds.

### What is the total number of purchases for the invoice with Id number 4?

• 2
• 4
• 14
• 9 (Correct)

Correct: You add the clause GROUP BY invoice_id to group the data by customer Id number. The complete query is SELECT invoice_id, SUM(quantity) AS total_purchases FROM invoice_item GROUP BY invoice_id. The GROUP BY command groups rows that have the same values from a table into summary rows. GROUP BY is always placed as the last command in a SELECT-FROM-WHERE query.

### What is the average total for India?

• 5.37
• 5.78 (Correct)
• 5.64
• 6.02

Correct: You add the statement AVG(total) AS average_total to calculate the average total and store it in a new column as average_total. The complete query is SELECT billing_country, AVG(total) AS average_total FROM invoice WHERE billing_country = “India”. The AVG function is an aggregate function that returns the average value of a group of values. The AS command gives a temporary name to the new column.

The average total for India is 5.78.

Liking our content? Then don’t forget to add us to your bookmarks so you can find us easily!

## Analyze Data to Answer Questions Course Challenge

### Which of the following tools will enable you to sort your spreadsheet by city (Column K) in ascending order?

• Sort Range by Column K from Z to A
• Sort Sheet by Column K from Z to A
• Sort Range by Column K from A to Z
• Sort Sheet by Column K from A to Z (Correct)

Correct: To sort your spreadsheet by city in ascending order, Sort Sheet by Column K from A to Z. You can also use the SORT function syntax =SORT(A2:R210, 11, TRUE).

### What spreadsheet tool allows you to control what can and cannot be entered in your worksheet in order to avoid typos?

• Data validation (Correct)
• Find
• List
• VLOOKUP

Correct: Data validation allows you to control what can and cannot be entered in your worksheet in order to avoid typos. It does this by adding drop-down lists with predetermined options, such as each city name.

### Which of the following spreadsheet tools will enable you to change how cells appear if they contain a value of \$100 or more?

• The COUNTA function
• Data validation
• The MAX function
• Conditional formatting (Correct)

Correct: To change how cells appear, use conditional formatting. Choose to format cells if they are greater than or equal to 100.

### To move the 5-digit zip code in cell L2 into its own column, you use the function =LEFT(L2,5).

• True
• False (Correct)

Correct: To move the 5-digit zip codes in Column L into their own column, you use the RIGHT function: =RIGHT(L2,5).

### Which SQL function will instruct the database to combine two columns into a new column called “address”?

• COALESCE
• COMBINE
• CAST
• CONCAT (Correct)

Correct: The CONCAT function is used to instruct a database to combine two columns into a new column called “address.”

### What SQL function do you use to select all data from the Donation_Form_List organized by zip code?

• ARRANGE BY
• SEQUENCE
• ORDER BY (Correct)
• ORGANIZE

Correct: To select all data from the Donation_Form_List organized by zip code, you use the ORDER BY function. The ORDER BY function sorts results returned in a query.

### To retrieve only those records that include people who have served on the board of trustees or on the board of directors, what is the correct query?

Correct: To retrieve only those records that include people who have served on the board of trustees or on the board of directors, the correct WHERE statement is the first option from the list.

### What SQL function will return records with matching values in both tables?

• OUTER JOIN
• INNER JOIN (Correct)
• LEFT JOIN
• RIGHT JOIN

Correct: An INNER JOIN function will return records with matching values in both tables.

### AVG(contributions_2020) AS AvgLineTotal

• True (Correct)
• False

Correct: To return average contributions in 2020, the correct portion of the SQL query is:

AVG(contributions_2020) AS AvgLineTotal

### What syntax adds the contribution amounts in cells O2 and P2? Select all that apply.

• =O2+P2 (Correct)
• =SUM(O2,P2) (Correct)
• =(O2/P2)
• =O2,P2

Correct: To add cells O2 and P2, use the function =SUM(O2,P2). You can also use the formula =O2+P2.

### What is the correct syntax to count how many donations of \$100 or greater appear in Column O (Contributions 2018)?

• =COUNTIF(O2:O210,”>=100″) (Correct)
• =COUNTIF(O2:O210>=100)
• =COUNTIF(O2:O210″>=100″)
• =COUNTIF(O2:O210,>=100)

Correct: To count how many donations of \$100 or greater appear in Column Q, the correct syntax is =COUNTIF(O2:O210,”>=100″).

### Which SQL query can be used to calculate the percentage of contributions from prospects? Which SQL query can be used to calculate the percentage of contributions from prospects?

Correct: To identify the percentage of contributions from prospects, the correct query is the first option above

### To retrieve the number of donors in each city, sorted high to low, you use the following query:

• True
• False (Correct)

Correct: To retrieve the number of donors in each city, sorted high to low, DESC must be included. ASC will sort the donors low to high. The correct query is:

## Performing data Calculation – conclusion

With the help of Coursera, students can learn about google data analytics and how to perform calculations effectively. This part of the course will cover formulas, functions, pivot tables, and SQL queries. By having this knowledge, individuals will be able gain a deeper understanding on how to manage data properly.

Join the learning experience in Coursera today!

#### Subscribe to our site

Get new content delivered directly to your inbox.

## Quiztudy Top Courses

Liking our content? Then, don’t forget to ad us to your BOOKMARKS so you can find us easily!