Brief tutorial for calculating summary statistics in Excel using NumXL functions and wizards.
Tutorial
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!
Comments
Hello,
I have studied your explanations about kernel density estimation on this page :
http://www.spiderfinancial.com/support/documentation/numxl/users-guide/descriptive-statistics/kernel-density-estimation-kde-tutorial
and I also watched it in YouTube channel :
https://www.youtube.com/watch?v=C_QP3AYG4Lc
I have some data that I want o estimate their distribution with kernel, but I am using Microsoft office excel 2007 and there isn' t any icon named Descriptive Statistics in my toolbar. and also there isn't any function named KDE in my functions!
so I want to ask you which version of excel had you used?and how I should create this function in my functions? :(
Hello Azimeh, I believe this is an installation or licensing issue.
Please, see the following articles to help you with this issue:
(1) NumXL Toolbar is missing?
http://support.numxl.com/entries/23470411-I-can-t-see-NumXL-toolbar-or-functions-after-install
(2) NumXL Post-Installation Checklist
http://support.numxl.com/entries/22922692-Post-installation-checklist
(3) NumXL worksheet functions are missing and inaccessible from the fx (Insert function) wizard.
http://support.numxl.com/entries/24801998-No-NumXL-functions-in-the-fx-menu-insert-function-
If you still have issues, please contact our support line via email (support@numxl.com) or by phone to better help you.
Please sign in to leave a comment.