How to Do a Statistical Distribution Analysis in Excel for Mac
Microsoft Excel for Mac OS X has a similar layout as the Windows version and operates much the same way. You can use functions to have Excel do all sorts of calculations, such as finding the mean average of a set of numbers, the standard deviation or the frequency a set of numbers is found within a data set.
Other People Are Reading
Instructions
-
Finding Functions
-
1
Launch Excel and open a new worksheet.
-
2
Familiarize yourself with the functions available in Excel by clicking the arrow beside the “Autosum” button. Select “More Functions.” The Paste Function window opens. Functions are categorized in the left menu and listed on the right menu.
-
-
3
Click “Statistical” in the left menu. A list statistical functions appears in the right menu.
-
4
Click “Average” in the right window. The proper format for using “Average” -- as well as a description of the function -- appears at the bottom of the window.
-
5
Select “STDEV,” then “FREQUENCY” to see the format they require and a description of the function. Click “Cancel” to close the Paste Function window.
Populating Cells With Statistical Data
-
6
Type “Scores,” “Bands” and “Distribution” in the first row of cells to label the first three columns.
-
7
Type a series of scores from “0” to “100” in cells “A2” to “A11” in the “Scores” column. These would represent, for example, exam scores based on a perfect mark of 100 in a class of 10 students.
-
8
Type “10,” “20,” “30” and so on, in increments of 10, to "100" in cells “B2” to “B11” in the “Bands” column. Each band represents scores up to that number. For example, “10” would be for a score of “0” to “10” and “20” would represent scores of “11” to “20.”
Analyzing the Data
-
9
Type “Average” in cell “A13.” Type “SD,” for “Standard Deviation,” in cell “A14.”
-
10
Click cell “B13.” Calculate the average of the scores by typing “=AVERAGE(A2:A11)” and press “Enter.” Alternatively, type “=AVERAGE(“ and highlight cells “A2” through “A11” with the mouse while holding down the mouse button, then press “Enter.”
-
11
Click cell “B14.” Calculate the standard deviation by typing “=STDEV(A2:A11)” in the cell and pressing “Enter.” Again, instead of typing “A2:A11” you can drag the mouse over the cells to select them for the function.
-
12
Click cell “C2” and hold down the mouse button. Drag the cursor to “C11” so cells “C2” through “C11” are highlighted. These are the cells you will be populating with the statistical analysis.
-
13
Type “=FREQUENCY(Scores,” then drag the cursor over cells “B2” through “B11” to highlight all of the bands. Type “)” to close the function. Hold down the “Shift” and “Command” keys, then press “Enter.” Excel automatically populates the third column with the correct data.
-
1
