In this video, Chris challenges you to take the analytics skills and key concepts covered in this course and apply them in new contexts. Consider how real-time data could be used to supplement a financial analysis, how lookup and reference functions might be used to join student attendance records and test scores, or how text formulas could help HR professionals categories employee data.
- [Voiceover] Alright, you've wrapped up the course…and now hopefully have a new set of Excel skills…at your disposal.…At this point, what you need to keep in mind…is that what you've built here isn't just a tool…to analysis accident rates and real time weather data.…It's a platform that you now have the skillset to apply…to the data that's most meaningful to you.…Interested in finance or investing?…Trying building your own tool…to track real time market performance or stock quotes.…If you're a teacher, use the v-lookup…and index match functions that you've learned…to explore correlations between student attendance rates…and test scores.…
If you work in HR or recruiting…try apply the text functions that you used in this project…to organize and categorize recruits or employees.…The point is the opportunities are literally endless…and I'm challenging you to take these skills…and apply them in new and interesting ways.…Analyze the data around you.…Explore new ways to visualize that data.…And then share your projects with the world.…
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