How to Do a Statistical Analysis in Excelby Darrin Koltow
Using Excel for statistical analysis helps you clearly see trends in your numerical data. For example, you may be a teacher running Excel's "Average" function on student scores for a test you wrote. If that function returns 60 percent, you may conclude that your test was too difficult. To perform statistical analysis with Excel, run one of the program's statistical formulas on a list of numbers in a workbook. Excel lists these formulas in its "Formulas" tab.
Type a list of at least five numbers ranging from about 50 to 100 in a column of cells. These numbers represent hypothetical test scores of students. The highest possible score is 100 and the lowest is 0. You'll use Excel's statistical functions to analyze the scores.
Click the cell below the last score you typed, then click the "More functions" button of the "Formulas" tab. Click the "Statistical" item, then click the "Average" function, which yields what number the list as a whole tends to be gravitating toward.
Click the gridded button next to the top text box of the dialog box that appears. Pressing the button enables you to select the range of cells you'd like to give as input to the "Average" function.
Click the cell of the top number you entered, then drag the mouse to the bottom number and release the mouse. Press "Enter" to make Excel enter the average function with your list of test scores into the workbook. The cell in which you entered the function now displays the average of the scores.
Use the instructions from steps 2 through 4 to insert the "Min" function in the cell below your test scores. This function tells you the smallest score in the list. While you can determine this number by visually scanning the list, the "Min" function can quickly discern the smallest number from a long list of numbers.
Use instructions 2 through 4 to insert the "Max" function into the worksheet. This function reveals your list's largest score. Insert the "Stddev" function next. This function computes the average distance of all test score from the average score. Standard deviation gives you a sense of how much variation exists in the scores. For the test scores example, if Excel computes a standard deviation of "2," this means that the average difference of each score from the figure returned by the "Average" function is only 2 percentage points, out of a scale of 100 points.