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

by Sly Tutor
Spreadsheets now use formulas to automatically calculate equations.

Spreadsheets now use formulas to automatically calculate equations.

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.

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.

2

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

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.

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.

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.

About the Author

Sly Tutor has been a writer since 2005 and has had work appear in the "Altoona Mirror" newspaper. She holds a Bachelor of Science in microbiology from Pennsylvania State University.

Photo Credits

  • photo_camera Comstock/Comstock/Getty Images