Tuesday, February 4, 2020

Calculate a One Way ANOVA in Excel

If you're trying to check if survey data for different group (two or more) are truly random and nothing is suspect about them, you're probably going to use  ANOVA (Analysis of variance) to test.   This video will cover how to use Excel to perform a one way ANOVA test the easy way (using the Analysis Toolpack) and the other way in a more manual but descriptive approach.

Wednesday, January 29, 2020

Approximate Lookup with INDEX & MATCH Functions

If you wanted to do a lookup that wasn't an exact match, you're probably looking to do an approximate match lookup. There are different functions that can perform a closest match, partial match or even a fuzzy match. One of the function you can use is the INDEX & MATCH combination to find the closest value to a looked up value. This video will cover a simple example of looking up a salesperson sales quantity to figure out what kind of commision to be given.

Tuesday, January 28, 2020

Use the Excel Geography Data Type & Filled Map Feature

Learn how to use the Office 365 Geography Data Type feature along with the Filled Map charts.

If you've to the Office 365 subscription, you have access to a linked data type that goes online to pull back lots of interesting background information based on a geography value. For example if you want to know info about the population of India, just type in the country name, turn it into a geographic data type and Excel will go online and pull info like population or area size auto-magically. And you can do this for a list of countries if you're trying to do some analysis.

To add more to this Excel goodness, you can chart this data on a map with the feature called Filled Maps. It basically is a choropleth map that uses color or shade to help visualize the geographic data. Filled Maps is also an Office 365 enhancement, but it is looking to be available in Excel 2019.