Category Archives: Statistics & Probability

What’s the expected value of your Powerball ticket?

As the Powerball jackpot grows to over $300 million, we start to wonder if maybe buying a ticket is “worth it.” While the lottery is “worth it” in that ticket sales goes to things like state education, buying tickets is typically not worth it for yo because the projected payoff is far less than the ticket price.

The concept of Expected Value is a central idea in probability and statistics and refers to a weighted average outcome. For our Powerball example, the expected value equals the probability of getting each combination of winning numbers, multiplied by the payoff of the combinations.

Spreadsheet Inputs

The Powerball website has a table with the odds and the payouts of each winning combination of numbers, making our job relatively easy.

image

We can transcribe these into a spreadsheet:

image

Spreadsheet output

There isn’t much logic to this one. First we convert the odds column into probability, by taking one over the odds plus one.

image

Then we multiply the probabilities with the respective payouts by using the Sumproduct() Function:

image

Since Powerball tickets cost $2, it seems we’re still losing about 65 cents on each ticket even with the jackpot above $300 million.

It looks even worse given the $302 million is the simple sum of annuity payments, while the cash value is much less at $187.3 million.

image

That takes us down to 96 cents.

image

Taking into account a 35% tax rate would then take us down to 62 cents. And then there’s the chance that there are multiple jackpot winners, resulting in a split (we won’t get into that one here, but check out this fivethirtyeight post for more). Finally, let’s use the Goalseek function in excel to see when it becomes worth it to buy a lottery ticket taking into account a 35% tax bracket.

Pull up Goalseek from the Data Tab –> What-If Analysis –> Goal Seek

image

Then we set the after tax expected value cell (D16) to $2, by changing the jackpot cell (B5). Basically Goalseek is Excel’s functionality for “guessing and checking”, for those who are not as algebraically inclined…

image

The answer? $806 million! And that isn’t accounting for potential splits which is pretty likely once the jackpot gets that high.

image

Use a spreadsheet to check Matthew Berry’s Top 200 Fantasy Football Rankings results from Week 1

“It’s hard to make predictions, especially about the future”  Various

Usually, improvement in prediction-making comes in two steps.  Step 1: Make a Prediction.  Step 2: Evaluate how accurate the prediction was, and learn from it.  Often times, Step 2 can get overlooked as we move on to future predictions and future weeks of fantasy football.  Spreadsheets can help us quickly evaluate how our predictions were, and quickly point out where we might have erred.

Continue reading

What are the chances a Little League baseball player gets to the Major Leagues someday?

baseballIt’s that time of the year when Little League World Series coverage heats up on ESPN, and viewers get to see a miniaturized version of baseball played at the highest level. This year has been especially impressive with a super-team from Las Vegas that rarely makes errors, the Chicago team that beat them, and of course the sensational female pitcher Mo’ne Davis, who throws as hard as some high school pitchers. Every Little Leaguer dreams of making it to the big leagues, someday making a career of playing the game they love.  But how hard is it to get there? This is a question that we will try to solve this week with the help of a spreadsheet.

Continue reading

Predicting a Tennis Pro’s Weight based on Height

TechsmithWorEE2E.pngWith a Grand Slam approaching, let’s talk tennis! If we were to predict a tennis pro’s weight based on his height, where would we begin? How will our understanding of the best-fit line and spreadsheets help us make this prediction?

We’ve collected the heights and weights of tennis pros including Federer, Djokovic, Nadal, Murray, Azarenka, Sharapova, and Williams, along with another 192 top players. Let’s investigate how to 1) calculate the correlation of weights and heights, and 2) draw a best fit-line and scatter plot in Google spreadsheets to extrapolate or make predictions!

Continue reading

Probabilities & Dice Roll Simulations in Spreadsheets

Dice

Photo: Amie Chuang

What is the probability of rolling any pair of numbers with two dice? Let’s first solve this and then confirm our calculated probability by simulating 500 dice rolls with a spreadsheet! In this post, we will focus on understanding basic probability concepts and then discover how with spreadsheets, we can actually see whether our calculated probability holds true!

Continue reading

Breaking it down: Sample Standard Deviation

We have a set of data and want to understand its characteristics. A great starting point is to measure the central or typical value and the dispersion around that value. In this post, we will focus on the latter – specifically a standard measure of spread known as the sample standard deviation!

Continue reading

Hypothesis Testing with Spreadsheets – Part II

Is the “height success rate” for seeds grown in organic soil significantly higher than that for those grown in the non-organic soil? Let’s use a statistical test to find out! But before we delve in, let’s review the amazing Central Limit Theorem (CLT). Why so remarkable, you may ask? Continue reading

Hypothesis Testing with Spreadsheets – Part I

seedlings2

Seedlings for Experiment. Photo: Amie

What is the impact of organic soil on a seedling’s height? Will an organically-grown seedling be taller than one grown in non-organic soil? Armed with data, we’ll now tackle these questions by performing a statistical test in a spreadsheet!

To see how we our experimental design and data collection method, please see: www.spreadsheetsolving.com/posts/experiment-spreadsheet Continue reading