week 12

OLP 4404-5504: Evaluation in Corporate and Professional Technical Education 1

Using Excel for Statistics Many basic statistics can be done with Excel or another spreadsheet like the Google drive spreadsheet. These instructions are written for Excel 2010 and although your version of Excel may vary, these instructions should give you a general guide for using each of these functions. You can either type in the functions manually, or you can use the computer to help you find and complete the functions. For more information and examples for any of the functions, use the Help feature in Excel. To use the computer to insert the functions:

1. Go to the cell where you would like your function to appear 2. Go to the Formula menu and select Insert Function 3. In the top box (“Search for a function”) type the function for which you are looking and

click Go OR select a function from the scrollable list. 4. Select the function you would like to use from the list and click OK 5. The computer starts the function in your current cell and makes its best guest as to the

range of data you want for this function. Adjust the range if necessary. 6. Follow the prompts to add any other necessary information to complete the function and

press OK. To enter functions manually Mean

In Excel, you use the average function. 1. Go to the cell where you would like your mean to appear 2. Type: =average( 3. Click and drag on the range of data you want to average. 4. Type the right parenthesis and press Enter

Median

1. Go to the cell where you would like the median to appear. 2. Type: =median( 3. Click and drag on the range of data for which you want to find the median. 4. Type the right parenthesis and press Enter

Mode

1. Go to the cell where you would like your mode to appear. 2. Type: =mode( 3. Click and drag on the range of data for which you want to find the mode. 4. Type the right parenthesis and press Enter

OLP 4404-5504: Evaluation in Corporate and Professional Technical Education 2

Range Excel does not have a function for range, but it does have a minimum function which returns the lowest number from a set of data, and a maximum function which returns the highest number from a set of data.

Minimum 1. Go to the cell where you would like your minimum number to appear 2. Type: =min( 3. Click and drag on the range of data for which you want to find the minimum

value. 4. Type the right parenthesis and press Enter

Max

1. Go to the cell where you would like your maximum number to appear 2. Type: =max( 3. Click and drag on the range of data for which you want to find the maximum

value. 4. Type the right parenthesis and press Enter 5.

Standard Deviation

1. Go to the cell where you would like your standard deviation to appear 2. Type: =stdev( 3. Click and drag on the range of data for which you want to determine the standard

deviation. 4. Type the right parenthesis and press Enter 5.

Correlation

1. Go to the cell where you would like your correlation coefficient (r) to appear 2. Type: =correl( 3. Click and drag on the first range of data for your correlation 4. Type a comma 5. Click and drag on the second range of data for your correlation 6. Type the right parenthesis and press Enter

Misc. Formatting numbers

1. Highlight the cells you would like to format 2. From the Home Menu, select formatting options from the font, alignment, number, styles,

and cells panels. Click on the numbers tab

OLP 4404-5504: Evaluation in Corporate and Professional Technical Education 3

NOTE: In all these examples, I suggest you click and drag over the numbers that will be acted upon by the formula. Alternatively, you can type in the range directly. For example: in this formula

=median(A2:A15) you can click and drag from A2 to A15, or you can just type A2:A15