W4 February 2 ✓

Agenda

Reflections and Personal Updates

What have you done/learned? Recall last week and anything since then.
What are you doing/learning next? Take a quick look ahead in the program and consider your personal goals.
What roadblocks do you have? Explain anything that may be hindering your progress.

✔️ Optional Individual Deliverable: Share anything new you have learned on Slack that may help others.

Breakout into Data Analytics and Robotics

Spreadsheets: Functions and Formulas

Exercise

In this exercise we will practice how to make simple calculations and use functions in spreadsheets to make processing easier. We will continue to use the Google sheet of movie data we created in the last exercise.

Review Last Exercise

Open your spreadsheet from the last exercise and review we accomplished.
Continue to use your spreadsheet for this exercise. If you missed the last exercise and need to jump into this one, use this link below and make a copy of the template spreadsheet. 🔗 Google Sheet with Starter Movie Dataset

Using a Formula to Generate a New Column of Data - Profit

In the column to the right of the genres_description column label the column profit for calculating profit.
In the first cell below the profit label, enter a formula for calculating the profit. =F2-E2This formula simply subtracts the movie budget from the revenue.
Copy that formula for all rows in our movie table. It is likely that Google Sheets offered to do this for you by asking your approval. If not, you can select the cell you want to copy and drag the corner down to the bottom of the column to quickly fill the rest of the table. There is also a double-click trick you can use.
That's it. Do a quick check to verify that the calculation is working as you expect on several movies. What other calculations do you think might be useful?

Using a Function to Calculate Averages

To the right of our table create a label in cell K1 by entering All Movies Vote Average
In the cell below our label, K2, enter the following function to calculate the average vote for the whole movie dataset: =AVERAGE(C:C) There are several ways to do this that we will cover in the demonstration.
Let's say we want to find the average vote for all drama movies. Create another label in L1 and enter All Dramas Vote Average.
In the cell below this label, L2, enter the following function to calculate the average vote for all the dramas in our movie dataset. =AVERAGEIF(H:H,"*Drama*",C:C) The H:H directs the functions to search the genres_description column to look for any movies containing the word "Drama" in the genre. It then averages the vote_average column, C:C, for matching movies. The * is a wildcard that allows the function to accept the word "Drama" even if there is text before or after the word.
That's it. Now consider how you would calculate the average for comedies or how you migh find the total revenue for action movies. Discuss how you might do that.

✔️ Individual Deliverable: Link to your Google Sheet.

Password Protected Formform.jotform.com

Review

Pacing with the Coursera coursework

Reference

Google Sheets function list - Google Docs Editors Helpsupport.google.com

Last updated 2 years ago

W4 February 2 ✓

Agenda

Reflections and Personal Updates

Breakout into Data Analytics and Robotics

Spreadsheets: Functions and Formulas

Exercise

Review Last Exercise

Using a Formula to Generate a New Column of Data - Profit

Using a Function to Calculate Averages

Review

Reference

Working with Patterns / Looping and Logic

Demo

Initializing the Set Up: An Index Variable with Starting Point

Adding a Conditional If Statement

Display var_x Value and Increment var_i by 1

Add the Movement and Set Up Loops

Challenge

Challenge Bonus

hashtagAgenda

hashtagReflections and Personal Updates

hashtagBreakout into Data Analytics and Robotics

hashtagSpreadsheets: Functions and Formulas

hashtagExercise

hashtagReview Last Exercise

hashtagUsing a Formula to Generate a New Column of Data - Profit

hashtagUsing a Function to Calculate Averages

hashtagReview

hashtagReference

hashtagWorking with Patterns / Looping and Logic

hashtagDemo

hashtagInitializing the Set Up: An Index Variable with Starting Point

hashtagAdding a Conditional If Statement

hashtagDisplay var_x Value and Increment var_i by 1

hashtagAdd the Movement and Set Up Loops

hashtagChallenge

hashtagChallenge Bonus

Agenda

Reflections and Personal Updates

Breakout into Data Analytics and Robotics

Spreadsheets: Functions and Formulas

Exercise

Review Last Exercise

Using a Formula to Generate a New Column of Data - Profit

Using a Function to Calculate Averages

Review

Reference

Working with Patterns / Looping and Logic

Demo

Initializing the Set Up: An Index Variable with Starting Point

Adding a Conditional If Statement

Display var_x Value and Increment var_i by 1

Add the Movement and Set Up Loops

Challenge

Challenge Bonus