See also: our Excel Histogram page and our Excel Scatterplot page
| Getting started | |||||||||
| Find the Microsoft Excel icon (it's green and has the X - L logo on it). If it's not already on your desktop, try Start>Programs>Microsoft Office>Excel | |||||||||
| Clearing the memory | |||||||||
| If you want a clear spreadsheet, just open a new one (press Ctrl O). | |||||||||
| Entering data | |||
| one variable | |||
| For this illustration, the list will start at A1 and go down, but you can start your list in any column and cell you like. (If your list has a heading, the list may begin at A3 or B3 or whatever.) Click on cell A1. Enter the first number and press ENTER. Enter the second number and press ENTER. Continue until the end of the list. | |||
| two variables | |||
| For this illustration the x and y lists will go into columns A and B respectively. Of course, you can use whichever columns and start in whichever cell you like. (As before, if your list has a heading, the list may begin at A3 or B3 or whatever.) Click on cell A1. Enter the first x-value and press ENTER. Enter the second x-value and press ENTER. Continue until all of the x-values have been entered. Then click on cell B1, and enter the y values the same way, pressing ENTER after each one. Make sure the corresponding x-values and y-values match up! | |||
| Calculating one-variable statistics
The (small) data set used in these examples is: 15, 16, 20, 21. | ||||
| mean | ||||
| Click in an unoccupied cell, for example A5. Then type =average(a1:a4) where a1 is the first element in the data list, and a4 is the last element in the data list. (So if your list goes from a3 to a6, type =average(a3:a6).) The mean will appear in the A5 box. You should get 18 as the mean. | ||||
| standard deviation for populations (s or sn) | ||||
| Click in an unoccupied cell, for example A6. Then type =stdevp(a1:a4) where a1 is the first element in the data list and a4 is the last element in the data list. The standard deviation will appear in the A6 box. You should get 2.54951 as the population standard deviation. | ||||
| standard deviation for samples (s or sn-1) | ||||
| Click in an unoccupied cell, for example A7. Then type =stdev(a1:a4) where a1 is the first element in the data list and a4 is the last element in the data list. The standard deviation will appear in the A7 box. You should get 2.94392 as the sample standard deviation. | ||||
|
Calculating two-variable statistics For this illustration the (small) data set consists of the following ordered pairs (x, y): (1,2), (2,4), (3,5), (4,7). | |||||
| r (correlation) | |||||
| Click in an unoccupied cell, for example C1. Then type =correl(b1:b4,a1:a4) where the first interval gives the cells containing y-values and the second interval gives the cells containing x-values. (NOTE: Because Correlation(X,Y)=Correlation(Y,X) the order doesn't really matter here; however, this order is consistent with the order you MUST use for regression coefficients below.) The correlation will appear in the C1 box. You should get 0.992278 as the correlation. | |||||
| regression coefficients | |||||
| slope | |||||
| Click in an unoccupied cell, for example C2. Then type =slope(b1:b4,a1:a4) where the first interval gives the cells containing the y-values and the second interval gives the cells containing x-values. (NOTE: the order matters here.) The slope will appear in the C2 box. You should get 1.6 as the slope of the regression line. | |||||
| y-intercept | |||||
| Click in an unoccupied cell, for example C3. Then type =intercept(b1:b4,a1:a4) where the first interval gives the cells containing the y-values and the second interval gives the cells containing the x-values. (Again: the order matters here.) The slope will appear in the C3 box. You should get 0.5 as the y-intercept of the regression line. | |||||
| Calculating combinations and permutations | ||||
| combinations (nCk) | ||||
| Click in an unoccupied cell, for example C4. Then type =combin(your n-value, your k-value), i.e. "10 choose 6" would be =combin(10,6). The number of combinations will appear in the C4 box. It should be 210. | ||||
| permutations (nPk) | ||||
| Click in an unoccupied cell, for example C5. Then type =permut(your n-value, your k-value), i.e. 9 P 5 would be =permut(9,5). The number of permutations will appear in the C5 box. It should be 15120. | ||||
| Ending the Excel session | ||
| Click the little x in the upper right hand corner of the screen. A dialog box will appear asking if you want to save the file. Click on "yes" if do and "no" if you don't. If you say "yes", it will ask you for a filename. | ||
Go to: