Descriptive Statistics in Excel

Brief tutorial for calculating summary statistics in Excel using NumXL functions and wizards.

Video script

Scene 1:

Module 2 descriptive statistics. In this module, I will use the descriptive statistics form in NumXL and compute several statistics for a given sample data.

Scene 2:

For this module and for the remaining modules of this series I will use the monthly log returns for the S&P 500 ETF also known as spider for the period between January 3rd, 2000 and October 1st, 2010. This data is freely available online at finance.yahoo.com.

Let's begin by calculating nine different summary statistics for the spider's monthly returns using the NumXL descriptive statistics form.

Scene 3:

Similar to all the NumXL forms, descriptive statistics returns its results as formulas into your workbook allowing the values to be easily updated as input data change.

Scene 4:

Now, click on the descriptive statistics shortcut to launch our form.

Scene 5:

Now, select the time series sample data, in this example the spider monthly log returns. To do this click on the top frame of this form and select your data.

Scene 6:

You will also need to specify how your data has been chronologically ordered. Since our sample data is sorted so that the oldest entries are at the top, I can leave the ascending checkbox checked.

Next, let's take a look at the statistics frame. here you can see all of the summary statistics which this form can return. For this demo, I'll leave all of the boxes checked to demonstrate those functions.

Take a quick look at the statistical testing panel. We will go over statistical testing in module three.

For now, let's uncheck all these tests. Look at the output range edit box, it references the active or selected cell in our workbook before we launched the form. This behavior is true for all NumXL forms. If you want to change the output cell range use the Edit control and select the cell where you wish the output to be returned.

Scene 7:

Click OK, and the summary statistics output table will be inserted in our workbook. Let's look at the output table.

First, note the order of the outputs matches the order of the checkboxes in the form.

Second, the formulas and the output are referencing the sample data and using NumXL or excel functions. As a result, the output table will be recalculated as you modify sample data.

Scene 8:

This concludes module 2, if you have any questions, concerns or feedback please call us at toll-free number 1(312) 324-0367 or email us at support@numxl.com. We look forward to working with you!