Sunday, July 11, 2021

Calculate a Chi Square Test with Excel

A Chi (rhymes with the word "my" but starting out with a "k" like kite) squared test is used to determine the relationship between observed and expected values that are categorical variables. This means items that are counted, so this type of test would be used for continuous variables like weight or height. Excel has some useful functions that will perform Chi squared calculations BUT you'll still need to calculate out the Chi squared test statistic based on your data. Check out the video for two examples of Chi squared - goodness of fit and test for independence.

Sunday, July 4, 2021

Perform a One Sample Proportion Test in Excel

This video covers how to perform a one sample test for proportion. This type of test used to estimate the proportion of a population. It compares the proportion to a target or reference value . Unfortunately Excel doesn't have a function in the Data Analysis Tool-pack to do this in a few steps, but it's not that hard if you lay out the calculations in a column and then re-use if needed.

Sunday, June 27, 2021

Perform Year over Year Analysis with a Pivot Table

If you're familiar with Pivot Table, it's a great analysis tool. One common type of analysis is to compare the year over year (or month over month) change or growth of items. Maybe it the comparison of the actual number value or you could want the percentage change. One option is to have your data in the pivot table ready and make the calculations outside of the table (e.g., in the next column). But if you do it that way and make changes in the Pivot Table, the outside cell calculation could not work because they might be reference different cells now. This video will look at a simple data set of car sales and see how to compare the difference and percent differences over two years and make comparisons by date (months) and even by the car make to emphasis the flexibility of Pivot Table when you make changes. You don't need to calculate the change outside of the Pivot; instead let Excel do it for you. Then all you need to to it to plug your final table into a report or presentation.