Tuesday, March 3, 2020

Perform a One Sample t-test in Excel (for Mean)



This video cover two ways to perform a student t test for a one sample mean. A common statistical method to test a null hypothesis against an alternate hypothesis. It's used when you are comparing quantitative (and continuous) sample data against a given population mean.

Saturday, February 29, 2020

Convert a Table with a Row Section Header Fields



Do you get reports that have a common row that acts as a header field? It may have other column header fields that make it look like a proper table but there is a row above like the title "Name" and the cell next to it is the actual name of someone. This report is visually okay because it kind of creates sections of table data separated by that row Name-Value pair, but it makes it harder to do any further analysis if you wanted to put is into a Pivot Table or do some aggregation for charts. You could create some formulas, perform copy/paste to fix this, but if this is a large report or something done often, it might be better to use Power Query to transform it into a proper table.

Thursday, February 27, 2020

Use PowerQuery to Change the Order of Cell Values or Text


If you've tried to rearrange the order of text or data in cells within Excel, you're probably familiar with the LEFT, MID and RIGHT functions. The combination of these functions help you reverse text or first and last names. You can rearrange the data of almost any kind of content in your cells with these function. Incidentally there are formulas in the Power Query M code that can do the same thing and this video will cover two examples.