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

by Sly Tutor

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.

Video of the Day

Brought to you by Techwalla
Brought to you by Techwalla

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.

More Articles

Photo Credits