Excel

One-Way

1. Make sure you have the Data Analysis ToolPak installed.

2. Enter the data for the different groups into separate columns.

   

3. Navigate to the Data tab, and select Data Analysis in the Analyze section. In the dialogue box that appears, select Anova:Single Factor and click OK.

4. 1. Enter the Input Range (in this example, cells A2:C5).

   2. Enter a value for Alpha.

   3. Select Output Range and enter a cell in the spreadsheet for Excel to enter the results.

   

5. Click OK.

   

Fisher's LSD

Note: These instructions assume a sample size of 4. Adjust the rows in the instructions to fit your data.

1. Put the data for the samples in columns A, B, and C.

   

2. Follow the instructions to calculate a One-Way ANOVA and place the output in cell E1.

   

3. In cell B8, type the formula "=T.INV(probability, degrees of freedom)" using the following information.

   1. Enter a probability of 1-α/2.

   2. Enter degrees of freedom n_τ - k, where n_τ is the total number of observations and k is the number of treatments.

   3. For this example, α is 0.05 and n_τ - k is 9, so the formula is "=T.INV(0.975,9)".

4. In cell B9, calculate Fisher's LSD for comparing the means of Salesperson 1 and Salesperson 2 by typing "=B8*SQRT(H13*((1/COUNT(A2:A5))+(1/COUNT(B2:B5))))".

5. In cell B10, calculate $\left|{\stackrel{\_}{x}}_i-{\stackrel{\_}{x}}_j\right|$ for comparing Salesperson 1 to Salesperson 2 by typing "=ABS(H5-H6)".

   

6. Compare cell B10 to B9 to determine if the means are different for Salesperson 1 and Salesperson 2.

Tukey's HSD

Note: These instructions assume a sample size of 4. Adjust the rows in the instructions to fit your data.

1. Put the data for the samples in columns A, B, and C.

   

2. Follow the instructions to calculate a One-Way ANOVA and place the output in cell E1.

   

3. Use a table to find the q critical value corresponding to α, the number of treatments k, and the degrees of freedom n_τ - k, where n_τ is the total number of observations. Enter this value in cell B8.

   For this example, α is 0.05, the number of treatments is 3, and the degrees of freedom are 9, so the associated q critical value is 3.949.

4. In cell B9, calculate Tukey's HSD for comparing the means of Salesperson 1 and Salesperson 2 by typing "=B8*SQRT((H13/2)*((1/COUNT(A2:A5))+(1/COUNT(B2:B5))))".

5. In cell B10, calculate $\left|{\stackrel{\_}{x}}_i-{\stackrel{\_}{x}}_j\right|$ for comparing Salesperson 1 to Salesperson 2 by typing "=ABS(H5-H6)".

   

6. Compare cell B10 to B9 to determine if the means are different for Salesperson 1 and Salesperson 2.

Two-Way

1. Make sure you have the Data Analysis ToolPak installed.

2. Enter your data in the following format.

   	A	B	C	D	E
   1		25-35	35-45	45-55	55-65
   2	0-5	22	25	34	37
   3		27	35	36	43
   4	5-10	34	35	42	49
   5		36	45	48	51
   6	10-15	39	40	53	51
   7		41	50	57	59

3. 1. Navigate to the Data tab, and select Data Analysis in the Analyze section.

   2. In the dialogue box that appears, select either Anova:Two-Factor With Replication (Factorial Design) or Anova: Two-Factor Without Replication (Randomized Block Design), depending on the wording of the problem.

   3. Click OK.

4. 1. Enter the Input Range (in this example, cells A1:E7).

   2. Enter the number of Rows per sample.

   3. Enter a value for Alpha.

   4. Select Output Range and enter a cell in the spreadsheet for Excel to enter the results.

   

5. Click OK.

   

Binomial Probability (pdf)

1. Type the formula "=BINOM.DIST(number of successes (x), number of trials (n), probability of success (p), FALSE)". Press Enter.

   Note: You may enter a 0 in place of FALSE.

   

   In the example pictured, the student was asked to compute the probability of 2 successes out of 4 trials, with each trial having a 1/6 probability of success.

Binomial Probability Distribution

1. Enter the values 0 to n in column A.

2. In cell B1 type the formula "=BINOM.DIST(A1, number of trials (n), probability of success (p), FALSE)". Press Enter.

   Note: You may enter a 0 in place of FALSE.

3. Drag cell B1 down to calculate the probabilities for all values of x in column A.

   

   In the example pictured, the student was asked to compute the probability distribution of 4 trials, with each trial having a 1/6 probability of success.

Binomial Probability (cdf)

1. Type the formula "=BINOM.DIST(number of successes (x), number of trials (n), probability of success (p), TRUE)". Press Enter.

   Note: You may enter a 1 in place of TRUE.

   

   In the example pictured, the student was asked to compute the probability of 2 or fewer successes out of 4 trials, with each trial having a 1/6 probability of success.

Critical Value

1. Type the formula "=CHISQ.INV.RT(probability, degrees of freedom)". Press Enter.

   The chi-square critical value with area to the right equal to the probability entered is returned.

   

   In the example pictured, the student was asked to compute the critical value of a chi-square distribution with 19 degrees of freedom and a significance level of 0.05.

Left Tailed Probability (cdf)

Used for finding the p-value corresponding to a ${\mathsf{\chi }}^2$ test statistic.

1. Type the formula "=CHISQ.DIST(x, deg_freedom, TRUE)". Press Enter.

   The area to the left of the value x is returned.

   

   In the example pictured, the student was asked to compute the probability that a chi-square random variable with 17 degrees of freedom is less than 6.3.

Right Tailed Probability (cdf)

Used for finding the p-value corresponding to a ${\mathsf{\chi }}^2$ test statistic.

1. Type the formula "=CHISQ.DIST.RT(x, deg_freedom)". Press Enter.

   The area to the right of the value x is returned.

   

   In the example pictured, the student was asked to compute the probability that a chi-square random variable with 12 degrees of freedom is more than 5.9.

Test for Association

Note the cell numbers given in these instructions are based on a test of four categories that are divided into two groups. To perform a test on a different number of categories or groups, use the appropriate number of columns and rows.

1. Enter the given contingency table of observed values in cells A1 through F4.

2. Calculate the expected value for each cell in the contingency table.

   1. In cell B7 type the formula "=B$4*$F2/$F$4".

      Note that the $ symbol is used to “lock” the column or row that follows the $ when a formula is copied from one cell and pasted into another.

   2. Click on the bottom right corner of cell B7, and drag the cursor down to cell B8.

      Highlight cells B7 and B8. Click on the bottom right corner of cell B8, and drag the cursor right to cell E8.

3. Calculate the p-value.

   In cell B9 type the formula "=CHISQ.TEST(B2:E3,B7:E8)".

   

Test for Goodness of Fit

Note the cell numbers given in these instructions are based on a test on four categories. To perform a test on a different number of categories, use the appropriate number of columns.

1. Enter the observed value and the expected value for each category in cells B1 through E3 as follows.

   1. In cells B1 through E1 enter the category name.

   2. In cells B2 through E2 enter the observed value for the category above.

   3. In cells B3 through E3 enter the expected value for the category above.

2. Calculate the test statistic (χ²).

   1. Compute ${\displaystyle \frac{{\left(\text{Observed Value}-\text{Expected Value}\right)}^2}{\text{Expected Value}}}$ for each category.

      1. In cell B4 enter: =(B2-B3)^2/B3

      2. Drag across the formula in B4 to cells C4:E4.

   2. Add together all the values computed in part (a).

      1. In cell B6 enter: =SUM(B4:E4)

3. Calculate the χ² critical value.

   1. In cell B7 enter: =CHISQ.INV.RT(alpha, df)

      1. Substitute a value for ‘alpha’.

      2. ‘df’ is the number of categories minus 1.

   

Proportion

1. In cells B2, B3, and B4 enter the number of successes, number of observations, and alpha, respectively.

2. 1. Compute the sample proportion by typing "=B2/B3" in cell B5. Press Enter.

   2. Compute the standard deviation by typing "=SQRT(B5*(1-B5))" in cell B6. Press Enter.

   3. Compute the critical value by typing "=CONFIDENCE.NORM(B4, B6, B3)" in cell B7. Press Enter.

3. To find the confidence interval, subtract and add the critical value to the sample proportion.

   

   In the example pictured, the student was asked to construct the 95% confidence interval for the population proportion of a normal population if the number of successes is 48 and the number of observations is 410.

t-Interval

1. Type the formula "=CONFIDENCE.T(alpha, standard_dev, size)". Press Enter.

   The error of estimation is returned.

   

2. To find the confidence interval, subtract and add the error of estimation to the sample mean.

   

   In the example pictured, the student was asked to construct the 90% confidence interval for the population mean of a normal population if the sample standard deviation is 900, the sample mean is 425, and the sample size is 100.

z-Interval

1. Type the formula "=CONFIDENCE.NORM(alpha, standard_dev, size)". Press Enter.

   The error of estimation is returned.

   

2. To find the confidence interval, subtract and add the error of estimation to the sample mean.

   

   In the example pictured, the student was asked to construct the 90% confidence interval for the population mean of a normal population if the population standard deviation is 900, the sample mean is 425, and the sample size is 100.

Two Sample Proportions z-Interval

1. Type the formula "=NORM.S.INV(confidence level)*SQRT((p1*(1-p1))/n1+(p2*(1-p2)/n2))". Press Enter.

   Note that the confidence level used in the function 1 – (alpha/2).

   The error of estimation is returned.

   

2. To find the confidence interval, subtract and add the error of estimation to the point estimate.

   

   In the example pictured, the student was asked to construct the 95% confidence interval for the difference in two population proportions if the sample proportions are 0.05 and 0.04 for Population 1 and Population 2, respectively. The sample size for each sample proportion is 200.

Two Sample t-Interval (Dependent Samples, Paired Difference)

1. In column A, enter the data from Sample 1. In column B, enter the data from Sample 2.

2. In cell C1, type the formula "=A1-B1". Press Enter.

   Drag cell C1 down to calculate all of the paired differences.

   Note, double-clicking on the bottom right corner of cell C1 will auto-fill the appropriate formula for the entire column.

3. See Descriptive Statistics, One Variable to calculate the sample mean, standard deviation, and sample size of the data in column C.

4. See Confidence Intervals, t-Interval to calculate the confidence interval.

Combination

1. Type the formula "=COMBIN(total number of items, number chosen)". Press Enter.

   

   In the example pictured, the student was asked to compute the number of ways to select 5 items out of 36 total items, disregarding the order of the items.

Factorial

1. Type the formula "=FACT(number)". Press Enter.

   

   In the example pictured, the student was asked to compute 5!.

Permutation

1. Type the formula "=PERMUT(total number of items, number chosen)". Press Enter.

   

   In the example pictured, the student was asked to compute the number of ways to select 3 items out of 7 total items, where the order of the items matters.

Filtering

1. Making sure to reserve the first row for headers (or otherwise left empty), enter the data in a column. (If your data covers multiple parameters, then enter the data for each parameter in separate columns.)

2. Select the header/empty row and use one of the following methods:

   1. Under Home > Editing click Sort & Filter > Filter.

   2. Under Data > Sort & Filter click Filter.

3. Click the small arrow in the column you wish to filter by to access your filtering options.

   1. A list will be shown of all the unique values in the column (up to 10,000). When the checkbox for a value is selected, the rows where this column has that value will be displayed. You can select any number of these checkboxes at any time.

   2. There are also Number Filters or Text Filters, depending on the type of data, that are useful for selecting multiple values that fit a certain criteria.

Sorting

1. Enter the values in a single column and select the data.

2. Use one of the following methods to sort the data:

   1. Right-click and select Sort > Sort Smallest to Largest, or Sort > Sort Largest to Smallest.

   2. Under Home > Editing, choose Sort & Filter > Sort Smallest to Largest or Sort & Filter > Sort Largest to Smallest.

   3. Under Data > Sort & Filter, choose or .

Subset Calculations

One Variable

1. Make sure you have the Data Analysis ToolPak installed.

2. Enter the data arranged in a column.

3. Navigate to the Data tab, and select Data Analysis in the Analyze section. In the dialogue box that appears, select Descriptive Statistics and click OK.

   

4. 1. Enter the Input Range by clicking and dragging your cursor over every cell containing data.

   2. Check the option for Summary Statistics.

   3. Select Output Range and enter a cell in the spreadsheet for Excel to enter the results.

   

5. Click OK.

   

Two Variable

Grouped Data

1. Enter the data in two separate columns: midpoints in column A and frequency values in column B.

2. Use the formula: =SUMPRODUCT([midpoint array], [data array])/SUM([data array]).

3. Press Enter.

   

Weighted Mean

1. Enter the data in two separate columns: data values in column A and weights in column B

2. Use the formula: =SUMPRODUCT([data array], [weight array])/SUM([weight array])

3. Press Enter.

   

Critical Value

1. Type the formula "=F.INV.RT(probability, numerator degrees of freedom, denominator degrees of freedom)". Press Enter.

   The F critical value with area to the right equal to the probability entered is returned.

   

F-Probability (cdf)

Left Tail Probability

1. Type "=F.DIST(x, numerator degrees of freedom, denominator degrees of freedom, cumulative)". Press Enter.

   Note, in the last term of the formula, type TRUE for cdf (cumulative) or FALSE for pdf.

2. The area to the left of the x value is returned.

   

Right Tail Probability

1. Type "=F.DIST.RT(x, numerator degrees of freedom, denominator degrees of freedom)". Press Enter.

2. The area to the right of the x value is returned.

   

Qualitative Frequency Distribution

1. Select the column of data you wish to create a frequency distribution with, including the column header.

2. With the data highlighted, under the Insert tab, click PivotTable.

   

3. In the PivotTable dialogue box, make sure that the correct range of data is selected, and select the location where you want your PivotTable to appear. Click OK.

   

4. Now, a blank PivotTable will appear in the specified location, and a pane titled PivotTable Fields will be shown on the right side of the window. The name of the highlighted column will appear in the upper portion of the Fields pane.

5. To create a frequency distribution of the qualitative variable you selected, drag the column name in the upper part of the Fields pane down to the area in the lower part of the pane with the label Rows. In the PivotTable, you will now see a list of the possible unique values within the data you selected.

   

6. Now, drag the same column name from the upper portion of the pane to the lower portion with the label Values. Make sure the variable in the Values area is summarized by count. This can be specified by clicking the dropdown arrow next to the variable name and opening the Value Field Settings dialogue box. Once the correct functions are set, the count, or frequency, of each unique value in your selected data column will now be displayed in the table, thus making it a qualitative frequency distribution.

   

Quantitative Frequency Distribution

1. Select the column of data you wish to create a frequency distribution with, including the column header.

2. With the data highlighted, under the Insert tab, click PivotTable.

   

3. In the PivotTable dialogue box, make sure that the correct range of data is selected, and select the location where you want your PivotTable to appear. Click OK.

   

4. Now, a blank PivotTable will appear in the specified location. When you click on the PivotTable, a pane titled PivotTable Fields will be shown on the right side of the window. The name of the selected data column will appear in the upper portion of the Fields pane.

5. To create a frequency distribution of the quantitative variable you selected, drag the column name in the upper part of the Fields pane down to the area in the lower part of the pane with the label Rows. In the PivotTable, you will now see a list of the possible unique values within the data you selected. If your data is continuous, this table will probably have more rows than desired. This will be fixed when we group the data at the end.

   

6. Now, drag the same column name from the upper portion of the pane to the lower portion into the box with the label Values. Make sure the variable in the Values box is summarized by count. This can be specified by clicking the dropdown arrow next to the variable name in the Values box and opening the Value Field Settings dialogue box. Select Count and click OK.

   

7. Once the correct functions are set, the count, or frequency, of each unique value in your selected data column will now be displayed in the table. However, since our data is continuous, we need to group the data by creating bins. Right-click on a cell in the Row Labels column of the PivotTable, and select the Group option.

8. In the Group dialogue box, specify your desired starting and ending values as well as the class width, denoted by the term By:.

   

9. Click OK. Now, your data should be grouped into classes, and the frequency count of each class should be displayed in the PivotTable.

   

Bar Charts

1. Organize the data into 2 columns, the labels on the left and the values for each label on the right.

   

2. Select all the data. Then, under the Insert tab, in the Charts group, select the Bar Chart symbol. Choose either 2-D Column > Cluster Column (vertical) or 2-D Bar > Cluster Bar (horizontal).

   

3. You may edit the chart title by clicking on the text of the title.

   

4. To add axis labels, click on the chart to display the Chart Design tab. Use Add Chart Element to select Axis Titles > Primary Horizontal or Axis Titles > Primary Vertical.

   

Side by Side Bar Charts

1. Organize the data into columns with the labels for the groups that you want to appear on the x-axis in the first (leftmost) column and the values for each group label in the columns to the right with labels for the legend in the first row.

   

2. Select all the data. Then, under the Insert tab, in the Charts group, select Recommended Charts. Choose the Clustered Column chart shown.

   

3. You may edit the chart title by clicking on the text of the title. To add an axis title, click anywhere on the chart, select the + icon to the right of the chart, check Axis Titles, and then Primary Horizontal and Primary Vertical. You may edit the axis titles by clicking on the text of the title.

   

Stacked Bar Charts

1. Organize the data into columns with the labels for the groups that appear on the x-axis in the first row and the data values for each label in the column below. The labels for the legend should be in the first column.

   

2. Select all the data. Then, under the Insert tab, in the Charts group, select Recommended Charts. Choose the Stacked Column chart shown.

   

3. To change the colors used in the graph, right-click on the appropriate portion of the bar/column, select Fill and choose the desired color. Repeat for the other segments of the bar/column.

   

4. You may edit the chart title by clicking on the text of the title. To add an axis title, click anywhere on the chart, select the + icon to the right of the chart, check Axis Titles, and then Primary Horizontal and Primary Vertical. You may edit the axis titles by clicking on the text of the title.

   

Box Plot

1. Organize the data for each box plot in a separate column.

   

2. To create the box plot, select Insert and then Recommended Charts. Go to the All Charts tab and select Box & Whisker, then OK.

   

3. You may edit the chart title by clicking on the text of the title.

   

4. If you are displaying more than one box plot, then you should add a legend. Using the Chart Design tab, Add Chart Element, and select Legend. You have several options of where to place the legend on the chart.

   

5. The value of 1 along the horizontal axis can be removed by selecting it and deleting it.

   

Choropleth Map

1. Organize the data into two columns, the geographic regions on the left and the data values corresponding to each region on the right.

   

2. Highlight all the data in both columns. Then, under the Insert tab, in the Charts group, select Maps. Choose the Filled Map chart shown.

   

3. You may edit the chart title by clicking on the text of the title.

   

Dot Plot

1. Organize the data for your dot plot into a single column.

   

2. Highlight the entire column. Under the Home tab, click the Sort & Filter dropdown (on the right side of the toolbar) and select Sort Smallest to Largest. The smallest values should now be at the top of the column.

   

3. Create a new column next to your data column titled "Frequency". In the first cell of the Frequency column, enter the number 1. In the second cell, enter the following formula (cell references may vary depending on the location of the data in your spreadsheet).

   =IF(A3=A2,B2+1, 1)

   

4. Since our data is sorted, identical values will be in adjacent cells. The above formula will count the number of occurrences of each value. Once you have finished typing the formula into the cell, press Enter, and then double-click the small box at the lower right corner of the cell to apply the formula to the whole column.

   

5. Now, we will create a scatter plot using our two columns. Highlight all the data in both columns (excluding column headers), then navigate to the Insert tab, and insert a Scatter plot.

   

6. Right-click on the horizontal axis, select Format Axis, and then change the Bounds to fit the range of your data (choose values slightly lower than the minimum and higher than the maximum to leave a small cushion of whitespace on either side). You can also specify the increment of the x-axis by changing the Major Units variable.

   

7. To remove the y-axis and the grid lines, right-click on the vertical axis, select Format Axis, and then Delete.

   

8. To remove the grid lines, right-click on a vertical grid line in the chart area, select Delete; repeat a similar process to delete the horizontal grid lines.

   

9. Move the cursor over the middle of the bottom side of the chart area until the double arrow appears. Click and drag to resize the graph so that our dots appear to be stacked on top of one another.

   

10. You may edit the chart title by clicking on the text of the title. To add a horizontal axis title, click on the chart, select the + icon to the right of the chart, check Axis Titles, and then Primary Horizontal. You may edit the horizontal axis title by clicking on the text of the title.

    

Histogram

1. Organize the data into a column.

   

2. Select all the data (excluding the column header). Then, under the Insert tab, in the Charts group, select the Histogram symbol.

   

3. To adjust the width of the bins/classes, right-click on the horizontal axis, select Format Axis, and then change the Bin width to a desired value. There is also an option to adjust the histogram by indicating the Number of bins. Note: The bins are automatically labelled using intervals with ) or ] to indicate the sorting for the endpoints.

   

4. You may edit the chart title by clicking on the text of the title. To add an axis title, click on the chart, select the + icon to the right of the chart, check Axis Titles, and then Primary Horizontal and Primary Vertical. You may edit the axis titles by clicking on the text of the title.

   

5. If you want to display the values for the column frequencies, click on the chart, select the + icon to the right of the chart, check Data Labels, and then select the location for the label to appear.

   

Line Graph

1. Organize the data in adjacent columns, so that corresponding paired data values are in the same row. The values that will be graphed along the horizontal axis appear in the first (left) column, and the values for the vertical axis appear in the second (right) column. (The labels are not required.)

   

2. Select the second (right) column of data (excluding the column header). Then, under the Insert tab, in the Charts section, select the Insert Line or Area Chart and the 2-D Line.

   

3. After inserting the graph, right-click on the horizontal axis data labels and choose Select Data. Under the Horizontal (Category) Axis Labels, press Edit and select the range of data in the first (left) column. Click OK and OK.

   

4. You may edit the chart title by clicking on the text of the title. To add a horizontal axis title, click on the chart, select the + icon to the right of the chart, check Axis Titles, and then Primary Horizontal and Primary Vertical. You may edit the axis titles by clicking on the text of the title.

   

Multivariate/Multidimensional

1. Organize three columns of quantitative data where the first (left) column will be graphed on the horizontal axis, the second (middle) column will be graphed on the vertical axis, and the third (right) column will be represented by the size of the bubbles.

   

2. Select the data (excluding the column headers). Then, under the Insert tab, in the Charts section, select Insert Scatter (X,Y) or Bubble Chart and choose Bubble chart.

   

3. Adjust the scale on the axes to “zoom” in on the ranges of the displayed data.

   Right-click on the vertical axis, select Format Axis, and then change the Bounds to fit the range of your data (choose values slightly lower than the minimum and higher than the maximum to leave a small cushion of whitespace). You can also specify the increment of the x-axis by changing the Major Units variable.

   Repeat this process to adjust the horizontal axis, if necessary.

   

4. If the bubbles overlap too much, you can scale them down by right-clicking the bubbles, selecting Format Data Series, and reducing the value in Scale bubble size to.

   

5. You may edit the chart title by clicking on the text of the title. To add a horizontal axis title, click on the chart, select the + icon to the right of the chart, check Axis Titles, and then Primary Horizontal and Primary Vertical. You may edit the axes by clicking on the text of the title.

   

Normal Probability Plot

1. Enter a column header (label for the data) in A1 and the data below in a single column starting in cell A2.

   

2. Select Column A. In the Editing portion of the Home menu, use Sort & Filter to Sort Smallest to Largest.

   

3. Starting in cell B2, assign a rank to each row of data. A shortcut uses the formula "=B2+1" in cell B3. Press Enter and drag down for all your rows of data. The last entry in this column is equal to n, the number of data points.

   

4. In cell C2, type the formula "=(B2-0.5)/n", replacing n with the number of data points, which can be found as the last entry in the previous column (see Step 3). This will calculate the percentile. Press Enter and drag down for all your rows of data.

   

5. In D2 type the formula "=NORM.INV(C2,0,1)" to calculate the z-score corresponding to each percentile. Press Enter and drag down for all your rows of data.

   

6. Highlight column A. Then hold the Ctrl key and highlight column D. Under the Insert tab, in the Charts section, select the Insert Scatter and the 2-D Line.

   

7. After inserting the graph, right-click on the vertical axis data labels and choose Format Axis. Under Axis Options > Horizontal axis crosses, select Axis value and enter the smallest value shown on the current vertical axis.

   

8. To add a trendline, click on the chart, select the + icon to the right of the chart, check Trendline, and then Linear. The data should closely follow a linear trendline if it is approximately normally distributed.

   

Pareto Chart

1. Organize the data into two columns, the labels on the left and the data values corresponding to each label on the right.

   

2. If the values are not sorted, then select the data in both columns (excluding the column headers), choose Sort & Filter, Custom Sort. Sort the data by the column with the data values (Tickets Sold in the example) and sort largest to smallest.

   

3. Once the data is sorted, select all the data and then under the Insert tab, in the Charts group, select the Bar Chart symbol. Choose either 2-D Column > Cluster Column (vertical) or 2-D Bar > Cluster Bar (horizontal).

   

4. You can edit the Chart Title by clicking on it and typing in a new title.

   

5. To add axis labels, use the Chart Design tab, Add Chart Element and select Axis Titles > Primary Horizontal or Axis Titles > Primary Vertical.

   

Pie Chart

1. Organize the data into 2 columns, the labels of the categories in the left column and the data values corresponding to each label in the right column. Format these values as percentages if you intend to label the percentages on the graph.

   

2. Select all the data, including the column headers. Then, under the Insert tab, in the Charts group, select the 2-D Pie chart.

   

3. To add labels showing the percentages, right-click on the pie chart and select Add Data Labels.

   

4. You may edit the chart title by clicking on the text of the title.

   

Scatterplot

1. Organize the data in adjacent columns so that corresponding paired data values are in the same row. Typically, the independent/explanatory variable is located in the first (or left) column and the dependent/response variable is located in the second (or right column).

   

2. Select the data (excluding the column headers). Then, under the Insert tab, in the Charts section, select the Scatter chart.

   

3. Adjust the scale on the axes to “zoom” in on the ranges of the displayed data.

   Right-click on the vertical axis, select Format Axis, and then change the Bounds to fit the range of your data (choose values slightly lower than the minimum and higher than the maximum to leave a small cushion of whitespace). You can also specify the increment of the x-axis by changing the Major Units variable.

   Repeat this process to adjust the horizontal axis, if necessary.

   

4. You may edit the chart title by clicking on the text of the title. To add a horizontal axis title, click on the chart, select the + icon to the right of the chart, check Axis Titles, and then Primary Horizontal and Primary Vertical. You may edit the axis titles by clicking on the text of the title.

   

Time Series

1. See Line Graph.

Hypergeometric Distribution

1. Type the formula:
   HYPGEOM.DIST(
   number of successes (x),
   number of trials (n),
   number of successes in the population (k),
   population size (N),
   TRUE for the probability of at most x successes/FALSE for the probability of getting exactly x successes)

   Note: You may enter a 1 in place of TRUE and a 0 in place of FALSE.

   

   In the example pictured, the student was asked to compute the probability of getting exactly 2 successes out of 5 trials from a population with 16 successes and a size of 30.

z-Test

1. Enter the summary statistics in cells B1 through B4 as follows.

   1. In cell B1 enter the sample mean, x̄.

   2. In cell B2 enter the population mean, µ.

   3. In cell B3 enter the population standard deviation, σ.

   4. In cell B4 enter the sample size, n.

2. Calculate the test statistic (z).

   1. In cell B6 enter: =(B1-B2)/(B3/SQRT(B4))

3. Calculate the p-value. Enter the appropriate formula below in cell B7.

   1. For a left tailed test: =NORM.S.DIST(B6, TRUE)

   2. For a right tailed test: =1-NORM.S.DIST(B6, TRUE)

   3. For a two-tailed test: =2*(1-NORM.S.DIST(ABS(B6), TRUE))

   

t-Test

1. Enter the summary statistics in cells B1 through B5 as follows.

   1. In cell B1 enter the sample mean, $\stackrel{\_}{x}$.

   2. In cell B2 enter the population mean, µ.

   3. In cell B3 enter the sample standard deviation, $s$.

   4. In cell B4 enter the sample size, $n$.

   5. In cell B5 enter the alpha level, $\alpha$.

2. Calculate the test statistic (t).

   1. In cell B7 enter: =(B1-B2)/(B3/SQRT(B4))

3. Compute the t critical value with n−1 degrees of freedom.

   1. For a left tailed test: =T.INV(B5, B4-1)

   2. For a right tailed test: =T.INV(1-B5, B4-1)

   3. For a two-tailed test: =T.INV.2T(B5, B4-1)

4. Compute the p-value with n−1 degrees of freedom. The example shown below is a one-tailed test.

   1. For a one-tailed test: =T.DIST.RT(ABS(B7), B4-1)

   2. For a two-tailed test: =T.DIST.2T(ABS(B7), B4-1)

   

One Proportion z-Test

1. Enter the summary statistics in cells B1 through B3 as follows.

   1. In cell B1 enter the sample proportion, $\hat{p}$.

   2. In cell B2 enter the population proportion, $p$.

   3. In cell B3 enter the sample size, $n$.

2. Calculate the test statistic (z).

   1. In cell B5 enter: =(B1-B2)/SQRT(B2*(1-B2)/B3)

3. Calculate the p-value. Enter the appropriate formula below in cell B6.

   1. For a left tailed test: =NORM.S.DIST(B5, TRUE)

   2. For a right tailed test: =1-NORM.S.DIST(B5, TRUE)

   3. For a two-tailed test: =2*(1-NORM.S.DIST(ABS(B5), TRUE))

   

Two Proportion z-Test

1. Enter the statistics for each variable in cells B2 through C3.

2. Calculate ${\hat{p}}_1$ and ${\hat{p}}_2$.

   1. In cell B4 enter: =B2/B3

   2. In cell C4 enter: =C2/C3

3. Calculate $\stackrel{\_}{p}$, $1-\stackrel{\_}{p}$ and the test statistic (z).

   1. In cell B6 enter: =(B2+C2)/(B3+C3)

   2. In cell B7 enter: =1-B6

   3. In cell B8 enter: =(B4-C4-0)/SQRT(B6*B7*(1/B3+1/C3))

   Note that the ‘0’ in the formula above is the presumed value of the difference between the two population means from the null hypothesis.

4. Calculate the p-value. Enter the appropriate formula below in cell B9.

   1. For a left tailed test: =NORM.S.DIST(B8, TRUE)

   2. For a right tailed test: =1-NORM.S.DIST(B8, TRUE)

   3. For a two-tailed test: =2*(1-NORM.S.DIST(ABS(B8), TRUE))

   

Two Sample t-Test (Dependent Samples, Paired Difference)

1. Make sure you have the Data Analysis ToolPak Add-in installed.

2. Enter the data for Variable 1 in column A and the data for Variable 2 in column B.

3. Navigate to the Data tab, and select Data Analysis in the Analyze section. In the dialogue box that appears, select t-Test: Paired Two Sample for Means and click OK.

4. 1. Enter the Variable 1 Range, the Variable 2 Range, and the Hypothesized Mean Difference.

      Note that Excel calculates the paired differences by subtracting the values for Variable 2 from the values for Variable 1, which is the opposite of what we do when we calculate them by hand or using a TI-83/84 Plus calculator.

   2. Select Labels if the first cells in your variable ranges are data labels.

   3. Enter a value for Alpha.

   4. Select Output Range and enter a cell in the spreadsheet for Excel to enter the results.

   

5. Click OK.

   

Two Sample t-Test (Independent Samples)

1. Enter the statistics for each variable in cells B2 through C4. Enter a value for alpha in cell B5.

2. Compute the test statistic by entering the following formula in cell B7.

   1. If assuming equal variances: =(B2-C2-0)/(SQRT(((B4-1)*B3^2+(C4-1)*C3^2)/(B4+C4-2))*SQRT(1/B4+1/C4))

   2. If assuming unequal variances: =(B2-C2-0)/(SQRT((B3^2/B4)+(C3^2/C4)))

   Note that the '0'' in the formulas above is the presumed value of the difference between the two population means from the null hypothesis.

3. Compute the t critical value with n₁ + n₂ − 2 degrees of freedom.

   1. For a left tailed test: =T.INV(B5, B4+C4-2)

   2. For a right tailed test: =T.INV(1-B5, B4+C4-2)

   3. For a two-tailed test: =T.INV.2T(B5, B4+C4-2)

4. Compute the p-value with n₁ + n₂ − 2 degrees of freedom. The example shown below is a one-tailed test.

   1. For a one-tailed test: =T.DIST.RT(ABS(B7), B4+C4-2)

   2. For a two-tailed test: =T.DIST.2T(ABS(B7), B4+C4-2)

   

Two Sample z-Test

1. Make sure you have the Data Analysis ToolPak Add-in installed.

2. Enter the data for Variable 1 in column A and the data for Variable 2 in column B.

   

3. Navigate to the Data tab, and select Data Analysis in the Analyze section. In the dialogue box that appears, select z-Test: Two Sample for Means and click OK.

4. 1. Enter the Variable 1 Range and the Variable 2 Range.

   2. Enter the Hypothesized Mean Difference, the Variable 1 Variance (known), and the Variable 2 Variance (known).

   3. Select Labels if the first cells in your variable ranges are data labels.

   4. Enter a value for Alpha.

   5. Select Output Range and enter a cell in the spreadsheet for Excel to enter the results.

   

5. Click OK.

   

Two Sample F-Test

1. Enter the statistics for each variable in cells B2 through C3. Additionally, enter an alpha value in cell B4.

2. Compute test statistic (F).

   1. In cell B6 enter: =B2/C2

3. Compute the F critical value(s). Enter the appropriate formula below in cell B7.

   1. For a left tailed test: =F.INV(B4,B3-1,C3-1)

   2. For a right tailed test: =F.INV.RT(B4,B3-1,C3-1)

   3. For a two-tailed test, calculate both the left and right critical values (using Excel formulas shown above) using half of the original alpha value for each.

   4. Calculate the p-value. Enter the appropriate formula below in cell B8. The example shown below is a left tailed test.

      1. For a left tailed test: =F.DIST(B6,B3-1,C3-1, TRUE)

      2. For a right tailed test: =F.DIST.RT(B6,B3-1,C3-1)

      

Kruskal-Wallis Test

Note: These instructions assume 3 samples with sizes of 6, 5, and 7. Adjust the rows and columns in the instructions to fit your data.

1. Put the data for the samples in columns A, C, and E (leaving blank columns in between the samples where we will put the ranks in the next step).

2. Determine the ranks (from smallest to largest) for each of the three samples and put them in columns B, D, and F, respectively.

   1. In cell B2 type the formula "=RANK.EQ(A2,($A$2:$A$7,$C$2:$C$6,$E$2:$E$8),1)".

      Click the small square at the lower-right corner of the cell and drag it down to the bottom row so that the ranks are calculated for all of the values in the first column.

   2. In cell D2 type the formula "=RANK.EQ(C2,($A$2:$A$7,$C$2:$C$6,$E$2:$E$8),1)".

      Click the small square at the lower-right corner of the cell and drag it down to the bottom row so that the ranks are calculated for all of the values in the second column.

   3. In cell F2 type the formula "=RANK.EQ(E2,($A$2:$A$7,$C$2:$C$6,$E$2:$E$8),1)".

      Click the small square at the lower-right corner of the cell and drag it down to the bottom row so that the ranks are calculated for all of the values in the third column.

3. Compute the sum of the ranks R₁, R₂, and R₃.

   1. In cell B10 type the formula "=SUM(B2:B8)".

   2. In cell D10 type the formula "=SUM(D2:D8)".

   3. In cell F10 type the formula "=SUM(F2:F8)".

4. Enter the sample size for the first sample, n₁, in cell B11 under the value for R₁. Do the same thing for the sample sizes for the other two samples in cells D11 and F11.

5. Enter the squared sum of ranks divided by the sample size, R₁²/n₁, for the first sample by typing '=(B10^2)/B11' in cell B12. Do the same thing for the other two samples in cells D12 and F12.

6. Enter the total number of observations in the samples, N, in cell B14 by typing '=SUM(B11,D11,F11)'.

7. In a new cell, type "=12/(B14*(B14+1))*SUM(B12,D12,F12)-3*(B14+1)" to calculate the test statistic. Press Enter.

8. The value of the test statistic for the Kruskal-Wallis test is shown in cell B16.

   

Sign Test

Note: These instructions assume a sample size greater than 25.

1. Enter the values for X and the sample size into cells A1 and A2, respectively.

2. To calculate the test statistic, in cell A3, input the formula "=(A1+0.5-A2/2)/(SQRT(A2)/2)". Press Enter.

   

3. To calculate the p-value, in cell A4, input the formula "=NORM.S.DIST(A3,1)". Press Enter.

   

Spearman Rank Correlation Test

1. Put the data for x and y in columns A and B, respectively.

2. Calculate the rank for each data value in the first column (A) by typing "=RANK.AVG(A2,$A$2:$A$13,1)" in cell C2. Click the small square at the lower-right corner of the cell and drag it down to the bottom row so that the ranks are calculated for all of the values in the first column. Repeat this procedure for ranking the second column (B), "=RANK.AVG(B2,$B$2:$B$13,1)" in cell D2.

3. Compute the squared differences by entering "=(C2–D2)^2" in cell E2. Click the small square at the lower-right corner of the cell and drag it down so that the formula is replicated for all the rows in the table.

4. In a new cell, type "=1-((6*SUM(E2:E13))/(12*(12^2-1)))" to calculate Spearman's rho. Press Enter.

   Note, if your data does not have 12 data points, adjust the range E2:E13 and the sample size in the denominator of the formula accordingly.

   

Wilcoxon Rank-Sum Test

Note: These instructions assume samples of size 10. Adjust the number of rows in the instructions to fit your data.

1. Put the data for the samples in columns A and B. Put the name of each sample in the first row and put the data values corresponding to each sample below the column labels.

2. Calculate the rank for each of the data values by typing "=RANK.AVG(A2,$A$2:$B$11,1)" in cell C2. Click the small square at the lower-right corner of the cell and drag it down to the bottom row and to the right by one so that the ranks are calculated for all of the values in the first two columns.

   

3. In cell C13, sum the ranks of the first sample by entering "=SUM(C2:C11)". Do the same for the second sample by entering "=SUM(D2:D11)" in cell D13.

4. Count the sample size by entering "=COUNT(A2:A11)" in cell C14. Do the same for the second sample by entering "=COUNT(B2:B11)" in cell D14.

5. Since our test is two-tailed and n₁ ≤ 10, we know T is the rank sum of the sample with the fewest members. Since both the sample sizes in our example are the same, we will arbitrarily choose the rank sum for the sample in column A. In cell B16, enter the label "T" and then in cell C16 enter the value of T, which is 110 in this example.

6. Compare this value with the values for T_L and T_U found in the table for critical values of the Wilcoxon Rank-Sum Test.

   

Wilcoxon Signed-Rank Test

Note: These instructions assume samples of size 9. Adjust the number of rows in the instructions to fit your data.

1. Put the data for the samples in columns A and B. Put the name of each sample in the first row and put the data values corresponding to each sample below the column labels.

2. Enter the title Difference in cell C1. Calculate the difference for each of the pairs by typing "=A2-B2" in cell C2. Click the small square at the lower-right corner of the cell and drag it down so that the formula is replicated for all the rows in the table.

3. Enter the title |Difference| in cell D1. Calculate the absolute value of the differences by typing "=ABS(C2)" in cell D2 and copy the formula down the column.

4. Enter the title Rank(|Difference|) in cell E1. Rank the absolute values of the positive differences from smallest to largest (ignore any 0 values and assign the average rank to ties), and put the ranks in column E.

5. Enter the title Positive Ranks in cell F1. Determine the ranks associated with positive differences by typing "=IF(C2>0, E2, "")" in cell F2, and copy the formula down the column.

6. Enter the title Negative Ranks in cell G1. Determine the ranks associated with negative differences by typing "=IF(C2<0, E2, "")" in cell G2, and copy the formula down the column.

7. Calculate the test statistic in a new cell by entering the formula "=MIN(SUM(F2:F10),SUM(G2:G10))".

8. Calculate the sample size in a new cell by entering the formula "=COUNT(F2:G10)".

9. Use the sample size and desired significance level α to look up the critical value for the Wilcoxon Signed-Rank Test in the appropriate table. Compare this to the test statistic calculated in Step 7.

   

Inverse Normal

Normal Probability (cdf)

Poisson Probability (cdf)

1. Use the formula "=POISSON.DIST(x, mean, TRUE)". Press Enter.

   Note, you may enter a 1 in place of TRUE.

   

Poisson Probability (pdf)

1. Use the formula "=POISSON.DIST(x, mean, FALSE)". Press Enter.

   Note, you may enter a 0 in place of FALSE.

   

Confidence Intervals for Slope and y-Intercept

1. Make sure you have the Data Analysis ToolPak Add-in installed.

2. Organize the data into two columns. Enter the independent variable (X) in the first column and the dependent variable (Y) in the second column.

3. Navigate to the Data tab, and select Data Analysis in the Analyze section. In the dialogue box that appears, select Regression and click OK.

4. 1. Enter the Input Y Range and the Input X Range.

   2. Check the Labels box if you included the variable labels in the input range.

   3. Check the Confidence Level box and enter the desired numeric value for confidence level percentage.

   4. Select Output Range and enter a cell in the spreadsheet for Excel to enter the results.

   5. Click OK.

5. In the example regression output, the Lower 95.0% and the Upper 95.0% columns give the lower and upper endpoints of the 95% confidence intervals for the y-intercept and slope for the independent variable Age (Years).

   

Correlation Coefficient

1. Organize your data into two columns with each row representing an ordered pair.

2. In a separate cell, use the formula "=CORREL(array 1, array 2)" where the arrays are the x and y variable columns of your data, respectfully. Press Enter.

   

Coefficient of Determination

Simple Linear Regression

1. Make sure you have the Data Analysis ToolPak Add-in Installed.

2. Organize the data into two columns. Enter the independent variable in the first column (left) and the dependent variable in the second column (right).

3. Navigate to the Data tab, and select Data Analysis in the Analyze section. In the dialogue box that appears, select Regression and click OK.

   

4. 1. Select the second column for the Y values, and the first column for the X values.

   2. If you have labels in the first cell of the columns, check the box that says Labels.

   3. Select Output Range and enter a cell in the spreadsheet for Excel to enter the results.

   4. Check the box at the bottom that says Line Fit Plots.

5. Click OK.

   

Random Samples

1. In cell A1 type the formula "=RANDBETWEEN(1, 897)" for example. 1 represents the smallest possible number to generate and 897 the largest.

   

2. Place the cursor over the bottom right-hand corner of cell A1, click and drag the box down as many rows as random numbers you desire.

   

Inverse t

Area in one tail

1. Type the formula "=T.INV(probability, degrees of freedom)". Press Enter.

   

   In the example pictured, the student was asked to compute the t-value on a Student's t distribution with 18 degrees of freedom and an area of 0.975 to the left of the t-value.

Area in two tails

1. Type the formula "=T.INV.2T(probability, degrees of freedom)". Press Enter.

   Note that the area in two tails is one minus the area between symmetric t-values.

   

   In the example pictured, the student was asked to compute the positive t-value on a Student's t distribution with 29 degrees of freedom such that half the given area, 0.01, is to its right.

t-Probability (cdf)

1. Type the formula "=T.DIST(x, degrees of freedom, TRUE)". Press Enter.

   The area to the left of the x-value is returned.

   

   In the example pictured, the student was asked to compute the area to the left of 2.38 on a Student's t distribution with 9 degrees of freedom.

Adjusted Exponential Smoothing

1. Enter the values for the time period in column A, enter the values for the response variable in column B, and include labels in row 1 of the spreadsheet

   

2. 1. In cells C1, D1, and E1, name the columns Forecast, Trend, and Adjusted Forecast, respectively.

   2. Enter the initial forecast in cell C2; enter the initial trend, 0, in cell D2; and initial adjusted forecast in cell E2.

   

3. In cell C3, type the formula "=alpha*B2+(1-alpha)*E2", replacing a value for the word "alpha". Press Enter.

   Note, F₂ = α*(D₁) + (1 – α)*(AF₁).

   

4. In cell D3, type the formula "=D2+beta*(C3-E2)", replacing a value for the word "beta". Press Enter.

   Note, T_t+1= T_t-1 + β*(F_t– AF_t-1).

   

5. In cell E3, type the formula "=SUM(C3:D3)". Press Enter.

   

6. We have performed calculations in row 3 that need to be repeated for all other rows. Select the cells from C3 to E3 and double-click on the bottom-right corner of cell E3. Thus, autofilling the values in the remaining rows.

   

Mean Absolute Percentage Error (MAPE)

1. Enter the values for the Period (t), Actual Data (Di), and Forecast (Fi) in Columns A, B, and C, respectively. Include labels in row 1 of the spreadsheet.

   

2. In cell D2, type the formula "=ABS(B2-C2)". Press Enter.

   Note, the Absolute Error is given by |Di-F_i|.

   

3. Double-click on the bottom-right corner of cell D2 so that the formula autofills the column.

   

4. In cell E2, type the formula "=100*(D2/B2)". Press Enter.

   Note, the Absolute Percentage Error is given by 100*|D_t - F_t|/D_t.

   

5. Double-click on the bottom-right corner of cell E2 so that the formula autofills the column.

   

6. To compute the MAPE, choose an empty cell and type the formula "=AVERAGE(E2:E7)". Press Enter.

   Note: These instructions assume data in rows 2 through 7. Adjust the rows in the instructions to fit your data.

   

   In the example shown, the MAPE is 2.8939.

Mean Absolute Deviation

1. Enter the values for Period (t), Actual Data (Di), and Forecast (Fi) in Columns A, B, and C, respectively. Include labels in row 1 of the spreadsheet.

   

2. In cell D2, type the formula "=ABS(B2-C2)". Press Enter.

   Note, Absolute Error is given by |Di - Fi|.

   

3. Double-click on the bottom-right corner of cell D2 to populate the remaining values in column D.

   

4. To compute the MAD, choose an empty cell and type the formula "=AVERAGE(D2:D7)". Press Enter.

   Note: These instructions assume data in rows 2 through 7. Adjust the rows in the instructions to fit your data.

   

Moving Average Plot

Method 1

1. Enter the values for the time period in column A, enter the values for the response variable in column B, and include labels in row 1 of the spreadsheet

   

2. Select the second (right) column of data (excluding the column header). Then, under the Insert tab, in the Charts section, select the Insert Line or Area Chart and the 2-D Line.

   For more information about making a line graph, see Line Graph.

   

3. Click anywhere on the chart, select the + icon to the right of the chart, click on the > arrow to the right of Trendline, and then select More Options....

   

4. The Format Trendline pane will appear. Select Moving Average and enter your desired period in the box on the right.

   Note, if your desired period is 2. Then, in the previous step, you may select Two Period Moving Average instead of More Options...

   

Method 2

1. Make sure you have the Data Analysis ToolPak Add-in installed.

2. Enter the values for the time period in column A, enter the values for the response variable in column B, and include labels in row 1 of the spreadsheet

   

3. Navigate to the Data tab, and select Data Analysis in the Analyze section. In the dialogue box that appears, select Moving Average and click OK.

   

4. 1. Provide the Input Range (in this example, cells B2:B7).

   2. Enter the desired Interval, that is the desired period.

   3. Select Output Range and enter a cell in the spreadsheet for Excel to enter the results (in this example, cell C2).

   

5. Click OK.

   

   Note, both the moving average data and the chart are displayed.

Mean Squared Error (MSE)

1. Enter the values for Period (t), Actual Data (Di), and Forecast (Fi) in Columns A, B, and C, respectively. Include labels in row 1 of the spreadsheet.

   

2. In cell D2, type the formula "=ABS(B2-C2)". Press Enter.

   Note, the Absolute Error is given by |Di-Fi|.

   

3. Double-click on the bottom-right corner of cell D2 to populate the remaining values in column D.

   

4. In cell E2, type the formula "=D2^2". Press Enter.

   

5. Double-click on the bottom-right corner of cell E2 to populate the remaining values in column E.

   

6. To compute the MSE, choose an empty cell and type the formula "=AVERAGE(E2:E7)". Press Enter.

   Note: These instructions assume data in rows 2 through 7. Adjust the rows in the instructions to fit your data.

   

   In the example shown, the MSE is 35.3333.

Simple Exponential Smoothing

Method 1

1. Enter the values for the time period in column A, enter the values for the response variable in column B, and include labels in row 1 of the spreadsheet

   

2. 1. In cell C1, name the column Forecast.

   2. Enter the initial forecast in cell C2.

   

3. In cell C3, type the formula "=alpha*B2+(1-alpha)*C2", replacing a value for the word "alpha". Press Enter.

   Note, F₂ = α*(D₁) + (1 – α)*(AF₁).

   

4. Double-click on the bottom-right corner of cell C3 to populate the remaining values in column C.

   

Method 2

1. Make sure you have the Data Analysis ToolPak Add-in installed.

2. Enter the values for the time period in column A, enter the values for the response variable in column B, and include labels in row 1 of the spreadsheet

   In cell C1, insert the label "Forecast".

   

3. Navigate to the Data tab, and select Data Analysis in the Analyze section. In the dialogue box that appears, select Exponential Smoothing and click OK.

   

4. 1. Provide the Input Range (in this example, cells B2:B7).

   2. Enter the Damping factor, 1-α (in this example, α = 0.3)

   3. Select Output Range and enter a cell in the spreadsheet for Excel to enter the results (in this example, cells C2:C7).

   

5. Click OK.

   

Simple Moving Average

1. Enter the values for the time period in column A, enter the values for the response variable in column B, and include labels in row 1 of the spreadsheet

   

2. In cell C7, type the formula "=AVERAGE(B4:B6)". Press Enter.

   

   Note: In the example pictured, the student was asked to compute a 3-month moving average for June. For a different time period, change the cell range accordingly inside the AVERAGE function.

Weighted Moving Average

1. Enter the values for the time period in column A, enter the values for the response variable in column B, enter the weight for each time period in column C, and include labels in row 1 of the spreadsheet

   Note, the weights in Column C should add to 1.

   

2. In cell D7, type the formula "=SUMPRODUCT(B4:B6,C4:C6)". Press Enter.

   

   Note: In the example pictured, the student was asked to compute a 3-month weighted moving average for June. The result is 11.3. For a different time period, change the cell range accordingly inside the SUMPRODUCT function.