A likely story

You have estimated the severity of the consequences, but you still need to calculate the likelihood of the event occurring to measure risk. This will help you make decisions about which precinct is the riskiest place to drive (and consequently which one could use more support).

To calculate how likely crashes are in each region, you'll want to divide the number of crash-related injuries in the precinct by the number of vehicles involved in crashes. After calculating likelihood and consequences, you can use the matrix described in the video to calculate total risk to determine which precinct needs the most help.

Remember that the SUMIF() function is useful for summing up only the events that meet certain criteria. Remember to specify the range to check, the criterion, and the range to sum.

This exercise is part of the course

Error and Uncertainty in Google Sheets

Exercise instructions

In cell E2, estimate the likelihood of a crash-related injury by dividing the total number of injuries in the precinct by the total number of vehicles crashed in the precinct. Be sure to use an the correct function to sum by precinct!
Calculate the probabilities of crash-related injuries for the respective precincts by copying your formula down to cells E3:E10.
Calculate the risk associated with each crash in cells F2:F10.

Hands-on interactive exercise

Turn theory into action with one of our interactive exercises

This exercise is part of the course

Error and Uncertainty in Google Sheets

IntermediateSkill Level

4.6+

Start Course for Free

The first chapter presents common terminology, introduces methods for determining significant differences between groups, and outlines the kinds of error and uncertainty involved. We will specifically look at Seattle crime data and evaluate crime rate differences between precincts and neighborhoods. This chapter will equip learners to identify threats to the validity and accuracy of their conclusions.

Exercise 1: Defining error and uncertainty Exercise 2: Measures of central tendency Exercise 3: Crime time Exercise 4: IF functions Exercise 5: Extracting UNIQUE() values Exercise 6: Book 'em and count 'em Exercise 7: Averages and IF conditions Exercise 8: Counts with multiple criteria Exercise 9: Correlation Exercise 10: Rap sheet Exercise 11: Correlation preparation Exercise 12: A (crimes) committed relationship Exercise 13: Strong relationships

The second chapter outlines both rudimentary (e.g., moving average, seasonal average, yearly average) and more complicated methods (e.g., linear regression) for making predictions and outlines the kinds of error and uncertainty involved. We will specifically look at anonymized student grades data and evaluate the accuracy of our predictions for given students. Throughout the chapter, we will identify threats to the validity and accuracy of our predictions.

Exercise 1: Making the grade Exercise 2: We all have our (central) tendencies Exercise 3: Variable weights Exercise 4: Now weight a minute Exercise 5: Lying in weights Exercise 6: Advanced prediction strategies Exercise 7: What's in the FORECAST()?Exercise 8: Variation in predictions Exercise 9: Seems about right Exercise 10: How clear is your crystal ball?Exercise 11: Prediction accuracy Exercise 12: Absolute deviation Exercise 13: Average absolute deviation Exercise 14: Statistical significance Exercise 15: Significant differences Exercise 16: Significant differences of opinion

Chapter 3 encourages learners to test the assumptions of their predictions using data on car crashes. Specifically, they will determine how to allocate resources to reduce injuries and fatalities from auto accidents. Learners will discuss the impact of outliers in prediction accuracy, evaluate the importance of normally distributed data in making predictions, employ consequence-likelihood matrices in risk management, and adapt psychological heuristics to discussions of numerical uncertainty and risk.

Exercise 1: Outliers Exercise 2: Down and outlier Exercise 3: No filter Exercise 4: Addressing outliers Exercise 5: Sparklines Exercise 6: Can't start a fire without a spark(line)Exercise 7: The max matters Exercise 8: What's the worst that could happen?Exercise 9: There are consequences Exercise 10: A likely story

Current Exercise

Exercise 11: Risky business Exercise 12: Risky business Exercise 13: Random numbers Exercise 14: How random Exercise 15: Be fruitful and multiply Exercise 16: Revisiting sparklines

The final chapter integrates all the previous lessons into a constructed-world scenario. Learners are tasked with updating the menu at their small business: the Risky Business Bakery. They need to figure out whether to add or drop menu items based on whether there are significant differences in sales by baked good; whether their predicted sales figures from their accountant are accurate.

Exercise 1: A half-baked idea?Exercise 2: Half-baked ideas Exercise 3: Falling on hearth times Exercise 4: Summary statistics Exercise 5: Do you know your muffins, man?Exercise 6: Changing prices Exercise 7: Bread on the rise?Exercise 8: Paying the price Exercise 9: Is change on the menu?Exercise 10: A recipe for change Exercise 11: Rain, rain, go away Exercise 12: Fed up Exercise 13: Review: Are we certain now?Exercise 14: Adding variation Exercise 15: Win some, lose some Exercise 16: Just t-testing Exercise 17: Wrap-up