Extensions: Calculate Probability with Google Sheets

In this extension, you will make your probability data easier to understand by using conditional formatting.

Conditional formatting changes the background color of cells in a spreadsheet that contains certain words or numbers.

This helps your viewer see the most important parts of your data without having to read through the entire sheet.

You can apply conditional formatting to your Probability column to make it easy to see which outcomes are most likely and which are least likely.

To start, open your probability spreadsheet.

Select all of the results in the Probability column.

Then, use the toolbar to open the conditional formatting menu.

The data in the Probability column shows the likelihood of rolling each possible outcome.

To visualize this data, create a color scale that assigns each number a particular shade of color.

Change the minpoint -- or lowest value -- dropdown from min value to number.

The lowest number in your column is two point eight percent.

This appears as a decimal.

Next, update the other dropdowns to numbers as well.

Your middle and highest numbers will appear in the menu next to the midpoint and maxpoint.

Choose a color to represent the least likely outcome.

Then, choose another color to represent the most likely outcome.

The midpoint color automatically blends the minpoint color and the maxpoint color.

Now, when you look at your table, it’s easy to see which outcomes are the most likely...

And which are the least likely.

Great work! Now, it’s your turn: Apply conditional formatting to your probability calculations Select a color scale, Choose colors for the minpoint and maxpoint, And look at your data to see which outcomes are most likely and which are least likely.

Calculating probability can help you make decisions about the future.

A weather forecast is one way to use probability in everyday life.

In this extension, you will use Google Search and Google Calendar to create an event based on the weather forecast.

You will: review the forecast to plan for the upcoming week, determine the best day for a picnic you’re planning, And invite guests.

You will use Google Calendar to record the date and time of the event, enter important details, and send guests an invite.

First, check your local weather for the upcoming week.

Open a new tab in your browser and go to google dot com.

Search for weather in your location to see the weekly weather forecast.

Weather data for the upcoming week appear in your search results.

For each day, there is a brief description of the weather.

You can also see: the high and low temperature, the chance of rain or snow, the speed and direction of wind, and the level of humidity.

For your picnic, you would probably prefer a day with a high probability of sunny weather.

But, you’d also likely want to choose a day that is not too hot, humid, or windy.

Use the data from the weather forecast to choose a day.

Start with days that have a high probability of sun and a low probability of rain.

Also consider other factors such as temperature, wind, and humidity.

Once you have chosen a date and time for your picnic, open Google Calendar.

Create a new event.

Then, give it a title.

Enter the date and time of your event.

Then, add other event details, such as the location and description of the event.

Next, send an invite to your guests.

Add a name and then select someone from your contacts list.

Or, type an email address to invite someone not in your contacts list.

When you save your event, your invite will be sent to your guests.

They will receive an email inviting them to your picnic.

When they RSVP, you’ll be alerted with an email.

Good work! You calculated the probability of nice weather, then scheduled an event based on that data.

Now, it’s your turn: Review the weather forecast, and choose a date and time for your picnic, Open Google Calendar and create a new event, Record the date, time, and other important event details, And send an invite to guests.

In the main lesson, you learned that probability tells you the likelihood that an event will happen.

In this extension, you will make a table in Google Sheets to visualize and solve a probability problem.

Visualization makes it easier to understand a problem and develop a plan to solve it.

Imagine your class is taking a field trip on a bus.

There are ten rows of seats on the bus, and each row has four seats.

Your teacher will assign each student a seat.

You will calculate the probability of being assigned an aisle seat.

Then, you will calculate the probability of being assigned a seat beside your best friend.

To do this, you will make a map of the seating arrangement by creating a table.

To start, open a new tab in your browser, then go to Google dot com.

Select Drive, and open your probability spreadsheet.

Next, add a new sheet.

Name it.

Now, select a range of cells containing ten rows and four columns.

Start with cell B4 to leave space to label your table.

Add borders to create a grid to represent each seat on the bus.

Name the rows and columns.

Bold the numbers and titles to make them stand out.

Make the titles Window and Aisle different colors so you can tell them apart.

The rows and columns of your table represent all the available seats on the bus.

Next, calculate the likelihood of being assigned an aisle seat.

To do this, divide the number of aisle seats by the total number of seats.

Calculate the total number of seats.

Multiply the number of rows in your grid by the number of columns.

Record this information in a cell below your table.

Now, use your table to calculate the probability of being assigned an aisle seat.

In your table, two of the four columns represent aisle seats.

That means half the total available seats are on the aisle.

To calculate probability, divide aisle seats by total seats.

Add a heading, and create a formula.

Convert it to a percentage...

and round the decimal.

Nice work! Now, calculate the probability of being assigned a seat next to your best friend.

In this example, your best friend got the front row window seat on the right side of the bus.

Add a new visual element to your table to see the problem clearly.

Add a fill color for your best friend’s seat.

You can clearly see that there is only one way you can sit next to your best friend: if you have the front row aisle seat on the right side.

To calculate the probability of sitting beside your best friend, divide the number one by the total number of seats.

Remember, now that your best friend has a seat, there are 39, not 40, available seats.

Add a heading, and create a formula.

Convert it to a percentage...

and round the decimal.

Compare your results.

You have a much greater chance at getting an aisle seat than sitting next to your best friend.

There are 20 ways to get an aisle seat, but there is only one way to sit beside your best friend.

Great job visualizing and solving a probability problem in Google Sheets! Now, it’s your turn: Look at your table to visualize the problem, And create a formula to calculate probability.

In the main lesson, you learned that probability tells you the likelihood that an event will happen.

You simulated rolling the dice eleven times.

In this extension, you will see how the probability changes when you work with a larger sample size, such as 500 rolls.

You will learn the law of large numbers and use Google Sheets to test it out.

The law of large numbers says that the more times you perform the same experiment, the closer the actual results get to the predicted results.

Think about the main lesson.

The law of large numbers implies that the more times you roll the dice, the closer the actual results will come to the probability, or predicted results.

To start, open your probability spreadsheet and make a copy.

Update the title.

Your sheet shows the results for 11 rolls.

Compare the Probability column with the Actual Percentage rolled column.

Notice the differences between the predicted outcomes and the actual outcomes.

Now, see what happens when you roll the dice 500 times.

Hold down shift and select the bottom row of cells in the dice simulation columns.

Then, drag the cell handle to copy the RANDBETWEEN formulas to at least 500 more rows.

Scroll back up to the top of your sheet.

Copy the new values in the roll simulation columns.

And paste only the values in the number rolled columns.

Next, add the orange and purple dice together for the new rolls.

Copy the sum formula to at least row 502.

Now that you have simulated rolling each die 500 times and added the dice together, count how many times each outcome appeared.

First, change the title of the Number of Times column to indicate the new number of rolls.

Then, use the COUNTIF formula to calculate how many times each outcome appeared in the 500 rolls.

Update the range of the COUNTIF formula to include the new dice rolls.

The first two rows are headings, so the last row in the range is 502.

Copy the formula to the rest of the column.

Finally, change the percentage formula, since you rolled the dice 500 times instead of 11.

Change the number of rolls in the formula in the Actual Percentage Rolled column from 11 to 500.

Copy the formula to the rest of the column.

Compare the actual outcomes with the probable outcomes.

To make it easier to compare your results, hide the columns you don’t need.

After 500 rolls, the percentages of each actual outcome are closer to the probability you calculated than they were after 11 rolls.

You just demonstrated the Law of Large Numbers using Google Sheets! Now, it’s your turn: Make a new copy of your spreadsheet.

Add 500 more dice rolls in the simulation columns, Copy and paste the new values to freeze the data, Copy the SUM formula to add the new dice rolls.

Update the range in the COUNTIF formula and the total number of dice rolls in the Actual Percentage formula.

And compare the actual outcomes to the probable outcomes.

In this extension, you will format titles and tables in your spreadsheet using Google Sheets formatting tools.

Formatting makes your spreadsheet more interesting for your viewers and helps show your data more clearly.

To start, open your probability spreadsheet and make a copy.

Title it.

Then, create a title for the table you made.

Insert two rows above the table to make room for your title.

Type a title.

Merge the cells above your table.

Merging combines two or more cells into a single cell.

Next, center your title.

Make your title stand out by changing the font size...

and making it bold.

Next, choose a font style.

Experiment with different fonts to pick your favorite.

Be as formal or as fun as you want.

Then, select a text color...

and a fill color for your background.

Finally, put a border around the table.

Borders, just like text, may have different colors, sizes and designs.

Nice job! You’ve added a lot of visual interest to your table.

Now you can clearly see the table of dice combinations and your data looks more interesting as well.

Continue experimenting with fill and text colors to emphasize different parts of your data.

Now, it’s your turn: Try out formatting options on the data in your spreadsheet, Then, create and format a title for the data in your sheet by merging cells, adding a title, and changing the font style, size, and color.

Imagine that you have been chosen to manage your school baseball team.

As a manager, you are responsible for choosing the batting order of the nine members of your team.

Use your knowledge of probability to help you choose the batting order.

Batting average is an expression of probability in baseball.

It measures the likelihood of a certain event—the likelihood that a batter will get a hit.

In this extension, you will create the batting order for your school baseball team based on probability.

You will use a formula in Google Sheets to calculate batting averages and use data sorting to help organize your data.

Batting average is the number of hits divided by the number of at bats.

Batting averages are expressed as decimals with three digits— for example, point four zero zero is called a four hundred average.

A batter who averages four hundred is a better hitter than a batter who averages two hundred.

In general, you can expect a player with a four hundred average to hit the ball about forty percent of the time and a player with a two hundred average to hit the ball about twenty percent of the time.

Managers prefer high-average hitters to bat first and low-average hitters to bat last.

This allows the team to get more hits because the players at the front of the batting order bat more than those at the end.

Now, create your own batting order! To begin, open the starter project linked on this page.

Make a copy and give it a title.

Your table lists player name, position, at bats, hits, and batting average.

First, calculate batting average for each of your players.

Use a formula.

Divide the data in the Hits column by the data in the At Bats column.

Next, calculate batting average for the rest of your team.

Batting averages only have three digits.

Decrease the decimal places in each result.

There is still a zero to the left of the decimal.

Batting average does not include the zero.

Remove the zero in each result.

Good job! You have calculated and formatted your batting average data.

Now, look at your data to determine the best and worst hitters.

Currently, the order of your table is determined by the players’ first names.

Re-sort your data so that batting average determines the order of players listed in your table.

Because your table has headers, you can sort by category.

In this case, sort by the Batting Average category.

Sort batting averages in ascending order, from highest to lowest.

Z refers to the highest number, A to the lowest number.

Now the batting averages are ranked in order from highest to lowest.

Notice that the sequence of player names has changed—this is because the order of your table is now determined by batting average.

Use this sequence of player names for your batting order.

The highest-average hitters will be first, the lowest-average hitters will bat last.

Great job creating a strong batting order! Now, it’s your turn: Calculate the batting averages of your players, Sort your data, And choose a batting order.