Eliminate leading zeros from month and day fields in order to match formatting across all data sources. In this video, modify existing MID and RIGHT text functions with an IF statement to identify and remove unnecessary characters and create new, standardized text strings with the proper number of digits.
- View Offline
- [Voiceover] When you join two data sets together in Excel,…it's absolutely critical to use a field…that's identical across sources.…In this case, I'll standardize my date format…by eliminating the leading zeros…from my month and day fields.…So if I open the file 02_02_Weather,…I'll take a look at columns H and I,…which are my Month and Day fields.…Now the problem with these formulas…as they currently stand is that they use…mid and right functions to extract…two characters from my text string…in column F, which is my Date.…
If I want to eliminate the leading zero,…I need to modify those functions…to make them a bit more dynamic.…So for the Month column, column H,…what I need to do is say,…"Is the fifth character in the date string a zero?"…If so, I only want to return the sixth character.…If the fifth character is not zero,…I'd like to return the fifth and the sixth.…So to do that, I'll introduce an IF statement…before this mid function and then write out the logic.…
So IF in the middle of F2, the fifth character…
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