Define a new dimension to capture weather type (Light or Heavy), in order to organize and analyze the data at a more granular level. Use Excel’s VLOOKUP function with IF statements to retrieve the relevant values based on the weather conditions (Rain, Snow, Dry) as well as the amount of precipitation.
- [Voiceover] Joining data from multiple sources…is a common task in Excel,…and one that can be a nightmare without the right tools.…I'll show you how to combine VLOOKUP with an IF statement…to pull data from across multiple tabs.…So, if I open up the 03_03_Weather file in Excel,…you can see two percentile tabs.…This is where I define weather type, in column C,…based on the percentile values in column B.…What I need to do is integrate this Type column…into the raw data on my Raw_Weather_Data tab…into column O.…
The catch is that I can use VLOOKUPs,…but where I'm pulling the values from…depends on the conditions noted in column N.…So, I'll start in O1,…and add a TYPE column header,…and this is where I'll type my IF statement,…so, IF the conditions equal Rain,…then what I want to do is perform a VLOOKUP,…where my lookup value is the rainfall amount in column L,…in this case, cell L-2.…My table array is the Rain_Percentiles tab,…columns A through C.…
I'll select the entire columns,…press F4 to lock that reference in,…
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