Common calculation formulas

Would you like to follow along with the instructor using the same spreadsheet?

To use the template for this spreadsheet, click the link below and select “Use Template.”

Link to template: Discount Variety Store-433, Monthly Sales

OR

If you don't have a Google account, you can download the spreadsheet from the attachment below.

Discount-Variety-Store-433-Monthly-Sales

Question: You are calculating the sum of a range of cells from A2 through F2. What is the correct syntax for this calculation?

A. =SUM(A2:F2)

B. =SUM(A2+F2)

C. =SUM(A2,F2)

D. =SUM(A2*F2)

The correct syntax is A. =SUM(A2:F2). Explain: To calculate the sum of a range of cells from A2 through F2, the correct syntax is =SUM(A2:F2). Formulas begin with an equals sign. A2:F2 are the cell references to be summed. And the colon between the two cell references indicates that it is a range of cells.

You are analyzing the results of retail sales calculations. You click in cell H10 and find the following formula: =H9/G9. What operation is taking place in this cell?

A. Multiplication

B. Addition

C. Division

D. Subtraction

The correct answer is C. Division. Explain: In the formula =H9/G9, the operation is division. The operator for division is a slash (/).

Functions and conditions

If you would like to follow along with this video, click the link below and select "Use Template."

Link to template: Kitchen Supplies, Transactions

OR

If you don't have a Google account, you can download the spreadsheet directly from the attachment below.

Kitchen Suppliers, Transactions

Question: A data analyst is using the following formula: =COUNTIF(C2:C50, “=100”). Which part of the formula defines the value condition that the data must meet in order to be counted?

A. COUNTIF

B. =100

C. C2

D. C50

The correct answer is B. =100. Explain: In the formula =COUNTIF(C2:C50, “=100”), “=100” defines the value condition that the data must meet in order to be counted. In this formula, cells C2 through C50 will be counted if their value equals 100.

Composite functions

To follow along using the same spreadsheet as the instructor, click the link below and select "Use Template."

Link to template: Kitchen Supplies, Profit

OR

If you don't have a Google account, you can download the spreadsheet in the attachment below:

KP Kitchen Supplies - Profit

Question: You want to calculate a company’s annual revenue using financial data. You use the SUMPRODUCT function to multiply each of the values in the arrays B3:B7 and C3:C7, then add them together. What is the correct syntax to complete this calculation?

A. SUMPRODUCT(B3,B7:C3,C7)

B. =(SUMPRODUCT)(B3:B7*C3:C7)

C. =SUMPRODUCT(B3:B7,C3:C7)

D. =SUMPRODUCT+(B3:B7/C3:C7)

The correct syntax to complete this calculation is C. =SUMPRODUCT(B3:B7,C3:C7). Explain: The correct syntax is =SUMPRODUCT(B3:B7,C3:C7). Using this syntax will multiply each value in the first range by its corresponding value in the second range. Then, the values will be added together.

Start working with pivot tables

Would you like to follow along with the instructor using the same spreadsheet?

To use the template for the spreadsheet, click the link below and select “Use Template.”

Link to template: Movie Data Starter Project

OR

If you don't have a Google account, you can download the spreadsheet directly from the attachment below.

Movie Data Starter Project

The menu option has slightly changed. To insert a pivot table select Insert and Pivot Table.

Heads up! To view the options that are shown next, in the Pivot table editor you must first click the Add button for Values.

Question: When using the Values menu in a pivot table, what does the Summarize By drop-down menu do?

A. Filters the values in ascending order

B. Changes the function applied to the values

C. Selects the values for the pivot table

D. Chooses how to sort the values

The correct answer is B. Changes the function applied to the values. Explain: The Summarize By drop-down menu changes the function applied to the values. The SUM function is the default function, but there are other options, such as COUNT.

Pivot tables continued

Question: A calculated field within a pivot table is used to carry out calculations based on what?

A. The values of other fields

B. The filtered values

C. The function in the calculated field

D. The syntax of the available formulas

The correct answer is A. The values of other fields. Explain: A calculated field within a pivot table is used to carry out calculations based on the values of other fields. The calculated field is added as an additional row or column in a pivot table.

Queries and calculations

A data analyst is writing a SQL query. Which commands would they use to first multiply values from a table, then place the resulting products in a new column? Select all that apply.

• FROM
• SELECT
• AS
• DIVIDE

Explain: Use the SELECT, FROM, and AS commands in a query to first multiply values from a table, and then place the resulting products in a new column. This will SELECT the values FROM a table and rename them AS the name of the column with the calculated values.

To find the average of a set of values, a data analyst can type AVERAGE in both spreadsheets and SQL. True or False?

A. True

B. False

It is false statement. Explain: The AVERAGE function finds the average of a set of values in spreadsheets. When using SQL, the function is AVG.

Embedding simple calculations in SQL

Note: At this point, you should have already uploaded the avocado.csv data set to your Big Query project.

If you are following along with your own BigQuery dataset, you may have noticed there is some information missing before the avocado_data.avocado_prices query line in the video. You can find the full Table ID in the top line of the DETAILS tab.

Anytime an uploaded local dataset name is typed in the FROM section of a SQL query, the dataset and data table file path name will always be preceded by the project name. Here is a template of the file path structure in this lecture example:

personal project name.avocado_data.avocado_prices

The created project name, unique to each learner, will be inserted before the dataset name. If you don't insert your personal project name, it will most likely cause an error to occur when the query is run.

Keep this technique in mind for future lessons anytime you are typing a local file path name in the FROM section of a query when uploading a dataset.

If you need a quick refresher in uploading the data set to this project, please refer to the previous reading exercise titled, "Optional: uploading the avocado dataset to BigQuery."

Question: When using SQL, which of the following are reasons for using underscores in column names? Select all that apply.

• It keeps the column names readable
• It tells the server that the values in the columns are for calculations
• It helps avoid potential issues with servers and applications
• It verifies that the values in the columns are accurate

Explain: Using underscores instead of spaces helps avoid potential issues with servers and applications. It also helps to keep the column names readable.

What will appear in the Total_Small_Bags_Cost column after the following query is run?

SELECT
Small_Bags,
Small_Bags_Cost,
Small_Bags * Small_Bags_Cost AS Total_Small_Bags_Cost
FROM avocado_data.avocado_prices

A. The sum of the values in the Small_Bags and Small_Bags_Cost columns

B. The product of the values in the Small_Bags and Small_Bags_Cost columns

C. The sum of the values in the “Small_Bags_Cost” and “avocado_data.avocado_prices” columns

D. The product of the values in the “Small_Bags_Cost” and “avocado_data.avocado_prices” columns

The correct answer is B. The product of the values in the Small_Bags and Small_Bags_Cost columns. Explain: The product of the values in the Small_Bags and Small_Bags_Cost columns will appear in the Total_Small_Bags_Cost column. The query will multiply the values in the two columns.

Calculations with other statements

NOTE: If you are following along in BigQuery with the video, you may notice after clicking Preview that the first 100 observations of the citibike_trips in the new_york_citibike dataset all have NULL values. Nonetheless, the initial observations of the table in the video have non-zero values populating the data Preview.

Near the bottom of the screen of your BigQuery Preview window, note the total amount of observations equaling 58,937,715. Click on the arrow button that will navigate to the end of the list, and you will see data populate each column. At this point you may begin to understand why BigQuery is used to search this table instead of downloading ~59M rows of data!

Question: Which of the following options will be returned with this query?

SELECT
Yes_Responses,
Total_Responses,
Total_Responses - Yes_Responses AS No_Responses
FROM
Survey_1

A. The number of “Yes” and “No” responses combined

B. The total when “Yes” responses are sorted from “No” responses

C. The difference when "Yes" responses are subtracted from the total number of responses

D. The result of dividing the total number of responses by “Yes” responses

The correct answer is C. The difference when "Yes" responses are subtracted from the total number of responses. Explain: This query will return the difference when "Yes" responses are subtracted from the total number of responses. The calculation will appear in a new column named No_Responses.

What is the purpose of the EXTRACT command in a query?

A. To extract a single value from a column

B. To extract a column from a database

C. To extract a part from a given date

D. To extract a single word from a string

The correct answer is C. To extract a part from a given date. Explain: The purpose of the EXTRACT command in a query is to extract a part from a given date. The EXTRACT command can extract any part from a date/time value.

Check and recheck

Note: To better reflect the directions indicated by the instructor, the WHERE clause should be typed as the following:

Total_Bags != Total_Bags_Calc

Question: Fill in the blank: The data validation process is a form of data _____.

A. reporting

B. cleaning

C. transformation

D. formatting

Explain: The data validation process is a form of data cleaning. During this process, data analysts check the quality of their data to make sure it’s complete, accurate, secure, and consistent.

Temporary tables

Question: A data analyst has a large number of sales records in a table. They want to perform calculations on a small subset of the table. Rather than filtering the data over and over, what should they do?

A. Use a copy of the table

B. Use a temporary table

C. Use an alternate table

D. Use a backup table

The correct answer is B. Use a temporary table. Explain: They should use a temporary table. A temporary table is created and exists for a short time on a database server.

Multiple table variations

Question: Which of the following are methods for creating a version of a temporary table using SQL? Select all that apply.

• CREATE TABLE statements
• WITH clauses
• WHERE clauses
• CREATE TEMP TABLE statements

Explain: WITH clauses, CREATE TABLE statements, and CREATE TEMP TABLE statements all create temporary tables in queries.