Excel

Installing Data Analysis Toolpak

  1. Click File, Options

    Excel Data Analysis Toolpak step 1
  2. Select Add-ins from the menu on the left. At the bottom of the window select Excel Add-ins for Manage and click Go

    Excel Data Analysis Toolpak step 2
  3. Check the box for Analysis ToolPak and click OK.

    Excel Data Analysis Toolpak step 3
  4. You will now have a button for Data Analysis in the Analysis section of the Data tab.

    Excel Data Analysis Toolpak step 4

ANOVA

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 Analysis section, then Anova:Single Factor. Select OK. Enter the Input Range (A1:C4 in this example), enter an Alpha, and check the Output Range and select the first cell in an available row. Select OK.

    Excel Anova One Way step 3a
    Excel Anova One Way step 3b

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. Navigate to the Data tab, and select Data Analysis in the Analysis section, then either Anova:Two-Factor With Replication (Factorial Design) or Anova:Two-Factor Without Replication (Randomized Block Design), depending on the wording of the problem. Select OK. Enter the Input Range (A1:E7 in this example), enter the number of Rows per sample, enter Alpha, and check the Output Range and select the first cell in an available row. Select OK.

    Excel Anova Two Way step 3a
    Excel Anova Two Way step 3b

Binomial Distribution

Binomial Probability (pdf)

  1. Type the formula "=BINOM.DIST(A5, 4, 1/6, FALSE)" which corresponds to
    BINOM.DIST(
    number of successes (x),
    number of trials (n),
    probability of success (p),
    FALSE)
    .

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

Binomial Probability Distribution

For n = 4 , p = 1 6 , x = 0 , 1 , 2 , 3 , 4 .

  1. Label your first column x. Below, list the possible number of successes from 0 to n.

  2. Label your second column Probability. Below, in cell B2 type the formula "BINOM.DIST(A2, 4, 1/6, FALSE)" which corresponds to BINOM.DIST(number of successes, number of trials, probability of success, TRUE for cumulative/FALSE for the probability there are that exact number of successes).

  3. Drag B2 down to calculate the probabilities for all values in column A.

    Excel Binomial Probability Distribution step 3

Binomial Probability (cdf)

  1. Type the formula "=BINOM.DIST(11, 20, 0.4, TRUE)" which corresponds to
    BINOM.DIST(
    number of successes (x),
    number of trials (n),
    probability of success (p),
    TRUE)
    .

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

Chi-Square Distribution

Critical Value

  1. Type "=CHIINV(probability, degrees of freedom)".

  2. Press ENTER.

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

    Excel Critical Value step 3

Left Tailed Probability (cdf)

Used for finding the p-value corresponding to a χ 2 test statistic.

  1. Type "=CHISQ.DIST(x, deg_freedom, TRUE)".

  2. Press ENTER.

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

Right Tailed Probability (cdf)

Used for finding the p-value corresponding to a χ 2 test statistic.

  1. Type "=CHISQ.DIST.RT(x, deg_freedom)".

  2. Press ENTER.

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

    Excel Right Tailed Probability (cdf) step 3

Test for Association

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

    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.

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

    1. In cell B7 enter: =B$4*$F2/$F$4

    2. Copy and paste the formula above into cells B7 through E8.

      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.

  3. Calculate the p-value.

    1. In cell B9 enter: =CHISQ.TEST(B2:E3,B7:E8)

      Excel Test for Association step 4a

Test for Goodness of Fit

  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.

      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.

  2. Calculate the test statistic (χ2).

    1. Compute Observed Value Expected Value 2 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 χ2 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.

    Excel Test for Goodness of Fit step 5

Confidence Intervals

t-Interval

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.

  1. Type "=CONFIDENCE.T(0.1,900,100)" and press enter.

  2. The result is 149.4352, the error of estimation. Subtract and add this value to the sample mean, 425, to find the confidence interval, (275.5648, 574.4352).

    Excel t-Interval step 2

z-Interval

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.

  1. Type "=CONFIDENCE.NORM(0.1,900,100)" and press enter.

  2. The result is 148.03683, the error of estimation. Subtract and add this value to the sample mean, 425, to find the confidence interval, (276.96317, 573.03683).

    Excel z-Interval step 2

Two Sample Proportions z-Interval

  1. The margin of error for the confidence interval can be computed as follows:

    1. Type “=NORM.S.INV(confidence level)*SQRT((p1*(1-p1))/n1+(p2*(1-p2)/n2)

  2. Add and subtract the margin of error from the point estimate, the difference between the sample proportions, to obtain the lower and upper endpoints of the confidence interval.

Counting

Combination

  1. Use the COMBIN function to calculate the number of combinations.

  2. Input "=COMBIN()" (without the quotations). Insert the total number of items as the first parameter, and the number of items in each combination as the second parameter.

    1. Input: =COMBIN(36,5)

    2. Output: 376992

Factorial

  1. Use the FACT function to calculate the factorial.

  2. Input "=FACT()" (without the quotations) and insert the desired number within the parentheses.

    1. Input: =FACT(5)

    2. Output: 120

Permutation

  1. Use the PERMUT() function to calculate the number of permutations.

  2. Input "=PERMUT()" (without quotations). Insert the number of total items as the first parameter, and then input the number of items in each permutation as the second parameter.

    1. Input: =PERMUT(7,3)

    2. Output: 210

Descriptive Statistics

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 Analysis section

  4. In the window that appears, select Descriptive Statistics and click OK

    Excel Descriptive Statistics One Variable step 4
  5. Select the input range by clicking and dragging your cursor over every cell containing data

  6. Check the option for Summary Statistics

  7. Choose the desired output location

    Excel Descriptive Statistics One Variable step 7
  8. Click OK

    Excel Descriptive Statistics One Variable step 8

Two Variable

Grouped Data

  1. Enter the data in two separate columns: midpoints in column A and data 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: weights in column A and data values in column B

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

  3. Press Enter

    Excel Weighted Mean step 3

Frequency Distribution

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.

    Excel Qualitative Frequency Distribution step 2
  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.

    Excel Qualitative Frequency Distribution step 3
  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.

    Excel Qualitative Frequency Distribution step 5
  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.

    Excel Qualitative Frequency Distribution step 6

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.

    Excel Quantitative Frequency Distribution step 2
  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.

    Excel Quantitative Frequency Distribution step 3
  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.

    Excel Quantitative Frequency Distribution step 5
  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.

    Excel Quantitative Frequency Distribution step 6
  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:.

    Excel Quantitative Frequency Distribution step 8
  9. Click OK. Now, your data should be grouped into classes, and the frequency count of each class should be displayed in the PivotTable.

    Excel Quantitative Frequency Distribution step 9

Graphs

Bar Charts

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

    Excel Bar Charts step 1
  2. Select all the data and under the Insert tab, insert either a 2-D Column (vertical) or 2-D Bar (horizontal) graph.

    Excel Bar Charts step 2a Excel Bar Charts step 2b
  3. You may edit the chart title.

    Excel Bar Charts step 4
  4. To add axis labels, use the Design tab, Add Chart Element and select Axis Titles > Primary Horizontal or Axis Titles > Primary Vertical.

    Excel Bar Charts step 5

Box Plot

  1. The data for each box plot should be in a separate column.

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

    Excel Box Plot step 2
  3. You can edit the Chart Title by clicking on it and typing in a new title.

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

    Excel Box Plot step 4
  5. The value of 1 along the horizontal axis can be removed by selecting it and deleting it.

    Excel Box Plot step 5

Dot Plot

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

    Excel Dot Plot step 1
  2. Highlight the entire column. Under the Home tab, click the Sort & Filter dropdown 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)

    Excel Dot Plot step 3
  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.

    Excel Dot Plot step 4
  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 an X-Y Scatter plot. Your chart should look like the following, but specific to your data.

    Excel Dot Plot step 5
  6. This is close to a dot plot, but it needs a few tweaks. First, we need to format the x-axis to remove all the extra whitespace. Right click on the x-axis, select Format Axis, and then change the Bounds to fit your data (make sure you fit in all the data, and 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.

    Excel Dot Plot step 6
  7. Next, let's remove the y-axis. Since each dot represents a single occurrence, the y-axis is unnecessary since we can simply count the dots. Also, we will delete the grid lines since they also are not necessary.

    Excel Dot Plot step 7
  8. Lastly, we need to resize the graph so that our dots appear to be stacked on top of one another. To finish it off, lets add a title for the x-axis and for the chart as a whole.

    Excel Dot Plot step 8

Histogram

  1. Organize the data into two columns. In the first column, enter the lower boundary of each class, but also include the upper boundary of the last class as the final entry. In the second column, enter the corresponding frequency values. There should be one less frequency value than there are boundaries, so in the frequency column, leave the cell corresponding to the first heart rate value empty and begin entering the frequency values in the cell that corresponds to the second heart rate value.

    A B
    1 Heart Rate Frequency
    2 56.5
    3 66.5 2
    4 76.5 10
    5 86.5 32
    6 96.5 5
    7 106.5 1

    Footnote: Histogram convention is for intervals to include a left endpoint and exclude a right endpoint. However, since the heart rates are presented as integers, a decimal or fraction can be used to eliminate the possibility of overlapping bins.

  2. Select the values in the frequency column, and insert a column chart.

    Excel Histogram step 2
  3. Right click on one of the columns in the chart and select the Format Data Series option. Under the Series Options menu, reduce the gap width to 0%. Then, under the Fill & Line menu, add a border of appropriate weight around the columns.

    Excel Histogram step 3
  4. Select the values in the Heart Rate column, and while holding down the Ctrl button, also select an identical amount of cells from the blank column to the right of the Frequency column. With the cells selected, copy them to the clipboard. Next, select the chart by clicking on it, and then in the ribbon at the top click Home tab > Paste dropdown menu > Paste Special. In the dialog box that appears, paste the data as a new series with the Values in Columns, and also check the Categories in First Column box.

    Excel Histogram step 4
  5. Right click on one of the corners of the selection box (since there is nothing else visible to click on) and select the Change Series Chart Type option. Format Series 2 as a Line chart and check the secondary axis box.

    Excel Histogram step 5
  6. Select the chart and click on the plus symbol that appears in the top right corner. Click the arrow to the right of the Axes option and check the box for the Secondary Horizontal axes.

    Excel Histogram step 6
  7. Format the secondary vertical axis (right) so that the horizontal axis crosses at the automatic position.

    Excel Histogram step 7
  8. Format the primary vertical axis (left) so that the horizontal axis crosses at the maximum axis value. This will turn the chart upside down. Now, delete the primary vertical axis (left) to return the columns to their upright position.

    Excel Histogram step 8
  9. Format the primary horizontal axis (top) so that it uses no line, labels, or tick marks. Make sure both options under Tick Marks and the Label Position option under Labels are set to None.

    Excel Histogram step 9
  10. Format the secondary horizontal axis (bottom) so the vertical axis crosses at the automatic position. Finally, set the axis position to be on tick marks.

    Excel Histogram step 10

Line Graph

  1. Organize the data into 2 columns, the labels on the left and the values for each label on the right. (The labels are not required.)

    Excel Line Graph step 1
  2. Select the column of data values and under the Insert tab, insert a 2-D Line graph.

    Excel Line Graph step 2
  3. After inserting the graph, to update the x-axis data labels, right-click the x-axis labels and choose Select Data. Under the Horizontal (Category) Axis Labels press Edit and select the label values range. Click OK and OK.

    Excel Line Graph step 3a Excel Line Graph step 3b Excel Line Graph step 3c
  4. You may edit the chart title.

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

    Excel Line Graph step 5

Multivariate/Multidimensional

  1. Select 3 columns of data and click Insert > Scatter or Bubble Chart and choose Bubble. The first column will be the x-axis values, the second column the y-axis, and the third column the size of the bubbles.

  2. If the bubbles overlap too much you can scale them down by right-clicking the bubbles, selecting Format Data Series and changing the value in Scale bubble size to to something less than 100.

  3. Use the Add Chart Element button on the Design tab to add axis labels if you wish.

Normal Probability Plot

  1. Enter a header for your data in A1 and your data below in a single column starting in A2.

    Excel Normal Probability Plot step 1
  2. Select cell A1 and press Sort & Filter located in the Editing portion of the Home menu. Sort the data smallest to largest.

    Excel Normal Probability Plot step 2
  3. Starting in cell B2, assign a rank to each row of data.

  4. In C2, enter the formula "=(B2-0.5)/n", replacing n with the number of data points. This will calculate the percentile. Press Enter and drag down for all your rows of data.

    Excel Normal Probability Plot step 4
  5. In D2 enter "=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.

    Excel Normal Probability Plot step 5
  6. Hold the Ctrl key and highlight columns A and D. Select Insert and choose the scatter plot axis.

    Excel Normal Probability Plot step 6
  7. Select the left axis labels, right click and choose Format Axis. Under Axis Options, select Axis value for Horizontal axis crosses and enter the smallest value shown on your vertical axis.

    Excel Normal Probability Plot step 7
  8. Click Design, Add Chart Element, Trendline, Linear to add a trendline. If the data are approximately normally distributed they should closely follow a linear trendline.

    Excel Normal Probability Plot step 8

Pareto Chart

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

    Excel Pareto Chart step 1
  2. If the values are not sorted, then select the data in both columns, choose Sort & Filter, Custom Sort. Sort the data by the column with the values (Total Visits in the example) and sort largest to smallest.

    Excel Pareto Chart step 2
  3. Once the data is sorted, select all the data and under the Insert tab, insert either a 2-D Column (vertical) or 2-D Bar (horizontal) graph.

    Excel Pareto Chart step 3a Excel Pareto Chart step 3b
  4. You can edit the Chart Title by clicking on it and typing in a new title.

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

    Excel Pareto Chart step 5

Pie Chart

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

    Excel Pie Chart step 1
  2. Select the data by clicking and dragging the cursor from the first cell containing data in the left column down to the last cell containing data in the right column.

  3. With the data selected, click on the Insert tab and insert a 2-D Pie chart.

    Excel Pie Chart step 3
  4. To add labels showing the percentages, right click on the pie and click Add Data Labels.

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

    Excel Pie Chart step 5

Scatterplot

  1. Organize the data in adjacent columns so that corresponding data values line up next to each other.

  2. Select the data by clicking and dragging the cursor from the first cell containing data in the left column down to the last cell containing data in the right column. Note: it may be beneficial to exclude labels in this step, but be sure to add them into the graph later.

  3. With the data selected, click on the Insert tab and click the Insert Scatter (X, Y) or Bubble Chart button in the Charts section.

  4. Zoom in on the data points by altering the scales on the axes as needed. However, be careful not to alter the axes in such a way that the data are no longer visible.

Hypergeometric Distribution

  1. Type the formula "=HYPGEOM.DIST(A2, 2, 16, 30, FALSE)" which corresponds to
    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.

Hypothesis Testing

One Proportion z-Test

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

    1. In cell B1 enter the sample proportion, 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 sample t-Test step 6

z-Test

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

    1. In cell B1 enter the sample mean, .

    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))

    z-Test step 3

t-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 sample standard deviation, s .

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

  2. Calculate the test statistic (t).

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

  3. Compute the t critical value with n−1 degrees of freedom. (Substitute a value for ‘alpha’.)

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

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

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

    t-Test step 3

Two Sample t-Test (Independent Samples)

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

    Two sample t-Test step 1
  2. Compute the test statistic by entering the following formula in cell B6.

    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 n1 + n2 − 2 degrees of freedom. (Substitute a value for alpha.)

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

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

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

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. Under the Data tab, select the Data Analysis option. In the dialogue box that appears, select the t-Test: Paired Two Sample for Means option and click OK.

  4. 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.

  5. Select Labels if the first cells in your variable ranges are data labels. Enter a value for Alpha. Select an output option:

    1. Output Range: A cell within your spreadsheet where excel will enter the results.

    2. New Worksheet Ply: The name of a new worksheet where excel will enter the results.

    3. New Workbook: Excel will enter the results in a new workbook.

    Two sample t-Test step 5
  6. Click OK.

    Two sample t-Test step 6

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.

    Two sample z-Test step 2
  3. Under the Data tab, select the Data Analysis option. In the dialogue box that appears, scroll down to select the z-Test: Two Sample for Means option and click OK.

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

  5. Select Labels if the first cells in your variable ranges are data labels. Enter a value for Alpha. Select an output option:

    1. Output Range: A cell within your spreadsheet where excel will enter the results.

    2. New Worksheet Ply: The name of a new worksheet where excel will enter the results.

    3. New Workbook: Excel will enter the results in a new workbook.

    Two sample z-Test step 5
  6. Click OK.

    Two sample z-Test step 6

Two Proportion z-Test

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

  2. Calculate p ^ 1 and p ^ 2 .

    1. In cell B4 enter: =B2/B3

    2. In cell C4 enter: =C2/C3

  3. Calculate p _ , 1 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 Proportion z-Test step 3

Two Sample F-Test

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

  2. Compute test statistic (F).

    1. In cell B5 enter: =B2/C2

  3. Compute the F critical value. Enter the appropriate formula below in cell B6, substituting a value for ‘alpha’.

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

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

    3. For a two-tailed test: =SUM( F.INV(alpha/2,B3-1,C3-1), F.INV.RT(alpha/2,B3-1,C3-1) )

    Two Sample F-Test step 3

Normal Distribution

Inverse Normal

Standard Normal

  1. Type "=NORM.S.INV(probability)".

  2. Press ENTER.

  3. The z-score with area to the left equal to the probability entered is returned.

    Excel Normal Distribution Inverse Normal Standard Normal step 3

Non-standard Normal

  1. Type "=NORM.INV(probability, mean, standard deviation)".

  2. Press ENTER.

  3. The x-value with area to its left equal to the probability entered is returned.

    Excel Normal Distribution Inverse Normal Non-Standard Normal step 3

Normal Probability (cdf)

Standard Normal

  1. Type "=NORM.S.DIST(z, cumulative)". (Choose TRUE for cumulative for cdf, FALSE is pdf.)

  2. Press ENTER.

  3. The area to the left of the z-score is returned.

    Excel Normal Probability (cdf) Standard Normal step 3

Non-standard Normal

  1. Type "=NORM.DIST(x, mean, standard deviation, cumulative)". (Choose TRUE for cumulative for cdf, FALSE is pdf.)

  2. Press ENTER.

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

    Excel Normal Probability (cdf) Non-Standard Normal step 3

Poisson Distribution

Poisson Probability (cdf)

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

    Poisson Probability (cdf)

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

Poisson Probability (pdf)

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

    Excel Poisson Probability (pdf) step 1

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

Regression

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. Under the Data tab, select the Data Analysis option. In the dialogue box that appears, select the Regression option and click OK.

  4. Enter the Input Y Range and the Input X Range. Check the Labels box if you included the variable labels in the input range. Check the Confidence Level box and enter the desired numeric value for confidence level percentage. Check the Output Range and select the first cell in an available row. Then select 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).

    Excel Confidence Intervals for Slope and y-Intercept step 5

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 to calculate.

    Excel Correlation Coefficient step 2

Coefficient of Determination

Simple Linear Regression

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

  2. In a separate cell, use the formula "=RSQ(array 1, array 2)". In this case, the array for the y variable comes first and the array for the x variable is second. Press Enter to calculate.

    Excel Coefficient of Determination step 2

Multiple Regression

  1. Follow the steps for Multiple Regression
  2. The coefficient of determination will be listed in the output under Regression Statistics next to "R Square".

Multiple Regression

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

  2. Enter your response values into the first column and each of your predictor values in the next columns. Label your columns.

  3. Under the Data tab, select the Data Analysis option. In the dialogue box that appears, select the "Regression" option and click OK.

  4. Enter the Input Y Range and the Input X Range. Check the Labels box, check the Output Range and select the first cell in an available row. Then select OK. (Note the X-variables must be in contiguous columns.)

    Excel Multiple Regression step 4a Excel Multiple Regression step 4b

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. Under the Data tab, select the Data Analysis option. In the dialogue box that appears, select the Regression option and click OK.

    Excel Simple Linear Regression step 3
  4. Select the second column for the Y values, and the first column for the X values. If you have labels in the first cell of the columns, check the box that says Labels. Choose a desired Output location, and check the box at the bottom that says Line Fit Plots.

    Excel Simple Linear Regression step 4a
    Excel Simple Linear Regression step 4b

t-Distribution

Inverse t

Area in one tail

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

    Excel t-Distribution step 1

Area in two tails

  1. Use the formula "=T.INV.2T(area in two tails, degrees of freedom)". Press Enter to calculate. This gives you the positive t-value with half the given area to it's right. Note that the area in two tails is one minus the area between symmetric t-values.

    Excel t-Distribution two tailed area step 1

Data Manipulation

Sorting

  1. Enter the values in a single column.

  2. Select the data, right-click, and select Sort > Sort Smallest to Largest or Sort > Sort Largest to Smallest. Another option is to select the data, and under Home > Editing choose Sort & Filter > Sort Smallest to Largest or Sort & Filter > Sort Largest to Smallest.

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 under Home > Editing click Sort & Filter > Filter. (Alternatively, navigate to the Data tab and click Filter under Sort & 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 which fit some certain criteria.

Subset Calculations

Say you have the numbers 1, 2, 3, …., 20 in a column of data and you have filtered this column to only the even values (2, 4, 6, …, 20).

If you want to sum these values, type =SUM( and select the visible range, you will end up with =SUM(A3:A21) which is equal to 209. This is also including the non-visible cells in the sum. To avoid this issue, you can use the SUBTOTAL function. =SUBTOTAL(109,A3:A21) will equal 110, the sum of only the visible cells. The 109 is the code to access the SUM function for visible cells. There are several functions for visible subsets built into SUBTOTAL.

101 – AVERAGE, 102 – COUNT, 103 – COUNTA, 104 – MAX, 105 – MIN, 106 – PRODUCT, 107 – STDEV.S, 108 – STDEV.P, 109 – SUM, 110 – VAR.S, 111 – VAR.P

Sampling

Random Samples

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

    Excel Random Samples step 1
  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.

    Excel Random Samples step 2