# Analysis Toolpak and Descriptive Statistics

Analysis ToolPak is an Excel add-in that provides many statistical tools. This add-in comes in two flavors, and each of them has to be enabled in "Add-ins" dialog (1) in order to use it. They both have the same functionality but "Analysis ToolPak" has its functionality available only through graphical interface. "Analysis ToolPak – VBA" is accessible both through graphical interface and VBA code.

After we enable "Analysis ToolPak" (1), a button will appear in Data > Analysis (2). By clicking on that button, we will open dialog with many statistical tools (3). In order to use any of these tools we just have to select it and click on OK. New dialog will appear which provides options for that specific statistical tool.

## Descriptive Statistics

Descriptive statistics is based on a brief descriptive coefficients that quantitatively describe or summarize features of a data set. Data set is set of results of measurements made on a population or a sample. This coefficients comprise measures of central tendency like mean, median, mode, and measures of variability like Standard error, Variance, Range, Kurtosis.

We will select "Descriptive statistics" tool in Analysis ToolPak. "Descriptive statistics" dialog asks us to enter what range will be used as a source of data (1). This range, in our example, has two columns and we will get results for each of them. Program doesn't know whether data in selected range is organized in Columns or Rows. We will choose Columns (2) in our case, to solve that dilemma. Our data has labels in the first row, so we have to check option (3) in order to have first row excluded from calculations.

Final output can be directed to a range in the same sheet or some other sheet in the same workbook (A). This range is defined by it's top left cell. Option (B) means that new sheet will be created with provided name and result will be pasted in "A1" cell in that new sheet. Selection of option (C) will create new workbook with the name "Book1", and the result will be pasted in sheet "Sheet1" in cell "A1" in that workbook.

Last options (4,5,6,7) are for selecting what results should be calculated. Option (4) "Summary statistics" should always be selected because without it most of important indicators (mean, variance…) will not be part of a final result. Option (5) needs confidence level for calculating confidence interval for the mean. Without it checked, we will not get this result. Options (6,7) will give us the same results as SMALL and LARGE functions in Excel.

## Formulas used in Analysis ToolPak descriptive statistics

Bellow are listed formulas used for calculation of some descriptive indicators.

Let's calculate Confidence Interval by using its formula. Because our sample is smaller than 30 observations, we will use t distribution. For 95% confidence level, with 3 degrees of freedom, t value would be 3.1824. That means that t * s = 3.1824 * 0,75 = 2.3868. This is the same result as Excel CONFIDENCE.T function is returning. Now our confidence interval is ( 2,75 – 2,3868; 2,75 + 2,3868 ), which is ( 0,3632; 5,1368 ).

Kurtosis and Skewness are used to describe how much our distribution fits into normal distribution. If Kurtosis and Skewness are zero, or close to zero, then we have normal distribution.

## Analysis ToolPak Descriptive Statistics through VBA

### Descr Subprocedure

Different functionalities of Analysis ToolPak can be called through VBA subprocedures. Bellow we can see how to provide arguments and call procedure with the name "Descr" for descriptive statistics. All arguments except the first one are optional. All arguments are of a type Variant.

``````Sub subDescrFunction()
Dim inputRng As Range: Set inputRng = Range("Sheet1!A3:B7")
Dim outputRng As Range: Set outputRng = Range("Sheet1!K3")
Application.Run "Descr", inputRng, outputRng, "C", True, True, 1, 1, 95
End Sub``````

VBA project for Analysis ToolPak (ATPVBAEN.XLAM) is protected with the password "Wildebeest!!". There you can find declarations of all VBA procedures.

### DescrQ Subprocedure

There are two variants of subprocedures for descriptive statistics. First has name Descr, and the second one has name DescrQ. "Descr" would create result immediately by using arguments provided in the code. "DescrQ" would first open GUI dialog filled with values provided in the code (1). We can then accept those values, or we can change them, and then we can click on OK button. Only then, final results will be created (2). "DescrQ" give us opportunity to customize values provided in the code.

All the code for "Descr" and "DescrQ" is the same. Only difference is the name of subprocedure.

``Application.Run "DescrQ", inputRng, outputRng, "C", False, True, 1, 1, 95``