Basic Statistics in Excel

Quick reference

• 00:04 Hi, I'm Ray Sheen, you know as we start to do more statistical analysis,
• 00:09 I want to make sure that you're able to do the calculations using Microsoft Excel.
• 00:14 So let's get everything set up and
• 00:16 we'll calculate the descriptive statistics of a dataset.
• 00:21 When using Excel to do the statistical analysis,
• 00:23 you will need to use their Data Analysis menu item.
• 00:27 Microsoft Excel has already preprogrammed
• 00:30 many of the statistical analyses that we'll need to do for an SPC charting.
• 00:35 You will find them under the Data Analysis menu item in the Data ribbon.
• 00:39 It's located at the far right of the Data ribbon.
• 00:43 However, if you're running Excel out of the box,
• 00:46 you probably won't see that item on your Data ribbon.
• 00:49 Don't worry, it's there in your version of Excel, it's just hidden away.
• 00:54 Let's look at how we can bring that up on to the Data ribbon.
• 00:58 Go to the File drop-down menu at the top left corner of your application.
• 01:02 When you select File,
• 01:03 you will see an item near the bottom of the list called Options, select it.
• 01:09 This will bring up a panel similar to the one you see here.
• 01:12 On the left sidebar of this panel, select Add-ins.
• 01:16 The window in the center of the panel will have a list of the number of items that
• 01:19 you can select as an Add-in.
• 01:21 We want the one called Analysis ToolPak, highlight that item.
• 01:26 Don't worry about cost or permissions, this feature is already in Excel.
• 01:30 You're just bringing it up onto the menu to make it easy to access it.
• 01:35 Now, select OK at the bottom right corner of that panel.
• 01:39 If you go back into your Excel worksheet, and
• 01:41 check the Data ribbon, the data analysis menu item should be there now.
• 01:45 We'll start to use the data analysis menu items with a basic,
• 01:50 but very useful item called descriptive statistics.
• 01:54 The descriptive statistics function does the calculations that we did in an earlier
• 01:59 lesson for mean, median, and standard deviation.
• 02:02 In fact, it does these and a few more that we don't need for SPC charting.
• 02:07 Let's look at how to use this function.
• 02:09 Start by selecting the Data Analysis menu item on the Data ribbon.
• 02:14 This will bring up a list if statistical analysis tools,
• 02:18 like you see in the panel on the right.
• 02:20 This list is set up alphabetically, so it's easy to find the tool called
• 02:24 Descriptive Statistics, select that one and click OK.
• 02:29 This now brings up a panel where we can tell Excel what you want to do.
• 02:33 The first thing is to put in the cell range that contains the data
• 02:36 from your data set.
• 02:38 Do that in the field labeled Input Range.
• 02:41 Then decide where you want Excel to put the results.
• 02:44 You can put them in a new worksheet or at a designated cell in the open worksheet.
• 02:49 Now that's what I chose to do and I designated the cell as P1,
• 02:52 you can use whatever cell you choose.
• 02:56 Next you need to tell excel what kind of descriptive statistics you want.
• 03:00 For SPC, it will always be Summary Statistics.
• 03:04 Finally, click OK in the upper right corner of the Descriptive Statistics panel
• 03:09 and Excel will place your results wherever you designated.
• 03:13 So now let's look at what Excel is telling us.
• 03:16 The statistics are in the table.
• 03:18 You can use the statistics to quickly determine whether you
• 03:21 are working with a normal dataset or non normal data.
• 03:25 We see that it has calculated some of the dataset metrics we already discussed,
• 03:29 such as the mean or an average value.
• 03:31 The median, or center point of the data,
• 03:34 when the data is ordered from smallest to largest.
• 03:37 The range, which is the spread from the smallest to the largest.
• 03:41 And then the standard deviation,
• 03:43 which was our measure of the typical spread in the data.
• 03:47 It also provides skewness, which is a measure for
• 03:50 whether the data is centered or is offset to one side or the other.
• 03:55 An ideal value is 0.
• 03:57 Normal curves should be near 0.
• 04:00 The further away from that value, the more likely the data is non-normal.
• 04:04 Another great measure of normalcy is Kurtosis.
• 04:08 This is a measure of central tendency,
• 04:10 In essence, it checks whether the center of the curve is higher than the edges or tails.
• 04:15 A normal curve should have light tails that are approaching 0.
• 04:20 Excel does something a little funky with this measure.
• 04:23 The ideal value for Kurtosis is 3, but you probably won't remember that.
• 04:28 So, Excel subtracts the dataset Kurtosis value from 3, and reports that.
• 04:33 This is often called the Sample Excess Kurtosis.
• 04:36 This means if the data was perfectly normal,
• 04:39 meaning the Kurtosis was 3, the Sample Excess Kurtosis would be 0.
• 04:44 By doing this, Excel lets you treat Kurtosis just like you treat skewness,
• 04:49 the closer to 0 the better.
• 04:51 Excel's Descriptive Statistics function in the Data Analysis
• 04:56 menu provides useful information for determining if you have normal data.
• 05:01 Now if you plan to use Excel for your analysis,
• 05:05 make sure you have the Data Analysis add-in ready to go.

Lesson notes are only available for subscribers.