How to Use Excel to Calculate Proportions With Mean & Standard Deviations
by Sly TutorA 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.
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.
Input the denominator of each proportion into column B. Be sure to line up the denominators with their respective numerators.
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.
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.
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
Photo Credits
- photo_camera Comstock/Comstock/Getty Images