Join Chris Dutton for an in-depth discussion in this video What you should know before watching this course, part of Excel Workshop: Working with Real-Time Data.
- [Voiceover] Before watching this course, there are a few things you should know. But first, some expectations. You should have a strong familiarity with the Excel interface, ideally versions from 2013 and on. You should have the ability to apply basic formulas and formatting rules, and you should have a working Internet connection. Two words of warning before we get started. First is that WEBSERVICE is not available for Excel Online, 2016 for Mac, or any version of Excel prior to 2013.
Second, there are differences in Excel's user interface between Mac and PC, including shortcuts and navigation. So if you are using a Mac during this course, you will notice some of these discrepancies between what you're seeing on your screen and what I'm showing on mine.
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