Convert a range of values into a scale with custom-defined minimum and maximum values. Understand the importance of using a custom scale to help facilitate interpretation of the data, and learn how they can be used as dynamic source data for charts and graphs with pre-set minimum and maximum constraints.
- [Voiceover] Converting an existing range of values…into a new custom scale, like 1-10 or 0-100, is a powerful…way to both standardize and facilitate interpretation…of your data.…So, when you create a custom range you…can convert existing values using the following formula.…The new value will be a function of the new minimum…and maximum that you choose, as well as the original value…and the old, or orginal minimum and maximum values.…So if, for instance, you'd like to create a custom range…from 0-10, your new minimum would become zero and your…new maximum would become a 10.…
In this case I'll show you how to use named ranges…and max/min functions to convert values into a scale…from one to nine.…So I'll open up my Excel file 04_04, and I'll begin…by selecting all of the values in the range that I want…to create the new scale for.…These are my accident rates in row five.…So, to make things simpler, in the name box…in the upper left, I'm just gonna type rates, that way…I don't have to refer to the specific array from B five…
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