How to Use Excel to Calculate Proportions With Mean & Standard Deviations

By Sly Tutor

i Comstock/Comstock/Getty Images

A proportion is a relationship between two numbers. Proportions normally express some number out of some other number. In math, "out of" means division, so if 23 out of 30 students passed a class, the proportion of students that passed the class is 23/30. To find the mean of a set of proportions, you simply take the average of the fractions. You can do this with a spreadsheet program like Excel that has an average function. It also has a standard deviation function, which helps you to figure out how spread out the data points are.

Step 1

Input the numerator of each proportion into column A of an Excel spreadsheet. A proportion expresses X out of Y, so the numerator is X and the denominator is Y.

Step 2

Input the denominator of each proportion into column B. Be sure to line up the denominators with their respective numerators.

Step 3

Select cell C1. Type "=A1/B1" in the formula bar above the spreadsheet. Press enter. Drag down this formula throughout column C. Column C should now display the proportions for each line of data.

Step 4

Select the cell immediately below the last proportion in column C. Type "=AVERAGE(C1:Cx)" in the formula bar above the spreadsheet, where "x" is the line number of the last line of data. For example, if you have 10 proportions, type "=AVERAGE(C1:C10)" in the formula box. Press enter. The bottom cell in column C should now display the mean of the proportions.

Step 5

Select cell D1. Type "=STDEV(C1:Cx)" in the formula bar above the spreadsheet, where "x" is the line number of the last line of data. For example, if you have 10 proportions, type "=STDEV(C1:C10)" in the formula box. Press enter. Cell D1 should now display the standard deviation of the proportions.

References

Writer Bio