Generate dynamic chart source data using lookup/reference and trigonometry functions in Excel. Learn how to automatically update data using INDEX and MATCH functions, and practice using SIN, COS and RADIANS formulas to generate source data that can be used to drive unique and powerful charts and visualizations.
- [Voiceover] Using functions and formulas to…generate dynamic source data is a great way to…bring your charts to life in Excel.…In this case, I'll show you how to get creative…with Index, Match, and even Trigonometry functions…to create source data that can be used to…drive a custom gauge chart visualization.…So go ahead and open up the 06_01 exercise file,…and select the Weather Dashboard tab.…This is where I'll input my dynamic source data,…and again, the goal here is to generate data…that you'll eventually use to build the gauge-style chart,…using some sort of indicator or needle…to display the current accident risk,…just like a gas gauge in a car…shows your current fuel level.…
This is a simple, but also very effective way…to visualize and communicate the level of risk,…given current weather conditions.…So we'll begin in Cell B23,…and type Current Risk,…and here in cell B24,…what you need is to populate this cell…with one of the indexed accidents rates,…but which value you choose depends on…the weather label, or current conditions.…
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