Simple & Easy: Descriptive Statistics in Excel

Forum to talk about all the latest technologies - ins and outs!!
Post Reply
Admin
Site Admin
Posts: 325
Joined: Tue Sep 25, 2007 12:41 pm

Simple & Easy: Descriptive Statistics in Excel

Post by Admin »

If you want a quick summary of key statistical parameters (like mean, standard deviation, sum etc.) for your data set, the best and easiest way is to use Excel's data analysis tool called Descriptive Statistics.

Where do you find this tool? Go to Data tab > here you should find the Data Analysis button. There you will find this tool - Descriptive Statistics.


In many cases, if it's not already installed with Excel, you have to activate this from the Analysis ToolPak under Add-Ins from the Option. Very easy. Don't worry!! At the end of this tutorial, I have explained it thoroughly how to activate Analysis ToolPak Add-In for statistical and engineering purposes.

Now, we assume, you have this tool active under Data tab :-)


Here I have an example of oil prices from January 2001 to January 2002. I want to generate a summary statistics for my data.

Go to Data tab > click Data Analysis > select Descriptive Statistics > click OK.


Image


Descriptive Statistics dialog box opens.

Here under Input Range highlight your range or cells (B3:B15). But if you want to include the heading Oil Price, then highlight the range from B2:B15 and check Labels in first row (otherwise you will get an error message - ' Descriptive Statistics – Input range contains non-numeric data.').

Under Output Range > select cell D4.

Click Summary statistics > then OK.


Image


So the output of your work is:


Image


Well, here you see Mode #N/A. Why?


Mode is the number that is repeated more often than any other in your data. So let me change the oil price for Jun 01 to 65 and do the analysis again. So what you see? This time Mode is 65 (it's repeated more than any other values in my data).


Image




Extra: How to activate Analysis ToolPak Add-In

The Analysis ToolPak is an Excel Add-In for data analysis. So let's activate this add-in:

In Excel 2003 and earlier versions, you get it from the Tools menu > click Add-Ins... > click Analysis ToolPak from the Add-Ins dialog box.

In Excel 2007 > click on the Office Button > click on Excel Options (In Excel 2010 > Green File > Options)


Image


Now under Add-ins > select Analysis ToolPak > click on the Go button. The Add-Ins window opens.

Here check Analysis ToolPak > click OK.


Image


Now click on the Data tab > click on Data Analysis.


Image


The Data Analysis window opens.

Select Descriptive Statistics > click OK.


Image


The Descriptive Statistics dialog box opens (second image in this tutorial). Now you can start analyzing your data.

Remember working with Excel is always a fun, so don't sit stressed in your chair. Just play with Excel. Do these things, I mean like trial-and-error, several times, it will make you a real expert. Keep in mind: 'Practise makes a man perfect!!'

So, have fun :-)
Post Reply