Descriptive Statistics in Excel

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

 

Welcome to Spider Financials’ Getting started Series Module 2: Descriptive Statistics. In Module 1: Orientation, I gave you a brief overview of NumXL’s Forms and Functions. In this Module, I will use the Descriptive Statistics Form to compute basic statistics.

For this module, and for the remaining modules of this Series, I will use the Monthly log-returns for the S&P 500 ETFs (aka their SPIDERs) between Jan 3rd, 2000 and Oct 1st, 2010. This S&P-500 data is publicly available online from Finance Yahoo. We’ll begin by calculating nine different summary statistics for the S&P 500 Spiders using NumXL’s Descriptive Statistics Form. Like all of NumXL’s forms, the Descriptive Statistics Form returns functions, Each referencing worksheet cells so that they can be easily updated. Let’s get Started!

First, you can find the shortcuts for NumXL's Forms on your Ribbon in Excel 2007 and 2010. Or, for Excel 2003, you can find these same shortcuts on the NumXL toolbar or under NumXL on the main menu. Click the Descriptive Stats shortcut to launch its Form.

Before you do anything else, select your time series data, In this case the SPIDER Monthly log-returns. To do this, click/select the input control in the top frame of this form, And select your data. You will also need to specify how your data has been chronologically ordered. Since the Spider sample data is sorted so that the oldest entries are at the top, I can leave the “Ascending” checkbox checked.

Now, let's take a look at the “Statistics” frame. Here you can see all the summary statistics this form returns. For this module, I’ll leave all of the boxes checked so I can demonstrate all the functions.

Take a quick look at the Statistical Testing frame. For this module, I am un-checking all of these tests, Since I will go over them in Module 3.

Please note – the cell I selected before launching the Descriptive Statistics Form was automatically entered in the Output Range Ref-Edit box at the bottom of the form. This is true for all NumXL forms. If you want to change the output cell after launching the form, Use the Output Range Ref Edit control to select the cell you want. Click OK to return the Summary Statistics for the SPIDER Monthly-log return sample data!

Look over the Descriptive Statistics output table: (1) The order of the output rows is the same as the order of the checkboxes in the form. (2) All of NumXL’s forms use our add-in functions or Excel’s built in functions, and reference the sample data range. (3) As a result, the forms output will be automatically updated when you update your sample data.

This concludes Module 2. If you have any questions, concerns or feedback please call our toll-free number at 1-888-427-9486 or email us at Support@SpiderFinancial.com. We look forward to working with you!

Have more questions? Submit a request

2 Comments