Create percentile scales to explore and analyze the distribution of both rainfall and snowfall precipitation amounts. Use Excel’s “remove duplicates” tool and PERCENTRANK function to calculate percentiles and assign weather types (Light or Heavy) based on where each precipitation value falls along the scale.
- View Offline
- [Voiceover] Alright, so sometimes when you're…working with data, you'll need to create…buckets or categories based on numerical values.…Calculating percentiles allows you to create…logical groupings based on the distribution…of your data points.…Here, I'll show you how to create a percentile scale…to generate new sub-categories of data.…So I'll go ahead and open up the…03_02_Weather Excel file and what you'll see in column M…is a Conditions field and what this field does…is basically categorize weather for any given day…based simply on the presence of precipitation.…
So if there are values in columns K or L.…Now, what if you wanna dig deeper than that?…What if you wanna distinguish between…light rain and heavy rain…or light snow and heavy snow?…This is where calculating percentiles is a great tool…which will allow you to define new sub-categories of data…based on these actual precipitation amounts.…But first, quick side note, you may have noticed something…a little bit strange, which is that the snow field…
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