Calculate accident rates under each weather category in order to understand the relative risk of driving in dry, rainy or snowy conditions. Use Excel’s COUNTIF function to determine the average number of accidents per day under each weather type, and compare rates to quantify estimated levels of risk.
- [Voiceover] Excel's COUNTIF and SUMIF functions…allow you to summarize and compare data based…on a number of criteria.…I'll demonstrate how a simple COUNTIF functions…can be used to analyze how accident rates…change under different types of weather conditions.…So we'll open up the 04_03 exercise file,…and begin by adding a new tab.…I'll name this weather dashboard…since I'll be adding to this later on,…and what I need to do here is just insert…each of my different weather conditions.…
So dry, light rain, heavy rain,…light snow, and heavy snow.…Because I wanna compare how the accident data changes…and how the accident rate changes…under each of these types of conditions.…So now I need to pull in two things.…First is the accident count itself,…which is the number of accidents that occurred…under each of those types of conditions.…The second is days in sample,…and that will give me the number of days…that can be categorized by each of these conditions,…and using those two pieces of information.…
I can calculate accidents per day,…
Using conditional statements and text functions like LEFT, MID, RIGHT, and CONCATENATE, you'll learn to standardize data sets from the NCDC and create new dimensions. Then find out how to use VLOOKUP to join the data, and use COUNTIF statements to analyze trends. Once the historical data is in place, Chris shows how to use Excel's powerful WEBSERVICE and FILTERXML functions to tap directly into Weather Underground's API, which provides real-time weather information for any location and allows you to estimate accident rates based on current conditions.
Last but not least, you'll learn how to build custom charts to show the expected accident risk based on real-time inputs, and walk through some additional opportunities for deeper analysis.
- Standardizing data
- Creating new data dimensions fields
- Joining data
- Analyzing trends in the data with COUNTIF
- Creating an API feed with WEBSERVICE
- Extracting components with FILTERXML
- Visualizing the findings in a custom combo chart
- Applying your new skill set