Excel Functions

=AVERAGE(number 1, [number 2], …)=MEDIAN(number 1, [number 2], …)=MODE.SNGL(number 1, [number 2], …) number 1 is the first number, cell reference, or range of cells for which to calculate the specified value. [number 2],… represents additional numbers, cell references, or ranges of cells. The square brackets indicate that the argument is optional.  For example, if we had the data set {0.5, 0.5, 1.5, 3.0, 4.0}, we could find the mean by entering =AVERAGE(0.5, 0.5, 1.5, 3.0, 4.0) into a cell. This would calculate the average, 1.9. More often when using Excel, we won’t input data directly into the formula, but will instead input a range of cells. For example if cell A1=0.5, A2=0.5, A3=1.5, A4=3.0, and A5=4.0, we could calculate the mean of the data set by entering =AVERAGE(A1:A5), which would return 1.9. Because the mean is the sum of all data points, divided by the number of data points, we could also enter =SUM(A1:A5)/COUNT(A1:A5). The COUNT function counts the number of cells that contain numerical values so in this case =SUM(A1:A5)/COUNT(A1:A5) is equivalent to =SUM(A1:A5)/5.MODE.SNGL replaces the function: =MODE(number 1, [number 2], …) Throughout the course we will provide alternative functions that existed prior to Excel 2010 that can still be used in Excel 2010.

To calculate mean, median, mode:

To find the correlation coefficient in Excel, we use the following function: =CORREL(array 1, array 2) array 1 is a set of numerical variables or cell references containing data for one variable of interest. array 2 is a set of numerical variables or cell references containing data for the other variable of interest. Note that the number of observations in array 1 must be equal to the number in array 2.

Find Corellation Coefficient in Excel:

Drill Down: Excel Functions for Distributions with Multiple Modes The Excel function MODE.MULT finds all of the modes in a data set, generating a vertical array that has one row for each mode in the data set. Suppose a data set has three modes. To find them, instead of entering the MODE.MULT formula in a single cell, highlight at least three vertically-contiguous cells and then input =MODE.MULT(number 1, [number 2], …) into the formula bar. Then, instead of using ENTER to find the result, use CTRL+SHIFT+ENTER to enter the array. The modes will appear in the first rows of the array (filling as many rows as there are modes in the data set) and #N/A will appear in each of the other rows in the array.  How do we know how many vertically-contiguous cells to highlight when using this function?  To determine how many modes are in a data set, enter =COUNT(MODE.MULT(number 1, [number 2], …)) in a single cell. When you click ENTER, the function will return the number of modes in the data set. The result will tell you how many vertical cells you should highlight to create a MODE.MULT array. Note that our embedded spreadsheet currently does not support the functionality required by MODE.MULT.

Excel Functions for Distributions with Multiple Modes

=AVERAGEIF(range, criteria, [average_range]) range contains the one or more cells to which we want to apply the criteria or condition. criteria is the condition that is to be applied to the range. [average_range] is the range of cells containing the data we wish to average.

CONDITIONAL MEANS

To find a percentile in Excel, we use the following function: =PERCENTILE.INC(array, k) array is the range of data for which we want to calculate a given percentile. k is the percentile value. For example, if we want to know the 95th percentile, k would be 0.95. ********************************************************************** PERCENTILE.INC replaces the function: =PERCENTILE(array, k) Throughout the course we will provide alternative functions that existed prior to Excel 2010 that can still be used in Excel 2010.

Find a Percentile in Excel:

Variance and Standard Deviation

To calculate the variance or standard deviation of a sample in Excel, we can use the following functions: =VAR.S(number 1, [number 2], …) =STDEV.S(number 1, [number 2], …) number 1 is the first number, cell reference, or range of cells for which to calculate the specified value. [number 2],… represents additional numbers, cell references, or ranges of cells. The square brackets indicate that the argument is optional.   Note that the “S” in VAR.S and STDEV.S indicates that we are working with a sample. We will learn more about the differences between samples and populations in the next module. We can also find the standard deviation using the Excel function =SQRT(number) to take the square root of the variance. For example, =SQRT(16)=4.***********VAR.S and STDEV.S replace the functions: =VAR(number 1, [number 2], …) =STDEV(number 1, [number 2], …)

Excel Descriptive Statistics Tool

Excel has a descriptive statistics tool that provides a number of summary statistics, including those we’ve already learned, for a set of data. Let’s walk through how to create it. Step 1 From the Data menu, select Data Analysis, then select Descriptive Statistics. Step 2 Enter the appropriate Input Range: The Input Range is the oil consumption data in column A with its label, A1:A11. Make sure to include A1, the cell containing the label, when inputting your range and check the Label in first row box, as this ensures that your output table will be appropriately labeled. Step 3 Enter the appropriate Output Range, in this case enter D1. This cell is the top left hand cell in which the output table will appear. Step 4 Be sure to select Summary Statistics so that the output table is generated.

Excel Descriptive Statistics Tool 2

Assuming data is in A2:A11:Mean:                                 =AVERAGE(A2:A11)Standard Error:               =STDEV.S(A2:A11) / SQRT(COUNT(A2:A11))  [This is also called the Standard Deviation of the Mean]Median:                             =MEDIAN(A2:A11)Mode:                                =MODE.SNGL(A2:A11)Standard Deviation:     =STDEV.S(A2:A11)Sample Variance:           =VAR.S(A2:A11)Kurtosis:                           =KURT(A2:A11)Skewness:                        =SKEW(A2:A11)Range:                               =MAX(A2:A11) - MIN(A2:A11)Minimum:                        =MIN(A2:A11)Maximum:                       =MAX(A2:A11)Sum:                                  =SUM(A2:A11)Count:                               =COUNT(A2:A11)

Let's practice using the RAND function. To ease navigation, we’ll use a population of only 25 phone numbers. Step 1 Before we generate random ID numbers, type “Random ID” in cell A1 to label column A. Step 2 In cell A2, enter the function =RAND() to generate a random ID number between 0 and 1. Step 3 Copy and paste the function from cell A2 into cells A3:A26 so that all 25 phone numbers are assigned a random ID number. You can use auto-fill instead of copying and pasting. Step 4 Now we need to sort the phone numbers. Highlight the data in column A and column B, excluding the labels, and select Sort Ascending from the Data menu. Note that the RAND function generates a random number for each phone number every time the spreadsheet is calculated. Therefore, even though the phone numbers actually were sorted, the (new) random numbers will not appear in order. The sorting was based on the previously assigned random numbers. After sorting, the 25 phone numbers on the list are in random order. If we wanted to draw a random sample of 10 phone numbers, we would start at the top of the list and choose the first 10 people.

RAND() Function in Excel

=NORM.DIST(x, mean, standard_dev, cumulative) x is the value at which you want to evaluate the distribution function. mean is the mean of the distribution. standard_dev is the standard deviation of the distribution. cumulative is an argument that specifies the type of probability we wish to calculate. We insert “TRUE” to indicate that we wish to find the cumulative probability, that is, the probability of being less than or equal to the x-value. (Inserting the value “FALSE” provides the height of the normal distribution at the value x, which we will not cover in this course.) Suppose we want to know the percentage of women who are shorter than 63 inches. Since the mean is 63.5 inches, we can estimate that less than 50% are shorter than 63 inches. How do we calculate the exact percentage? We could use the previous interactive, but it’s much more convenient to know how to use Excel’s functionality. To find a cumulative probability, the probability of being less than a specified value on a normal curve, we use Excel’s NORM.DIST function.* Usually for less than or equal to.* For greater than, use 1 - NORM.DIST(x, mean, stdev, cumulative)

NORM.DIST function

For a standard normal curve, we know the mean is 0 and the standard deviation is 1, so we could find a cumulative probability using =NORM.DIST(x,0,1,TRUE). Alternatively, we use Excel’s NORM.S.DIST function =NORM.S.DIST(z, cumulative). The "S" in this function indicates it applies to a standard normal curve. z is the value (the z-value) at which we want to evaluate the standard normal distribution function. cumulative is an argument that specifies the type of probability we wish to calculate. We will insert “TRUE”.

NORM.S.DIST()

Another way to find the cumulative probability for a normal distribution is to use the functions STANDARDIZE and NORM.S.DIST. The STANDARDIZE function “standardizes” the data by returning the z-value, using the familiar equation, z=x−μσz=x−μσ. Note that the term “standardize” reinforces our interpretation of the z-value as the number of standard deviations from the mean.=STANDARDIZE(x, mean, standard_dev) x is the value to be standardized. mean is the mean of the distribution.  standard_dev is the standard deviation of the distribution. After standardizing, we can insert the resulting z-value into the NORM.S.DIST function to find the cumulative probability of that z-value. 

CALCULATING THE Z-VALUE

The Normal Function: NORM.INV

=NORM.INV(probability, mean, standard_dev) probability is the cumulative probability for which we want to know the corresponding x-value on a normal distribution. mean is the mean of the distribution.  standard_dev is the standard deviation of the distribution. =NORM.S.INV(probability) probability is the cumulative probability for which we want to know the corresponding x-value on a standard normal distribution. For example, if we wanted to know the z-value for the 95th percentile on a standard normal curve, we would enter =NORM.S.INV(0.95)=1.645. Equivalently, we could enter =NORM.INV(0.95,0,1)=1.645.