Load the Analysis ToolPak in Excel

If you need to develop complex statistical or engineering analyses, you can save steps and time by using the Analysis ToolPak. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables.

The data analysis functions can be used on only one worksheet at a time. When you perform data analysis on grouped worksheets, results will appear on the first worksheet and empty formatted tables will appear on the remaining worksheets. To perform data analysis on the remainder of the worksheets, recalculate the analysis tool for each worksheet.

1. Click the File tab, click Options, and then click the Add-Ins category.

If you’re using Excel 2007, click the Microsoft Office Button, and then click Excel Options

2. In the Manage box, select Excel Add-ins and then click Go.

If you’re using Excel for Mac, in the file menu go to Tools > Excel Add-ins.

3. In the Add-Ins box, check the Analysis ToolPak check box, and then click OK.
   • o If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.
   • o If you are prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.

Load the Analysis ToolPak in Excel – Office Support (microsoft.com)

Excel Functions for MAT 135

randbetween (bottom, top) Gives a random number between the two numbers entered for “bottom” and “top”.  Copy this function down to create a list of random numbers.

correl (array1, array2)   Calculates the correlation coefficient between two data sets.

Data Analysis Toolpak:  correlation   Output will give you a matrix which gives the correlation of x vs. x, x vs. y and y vs y.  Only use x vs. y

Data Analysis Toolpak:  regression   Can be used to find the regression line (but using a trendline on the scatter plot is easier).  The advantage of this function is the residual plot.

average (     )   Gives the arithmetic mean for a set of given numbers.

median (     )   Gives the median for a set of given numbers.

mode.sgnl (   )   Gives the mode for a set of given numbers.    This function will return only one mode, even if multiple values are repeated the same number of times.   The mode.mult(  ) does return multiple modes but you have to have several cells highlighted and use the ctrl, shift and enter keys simultaneously.  It may almost be easier to sort your data and look for the mode or modes yourself.

min(   )  Gives the lowest value in the data set*.

max(   )  Gives the highest value in the data set*.

*The min and max functions can be combined to find the range of a data set in one step without sorting [=max(   )-min(   )].

var.p(  )  Gives the population variance.

var.s(  )  Gives the sample variance.

stdev.p (  )   Gives the population standard deviation.

stdev.s ( )   Gives the sample standard deviation.

Data Analysis Toolpak:  Descriptive Statistics    Returns many descriptive statistics such as mean, sample standard deviation and sample variance.

Note – do not use the Excel quartile functions.  To find Q1 and Q3, either sort and count or after finding the overall median (Q2), take the median of the lower half to find Q1 and the median of the upper half to find (Q3).

fact(n)   Use this function to calculate n!.

permut (number, number chosen)   Gives the number of permutations.  Number is the number of objects and number_chosen is the number taken at a time.

combin (number, number chosen)   Gives the number of combinations.  Number is the number of objects and number_chosen is the number taken at a time.

binom.dist (number_s, trials, probability_s, cumulative)   Use true or 1 for cumulative if you want the sum of the probabilities up to and including that value and false or 0 if you want the probability of that value only.

norm.s.dist (z, true or 1)   Gives area under the curve to the left of z using the standard normal distribution (mean of 0 and std dev of 1).  Use 1 – norm.s.dist for area to the right. Only use cumulative true or 1 – never use cumulative false or 0.

norm.dist (X, mean, std dev, true or 1)   Gives the normal cumulative distribution function (area under the curve to the left of X).  Use 1 – norm.dist for area to the right). Only use cumulative true or 1 – never use cumulative false or 0.

norm.s.inv (area)   Gives the inverse of the standard normal distribution (gives z when probability/area to the left is given).

norm.inv (area, mean, std dev)   Gives the inverse of the normal cumulative distribution (gives X when probability/area to the left is given).

confidence.t (alpha, standard dev, size)   Returns E for a population mean using the t distribution.  To find confidence interval, add and subtract E from the sample mean to get the upper bound and lower bound.

t.inv(probability, deg_freedom)  Returns the t critical value with the specified probability to the left.

t.dist(X, deg_freedom, cumulative)  Gives the t cumulative distribution function (area under the t distribution curve for the given degrees of freedom to the left of X).  Use 1 – t.dist for area to the right.  Can be used to find P-values. Only use cumulative true or 1 – never use cumulative false or 0.