Math 320 Excel and Calculator Notes

See Procedures below for specific procedures for Excel or a TI-83.

  • Using Excel in Math320
  • The Data Analysis Toolpak in Excel

  • Importing Data from the Textbook Data Diskette
  • Importing Data from a Computer Text File
  • Things to do using Excel

  • Go to Math320 home page
  • Go to Math320 homework page
  • Go to Professor Sawyer's home page
  • Go to Mathematics Department Home Page
  • Go to Washington University Home Page

    Procedures:

    1. Sample mean, sample standard deviation

    2. Population mean, population standard deviation

    3. Binomial: Find P(X<=x) and P(X=x) where X is binomial with parameters n and p

    (To find P(X>=x), note that P(X>=x) = 1 - P(X<=x-1). )

    4. Normal: Find P(X<=x) or P(a<=X<=b) where X is normal with parameters mu and sigma

    (To find P(X>=x), note that P(X>=x) = 1 - P(X<=x) for any continuous distribution. )

    5. Inverse normal: Find x such that P(X<=x)=p for a given p where X is normal with mu and sigma

    6. One-sample and two-sample Z tests: Given one or two normal samples with known population standard deviations, test H0:muX=mu0 (one sample) or else H0:muX=muY (two samples).

    7. Poisson: Find P(X<=x) and P(X=x) where X has a Poisson distribution with mean mu.

    8. Student's t, Chi-square, or F distribution: Find P(X<=x) for given degrees of freedom

    9. One-sample and two-sample T tests: Given one or two normal samples with UNKNOWN population standard deviations, test H0:muX=mu0 (one sample) or else H0:muX=muY (two samples).

    10. Confidence interval for a population proportion based on a sample proportion (Normal approximation)

    11. Confidence interval for a population mean from a normal sample (Student's t).

    12. Do a one-sample or two-sample t-test

    13. Correlation coefficients and simple linear regression

    14. One-way ANOVA for d samples

    
    

    1. Calculating sample mean and sample standard deviations:     Go to top of this page

    NOTE: If all else fails, and you cannot figure out how to use the statistical features on your calculator, you can always find (i) the sum the data and (ii) the sum of the squares of the data and then use the ``calculator-friendly'' formula for the sample standard deviation on page 86 of the text.

    If you have a Sharp EL-546L, read pages 21-24 of its manual and follow the worked out example on page 113 of the manual.

    In Microsoft Excel:     (See Math320 Excel Notes)

    (a) Enter the data for which you want the sample mean and standard deviation in a groups of cells: (i) a sequence of cells in one column, (ii) a sequence of cells in one row, or (iii) a rectangular block of cells.
    (b) Clink on an emply cell to highlight it and enter fx on the menubar. Alternatively, you can click on Insert | fx Function... On the dialog box that appears, click on Statistical and double-click on AVERAGE. Enter the range of cells in the window that appears or else highlight the range of cells. When you click on OK, the average will appear in the cell that you first highlighted.
    (c) Position the cursor on another cell, for example the cell next to the cell with the average. Click on fx , then Statistical, and then STDEV as in (b). Enter the range of cells as above. When you click OK or press ENTER, the sample standard deviation will appear in the second cell that you highlighted.

    Alternatively, if the numbers are in a single row or a single column, you can click on Tools | Data Analysis... | Descriptive Statistics if you have the Data Analysis Toolpak installed. Check Summary Statistics and click on  OK. A table will appear with the sample mean, sample standard deviation, and several other summary statistics. If your data is in a rectangular block of cells, you will be given the choice of Descriptive Statistics for each column or for each row, which may not be what you wanted. Warning: The standard error is the standard deviation divided by the square root of the number of observations, and is not the same as the standard deviation.

    If you have a TI-83:

    (i) Press the ON button to turn it on,
    (ii) Press the STAT button,
    (iii) In the EDIT display in the STAT window (you may have to use the cursor buttons to select the EDIT display), press 1 for 1:Edit,
    (iv) Move the cursor to the column heading of L1 (for List 1). If there are numbers in L1 already, or if you cannot find list L1, see below for how to RESET the TI-83 (which will clear all lists),
    (v) Enter your numbers in sequence using the number keys and either the down-arrow or the Enter key,
    (vi) When you are done, press STAT again and select the CALC display in the STAT window,
    (vii) Press 1 for 1:1-Var Stats (this means single-sample statistics),
    (viii) When a window with 1-Var Stats appears, press (2nd)1 (for List 1) and then Enter.
    A number of sample statistics will now appear. Sx is the sample standard deviation. The similar but slightly smaller number (sigma)x is the population standard deviation for the sample. The population standard deviation is only appropriate if you are absolutely sure that the sample mean Xbar is exactly the same as the underlying population mean, which rarely happens in practice.

    RESETTING the TI-83:   Press (2nd)+ (for MEM) and then either 4:ClrAllLists or 5:Reset. Follow the instructions. To do this quickly, enter (2nd)+ (for MEM) then 5 then 1 then 2.

    2. Population mean and population standard deviation:     Go to top of this page

    This is the same as for the sample mean and standard deviation except that (a) population relative frequencies may be entered as well as the values and (b) you need the population standard deviation ((sigma)x) instead of the sample standard deviation (Sx). The two quantities (sigma)x and Sx differ by a factor of root(n/(n-1)).

    In Microsoft Excel:     (See Math320 Excel Notes)

    Enter your data in a range of cells as in Sample mean and standard deviation above. Instead of entering STDEV for the second cell, enter STDEVP (for population standard deviation). There doesn't seem to be a statistical function that uses population relative frequencies. However, calculating population means and standard deviations is fairly easy using spreadsheet operations.

    If you have a TI-83:

    Follow the same procedure as for the sample mean and standard deviation, except
    (a) After steps (iv) and (v) above, ALSO enter the population relative frequencies in list L2.
    (b) When 1:1-Var Stats appears in step (viii), enter ( (that is, press the left-parenthesis button) then (2nd)1 (for list 1) then COMMA (yes, there is a button for the comma) then (2nd)2 (for list 2) then ) (the right-parenthesis button) then ENTER. The first list is for the values and the second list is for the population relative frequencies. A screen with calculated values should appear.
    (c) The population standard deviation will be listed after (sigma)x. The entry for the sample standard deviation (Sx) should be blank.

    3. Find P(X<=x) where X has a binomial distribution with parameters n and p:     Go to top of this page

    In Microsoft Excel:     (See Math320 Excel Notes)

    Highlight an empty cell and then click on fx on the icon menubar, Statistical..., and then BINOMDIST . The causes a window to drop down with 4 entries to fill in for each binomial pdf or cdf probability value.
    To calculate P(X<=x) or P(X=x), enter the four values (a) x, (b) n, (c) p, and (d) a ``logical'' variable called ``Cumulative'' in that order in the places for the 4 entries. The logical variable ``Cumulative'' is entered as 1 or TRUE if you want P(X<=x) and 0 or FALSE if you want P(X=x). The value of P(X<=x) or P(X=x) will appear instantly in the window as soon as you enter 0 or 1 (or else finish spelling TRUE or FALSE) . If you press OK in the Excel window or else the Enter key on your computer's keyboard, the value will appear in the default Excel cell.
    To find P(X>=x), note that P(X>=x) = 1 - P(X<=x-1) for any integer-valued random variable X.  You can calculate probabilities like P(a<=X<=b) by subtraction.

    If you have a TI-83:

    (i) Press the ON button to turn it on,
    (ii) Press EITHER (2nd)0 for CATALOG or ELSE (2nd)VARS for DISTR .
    (iii) For P(X<=x), scroll down until the cursor is next to binomcdf(  and press ENTER . Select binompdf(  for P(X=x).
    (iv) The basic syntax for the two commands in (iii) is binomcdf(numtrials,p,x) and binompdf(numtrials,p,x). For example, assuming that you want P(X<=x), wait for a window with binomcdf(  to appear. Enter n then COMMA (that is, press the button with a comma on it) then p then COMMA then x then ) (the right-parenthesis button) then ENTER. The probability P(X<=x) should then appear. For example, If n=50, p=0.055, and x=3, enter 50 then COMMA then 0.055 then COMMA then 3 then ) then ENTER. The number 0.70469... will then appear.
    (v) To find P(X>=x), note that P(X>=x) = 1 - P(X<=x-1) for any integer-valued random variable X.  You can calculate probabilities like P(a<=X<=b) by subtraction.

    You can do some fancier things as well:
    (vi) To get all values of P(X<=x) at once, use the syntax binomcdf(numtrials,p). That is, after you get to the window with binomcdf(, enter n then COMMA then p then ) (right parenthesis) then ENTER, without the variable x. A list of the values of P(X<=x) will appear, most of which will be outside of the calculator window. To view them in a list, enter STO(arrow) then (2nd)1 (for List 1) then ENTER, then enter STAT then 1 for 1:Edit. The values of P(X<=x) will be displayed in a list.
    (vii) To calculate P(X=x) for n=50, p=0.055, and all values of x between 0 and 7 inclusively and display the probabilities in list L2, first enter the values 0,1,2,3,4,5,6,7 in list L1. (See the instructions above for sample mean and standard deviation.) Enter (2nd)MODE for QUIT so that the calculator will know that you have stopped entering values in list L1. (Otherwise, the calculator will assume that any further operations have to do with the next entry in L1.) Enter (2nd)0 for CATALOG, scroll down to binompdf(, and press ENTER. When the window with binompdf( appears, enter 50 then COMMA then 0.055 then COMMA then (2nd)1 for list L1 then ) (the right-parenthesis button to close the binompdf( function) then STO(arrow) then (2nd)2 for list 2 then ENTER. If you press STAT and then 1 for 1:Edit, then the eight probabilities P(X=x) for x=0,1,2,3,4,5,6,7 will be in list L2 .

    NOTE: Some TI-83s will crash if you try to calculate a binomial cumulative probability with n=1000 . (This is true for the TI-83 in my office, but is not true for newer TI-83s.) Excel 97 will crash if you enter n=10,000. Use the normal approximation to the binomial for values of n that are this large.

    4. Find P(X<=x) or P(a<=X<=b) where X has a normal distribution with parameters     Go to top of this page

    In Microsoft Excel:     (See Math320 Excel Notes)

    To calculate P(X<=x) or the normal density f(x) where X has a normal distribution with parameters mu and sigma2, first highlight an empty cell. Click on fx on the icon menubar and then Statistical... and NORMDIST .
    In the window that drops down, enter (a) x, (b) mu, (c) sigma, and (d) a logical variable ``Cumulative'', in that order in the places for the 4 entries. As with the binomial distribution, enter 1 or TRUE for ``Cumulative'' for P(X<=x) and 0 or FALSE for the value of the normal density f(x). If you press OK on this window or Enter on the computer keyboard, the value will also appear in the default Excel cell.

    If you have a TI-83:

    (i) Press the ON button to turn it on,
    (ii) Press (2nd)VARS for DISTR and then 2 for normalcdf(
    (iii) To calculate P(a<=X<=b) when X is a normal distribution with mean mu and standard deviation sigma, enter a then COMMA (the key with a comma on it) then b then COMMA then mu then COMMA then sigma then ) (the right-parenthesis button) then ENTER. The probability will appear. For example, to calculate P(20<=X<=22.7) when mu=20 and sigma=1.8, enter 20 then COMMA then 22.7 then COMMA then 20 then COMMA then 1.8 then ) (right-parenthesis) then ENTER. The number 0.43319.... should appear.
    (iv) To calculate P(a<=Z<=b) when Z is a standard normal distribution (that is, mean 0 and standard deviation 1), follow the steps in (iii) above but leave out the mu and sigma. For example, to calculate P(0<=Z<=1.50), enter 0 then COMMA then 1.5 then ) (right-parenthesis) then ENTER. The answer should be the same as in (iii).
    (v) To calculate P(X<=x) when X is a normal distribution with mean mu and standard deviation sigma, follow the steps in (iii) with the number -1E99 in place of the lower bound. This is scientific notation for -1 followed by 99 zeroes and is meant to represent ``-infinity''. Similarly, calculate P(X>=x) by entering 1E99 as the upper bound. To enter -1E99, (a) press the (-) key at the bottom of the keypad (this is the ``unary'' minus sign as in -1 or -5. DO NOT USE the - key just above the + key. That is the ``binary'' minus sign, as in 4-2=2), (b) press 1, (c) press (2nd)COMMA for EE, which denotes scientific notation for numbers, and (d) enter 99 for the ``exponent''. For example, to calculate P(X<=22.7) for a normal distribution with mean 20 and standard deviation 1.8,  get to a screen with normalcdf( as in step (ii). Enter -1E99 as above then COMMA then 22.7 then COMMA then 20 then COMMA then 1.8 then ) (right-parenthesis) then ENTER. The answer 0.93319.... should appear.

    5. Find x such that P(X<=x)=p for a given p where X has a normal distribution with parameters     Go to top of this page

    In Microsoft Excel:     (See Math320 Excel Notes)

    Click on an empty cell. Then click on fx on the icon menubar, Statistical..., and then NORMINV . A window will drop down with places for 3 entries. Enter (a) p, (b) mu, and (c) sigma in that order in the places for the 3 entries. The value of x will instantly appear. If you enter OK on the drop-down window or Enter on the computer keyboard, it will also be placed in the default Excel cell.

    Using a TI-83:

    (i) Press the ON button to turn it on,
    (ii) Press (2nd)VARS for DISTR,
    (iii) Press 3 for 3:invNorm(.
    (iv) A screen will appear in which you can enter parameters for the inverse normal distribution. For example, to find x such that P(X<=x)=p when X is a normal distribution with mean mu and standard deviation sigma, enter p then COMMA (the key with a comma on it) then mu then COMMA then sigma then ) (the right-parenthesis button) then ENTER. The probability will appear. For example, to find x such that P(X<=x)=0.666 when mu=20 and sigma=1.8, enter 0.666 then COMMA then 20 then COMMA then 1.8 then ) (right-parenthesis) then ENTER. The number 20.777... should appear.
    (iv) To find x such that P(Z<=x)=p when Z has a standard normal distribution (that is, mu=0 and sigma=1), follow the steps in (iii) but leave out the mu and sigma. For example, to find x such that P(Z<=x)=0.666, enter 0.666 then ) (right-parenthesis) then ENTER. The number 0.42889... should appear.

    6. One-sample and Two-sample Z tests:     Go to top of this page

    Given one or two normal samples with KNOWN population standard deviations, test H0:muX=mu0 (one sample) or else H0:muX=muY (two samples).
    In Microsoft Excel:
    Click on Tools then Data Analysis.... then
    z-Test: Two-Sample for Means
    Follow the directions. The option and dialog box assume two samples. I suspect that the second sample can be identically zero, which would have the same effect as a one-sample test. However, I haven't checked this. See the Math320 Excel Notes for more details.

    Using a TI-83:
    After you have turned on the TI-83 and possibly reset or cleared it, enter STAT, then TESTS  and then either 1: for a (one-sample) Z test or 3: for a 2-sample Z test. In either case, the first step will be to either highlite either Data or Stats.

    If your data is in terms of the samples themselves, make sure that Data is highlighted. Press Enter to make sure that your choice of Data or Stats is fixed. Fill in mu0 (for the one-sample test), the known population variance or variances, and the lists (L1 or L1 and L2) that you have entered your data in. (See 1. Calculating sample mean and sample standard deviations for discussion about using Lists with a TI-83.) Leave any Freq entries at 1.  Highlite the correct choice for a two-sided test, lower-tailed one-sided test, or upper-tailed one-sided test and press Enter to make sure that the choice is set. Finally, highlite Calculate and press Enter.
    If your data is in terms of summary statistics (Xbar or Xbar and Ybar), make sure that Stats is highlighted instead of Data and continue in the same way.

    7. Poisson: Find P(X<=x) and P(X=x) where X has a Poisson distribution with mean mu.     Go to top of this page

    In Microsoft Excel:     (See Math320 Excel Notes)

    Highlight an empty cell and then click on fx on the icon menubar, Statistical..., and then POISSON . The causes a window to drop down with 3 entries to fill in.
    To calculate P(X<=x) or P(X=x), enter the three values (i) x, (ii) mu, and (iii) a ``logical'' variable called ``Cumulative''.  The logical variable ``Cumulative'' is entered as 1 or TRUE if you want P(X<=x) and 0 or FALSE if you want P(X=x). The value of P(X<=x) or P(X=x) will appear instantly in the window as soon as you enter 0 or 1 (or else finish spelling TRUE or FALSE) . If you press OK in the Excel window or else the Enter key on your computer's keyboard, the value will appear in the default Excel cell.
    To find P(X>=x), note that P(X>=x) = 1 - P(X<=x-1) for any integer-valued random variable X.  You can calculate probabilities like P(a<=X<=b) by subtraction.

    If you have a TI-83:

    (i) Press the ON button to turn it on,
    (ii) Press EITHER (2nd)0 for CATALOG or ELSE (2nd)VARS for DISTR .
    (iii) For P(X<=x), scroll down until the cursor is next to poissoncdf(  and press ENTER . Select poissonpdf(  for P(X=x).
    (iv) The basic syntax for the two commands in (iii) is poissoncdf(mu,x) and poissonpdf(mu,x). For example, assuming that you want P(X<=x), wait for a window with poissoncdf(  to appear. Enter the value of mu then COMMA (that is, press the button with a comma on it) then x then ) (the right-parenthesis button) then ENTER. The probability P(X<=x) should then appear.
    (v) To find P(X>=x), note that P(X>=x) = 1 - P(X<=x-1) for any integer-valued random variable X.  You can calculate probabilities like P(a<=X<=b) by subtraction.

    8. Find P(X<=x) where X has a Student's t, Chi-square, or F distribution     Go to top of this page

    In Microsoft Excel:     (See Math320 Excel Notes)

    Click on an empty cell. Then click on fx on the icon menubar, Statistical..., and then TDIST, CHIDIST, or FDIST. Follow the directions. Note that several different options are controlled by entering codes like 1 or 2 or 3 in edit lines.

    Using a TI-83:

    (i) Press the ON button to turn it on,
    (ii) Press (2nd)VARS for DISTR,
    (iii) Press 5 for 5:tcdf(,, 7 for 7:X2cdf(, or 9 for 9:Fcdf(,
    (iv) A screen will appear in which you can enter the parameters for the distribution value. The syntax for tcdf and X2cdf is (Function)(Lower,Upper,df). The syntax for Fcdf is Fcdf(Lower,Upper,numdf,denomdf). For example, to find P(X>=1.645) where X has a Student's t-distribution with 11 degrees of freedom, enter (in order) 1.645 then COMMA then 1 2ndCOMMA (for EE) 99 (for 1E99) then COMMA then 11 then )(right parenthesis) then ENTER. The number 0.0641075923 should appear. Chi-square and F distributions are restricted to nonnegative values, so that you can enter 0 (zero) in place of -1E99 for the lower bound for 7:X2cdf( and 9:Fcdf(.
    Note: tcdf( accepts fractional numbers of degrees of freedom, so that it can be used to get an exact value for Satterthwaite's test. Fcdf( does not accept fractional numbers of degrees of freedom on my TI-83, but may on newer calculators.

    9. One-sample and Two-sample T tests:     Go to top of this page

    Given one or two normal samples with UNKNOWN population standard deviations, test H0:muX=mu0 (one sample) or else H0:muX=muY (two samples).
    In Microsoft Excel:
    Click on Tools then Data Analysis.... then one of
    t-Test: Paired Two Sample for Means
    t-Test: Two-Sample Assuming Equal Variances
    t-Test: Two-Sample Assuming Unequal Variances
    Follow the directions. See the Math320 Excel Notes for more details.

    Using a TI-83:
    After you have turned on the TI-83 and possibly reset or cleared it, enter STAT, then TESTS and then either 2: for a (one-sample) T test or 4: for a 2-sample T test. In either case, the first step will be to either highlite either Data or Stats.

    If your data is in terms of the samples themselves, make sure that Data is highlighted. Press Enter to make sure that your choice of Data or Stats is fixed. Fill in mu0 (for the one-sample test) and the lists (L1 or L1 and L2) that you have entered your data in. (See 1. Calculating sample mean and sample standard deviations for discussion about using Lists with a TI-83.) Leave any Freq entries at 1.  Choose a two-sided test, lower-tailed one-sided test, or upper-tailed one-sided test and press Enter to make sure that the choice is set. In the 2-sample case, select Pooled=Yes for the Classical 2-sample t-test or Pooled=No for the Satterthwaite Test. Finally, highlite Calculate and press Enter.
    If your data is in terms of summary statistics (either Xbar and sX or else Xbar, sX, Ybar, and sY), highlite Stats instead of Data and continue in the same way. Enter the sample standard deviation or standard deviations rather than the sample variance or variances.
    (If you are suspicious about whether the TI-83's version of the test for Pooled=No is the same as the Satterthwaite test, enter the summary two-sample data on page 403 of the text into the TI-83 for a 2-sample T test with Stats highlighted. Set Pooled=No. Note that the values of Xbar and Ybar on page 403 should be 0.41 and 0.38 instead of 4.1 and 3.8 (see page 404), and that page 403 lists the sample variances of the two samples, so that you will have to take square roots before entering sample standard deviations. Then the TI-83 will calculate exactly the same values for the degrees of freedom and the T statistic as on pages 403 and 404.)

    10. Given a sample of size n for which x=k have a Property P, find the 95% confidence interval for the population proportion of Property P based on a normal approximation.     Go to top of this page

    In Microsoft Excel:     (See Math320 Excel Notes)

    Microsoft Excel will compute the half-width of a normal confidence interval (that is, if the population standard deviation can be assumed to be known) using the CONFIDENCE function in the Statistical menu. Excel does not seem to offer any other help on confidence intervals.

    Using a TI-83:
    After you have turned on the TI-83 and possibly reset or cleared it, enter STAT, then TESTS, then scroll down to A:1-PropZInt. (Alternatively, you can enter STAT, then TESTS, then ALPHA then MATH . The ALPHA key is a kind of alternative shift key, which produces A when you enter MATH .)
    On the screen that appears, fill in X: (the number of ``successes''), N: (the number of trials), and make sure that C-Level: is set at 0.95 . Scroll down to CALCULATE and press ENTER. The normal-theory interval should appear. What confidence interval do you get when you enter zero (0) for the number of successes?

    11. Given data X1,  X2,  ... Xn,  find a ``Student's-t'' confidence interval for E(X) based on the assumption that Xi are normal:
    In Microsoft Excel:    
    (See Math320 Excel Notes)

    Microsoft Excel does not seem to offer any help on this question, although the Regression option in the Data Analysis Toolpak finds Student-t confidence intervals for regression parameters.

    Using a TI-83:
    After you have turned on the TI-83 and possibly reset or cleared it, enter STAT, then 1:EDIT, then enter your sample into list L1.
    Enter STAT again, then TESTS, then 8:TInterval. (That is, either scroll down to this entry and press ENTER or else just enter 8 .) The next screen should show DATA (if not, highlite it), List:L1 , and C-Level: 0.95 for a 95% confidence interval. Scroll down to highlite Calculate and press Enter. The screen may go blank, but after a few seconds a screen with the confidence interval should appear.

    Note: If you already know Xbar and sX, and don't want to enter the data into the TI-83, then enter STAT, then TESTS, then 8 for 8:TInterval, then highlite STATS (instead of DATA) and press ENTER. A screen will appear that will allow you to enter Xbar, sX, and the sample size n directly.

    12. Do a one-sample or two-sample t-test     Go to top of this page

    In Microsoft Excel:     (See Math320 Excel Notes)

    You can use either Excel statistical functions or the Data Analysis Toolpak. In either case, first enter the two samples in two columns (or two rows) of cells.
    (i) To use the Excel Statistical functions, click on an empty cell and then fx on the icon menubar. Click on Statistical... and then double-click on TTEST. Follow the instructions. The TTEST function has options for (a) one-sample paired t-tests, (b) the pooled variance (usual) two-sample t-test, and (c) an unequal-variance (presumably Satterthwaite) two-sample t-test.
    The TTEST function calculates and displays the one-sided or two-sided P-value in the cell that you have highlighted, but does not show the value of the test statistic. For more information (such as the value of the test statistic and the means and standard deviations of the two samples), you must use the Data Analysis ToolPak.
    (ii) See Notes on using Excel above for how to use the Data Analysis ToolPak. After you enter the ToolPak, you will see options for (a,b,c) in part (i) above. Click on (for example) ``t-test: Two Sample assuming Equal Variances''. Note that there is also an option to carry out an F-test for the equality of variances for two samples.
    Data can be entered in either two columns or in two rows. The ToolPak output will be a table of values with the means and standard deviations of both samples, the two sample sizes, the two-sample test statistic and the number of degrees of freedom, and both one-side and two-sided P-values. Click on the ``Output Range'' button on the window and enter the name of an empty cell. The table will be written to a block of cells with that cell as the upper-left corner.
    If you don't click on ``Output Range'', then Excel will write this small 14-row by 3-column table to a separate ``worksheet'' in your spreadsheet, and you will no longer be able to see your data unless your return to your first ``worksheet''. See Notes on ToolPak Output Range for more details.

    Using a TI-83:
    After you have turned on the TI-83 and possibly reset or cleared it,

    (a) Enter STAT, then 1:EDIT, then enter the first sample (Xi) into list L1 and the second sample (Yi) into list L2. (If you only have one sample, use just list L1.)
    (b) Enter STAT then TESTS and either 2 for 2:T-Test or 4 for 4:2-SampTTest. Press ENTER and fill in the screen that appears. Nothing will happen until you highlight eiter CALCULATE or DRAW at the bottom of the screen and press ENTER. After a few seconds, the value of the T-statistic and the P-value will appear.

    13. Given paired data (X1,Y1),  (X2,Y2),  (X3,Y2),  ...,  (Xn,Yn),  find     Go to top of this page

    (a) The Pearson correlation coefficient between Xi and Yi and
    (b) The coefficients of the linear regression Y = aX+b
    In Microsoft Excel:     (See Math320 Excel Notes)
    You can use either the Statistical Functions CORREL or LINEST or else the Data Analysis ToolPak functions CORRELATION or REGRESSION. The REGRESSION function gives the most information. Both CORRELATION and REGRESSION assume that two samples are entered as COLUMNS. Both will give you output if you enter data in two rows, but will assume that you have n-1 different kinds of predictor variables with two observations for each set of covariates. It will then carry out a (correct) analysis of a totally different problem. CORREL and LINEST allow you to enter the samples as two rows or two columns. Of the four functions, only REGRESSION does a complete statistical analysis. It returns several tables with the correlation coefficient and an ANOVA table with regression coefficients, P-values, and Student-t confidence intervals.
    The Statistical Function CORREL gives the Pearson correlation coefficient and LINEST gives the linear regression slope. Data can be entered in either two columns or in two rows for CORREL and LINEST. To get more information than this, you must use Data Analysis ToolPak options.
    The CORRELATION function gives correlation coefficients but no P-values.

    Using a TI-83:
    After you have turned on the TI-83 and possibly reset or cleared it,

    (a) Enter STAT, then 1:EDIT, then enter the Xi into list L1 and Yi into list L2
    (b) If you want the TI-83 to calculate the Pearson correlation coefficient r (and the Model R2 = r2) for the linear regression, you must turn ``Diagnostics'' ON by entering 2nd 0 (for CATALOG), space down to DiagnosticOn, press ENTER, and then ENTER again if you see DiagnosticOn on a different screen,
    (c) Enter STAT then CALC then 4 for 4:LinReg(ax+b). When a new screen appears, enter 2nd 1 for list L1, then COMMA (the comma key), then 2nd 2 for list L2, then ENTER. After a few seconds the coefficients a,b of the regression Y=aX+b will appear. If you entered DiagnosticOn in (b), then r2 and r will also appear.

    14. One-way ANOVA for d samples     Go to top of this page
    In Microsoft Excel::     (See Math320 Excel Notes)

    Enter the data for the d samples in d adjacent columns or d adjacent rows.
    Use the Data Analysis Toolpak by entering Tools then Data Analysis... then Anova: Single factor. If the Tools dropdown menu does not have a Data Analysis... option, see Data Analysis ToolPak. When the ToolPak ANOVA window appears, enter the range of cells as the upper-left and lower-right corner of a rectangle of cells.
    The data can either be entered with one sample in each column OR as one sample in each row. You must indicate which. If you leave the ``by columns'' box checked when each sample is in a different row, then Excel will analyze the wrong model. Excel knows which cells are occupied and which are not, so that unequal sample sizes can be handled as well as equal sample sizes.
    The output will include an ANOVA table with the ANOVA F-test as well as sample means and standard deviations for the d ``treatments''.

    Using a TI-83:
    After you have turned on the TI-83 and possibly reset or cleared it,

    (a) Enter STAT, then 1:EDIT, then enter the d samples in your data into d lists L1 through Ld. (For example, L1 through L4 if there are four ``treatments''.)
    (b) Enter STAT then TESTS then F:ANOVA( (for example, by entering ALPHA then COS for F). If d=4 for four treatments, the syntax is ANOVA(L1,L2,L3,L4). You will have to scroll through several screens for the entire output.

  • Click here for the top of this page

    Last modified July 23, 2001