﻿ Hawkes Learning | Statistics Resources | Technology | Excel Instructions

# Excel

## Installing Data Analysis Toolpak

1. Click File, Options 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 3. Check the box for Analysis ToolPak and click OK. 4. You will now have a button for Data Analysis in the Analysis section of the Data tab. ## 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.

### Two-Way

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

2. Enter your data in the following format.

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.

## 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 $\mathsfit{n}=4$, $\mathsfit{p}=\frac{1}{6}$, $\mathsfit{x}=0\text{,}1\text{,}2\text{,}3\text{,}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. ### 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. ### Left Tailed Probability (cdf)

Used for finding the p-value corresponding to a ${\mathsfit{\chi }}^{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 ${\mathsfit{\chi }}^{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. ### 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) ### 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 $\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 χ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. ## 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). ### 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). ### 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 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 8. Click OK ### 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 ## 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. 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. ## Graphs

### 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 and under the Insert tab, insert either a 2-D Column (vertical) or 2-D Bar (horizontal) graph.  3. You may edit the chart title. 4. To add axis labels, use the Design tab, Add Chart Element and select Axis Titles > Primary Horizontal or Axis Titles > Primary Vertical. ### Box Plot

1. The data for each box plot should be 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 can edit the Chart Title by clicking on it and typing in a new title. 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. 5. The value of 1 along the horizontal axis can be removed by selecting it and deleting it. ### 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 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 an X-Y Scatter plot. Your chart should look like the following, but specific to your data. 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. 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. 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. ### 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 Heart Rate Frequency 56.5 66.5 2 76.5 10 86.5 32 96.5 5 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. 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. 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. 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. 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. 7. Format the secondary vertical axis (right) so that the horizontal axis crosses at the automatic position. 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. 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. 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. ### 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.) 2. Select the column of data values and under the Insert tab, insert a 2-D Line graph. 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.   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. ### 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. 2. Select cell A1 and press Sort & Filter located in the Editing portion of the Home menu. Sort the data smallest to largest. 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. 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. 6. Hold the Ctrl key and highlight columns A and D. Select Insert and choose the scatter plot axis. 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. 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. ### Pareto Chart

1. Organize the data into 2 columns, the labels on the left and the values for each label on the right. 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. 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.  4. You can edit the Chart Title by clicking on it and typing in a new title. 5. To add axis labels, use the 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 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. 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. 4. To add labels showing the percentages, right click on the pie and click Add Data Labels. 5. You can edit the Chart Title by clicking on it and typing in a new title. ### 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, $\stackrel{^}{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)) ### 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)) ### t-Test

1. Enter the summary statistics in cells B1 through B4 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$.

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) ### Two Sample t-Test (Independent Samples)

1. Enter the statistics for each variable in cells B2 through C4. 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. 6. Click OK. ### 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. 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. 6. Click OK. ### Two Proportion z-Test

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

2. Calculate ${\stackrel{^}{p}}_{1}$ and ${\stackrel{^}{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 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) ) ## 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. #### 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. ### 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. #### 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. ## Poisson Distribution

### Poisson Probability (cdf)

1. Use the formula "=POISSON.DIST(x, mean, TRUE)". Press Enter to calculate. 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. 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). ### 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.

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

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

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

## t-Distribution

### Inverse t

#### Area in one tail

1. Use the formula "=T.INV(probability, degrees of freedom)". Press Enter to calculate. #### 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. ## 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. 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. 