In excel, how to find standard deviation when the data available is the values and the number of their occurrences?

I'm trying to find the standard deviation of a dataset, but I do not have a sheet with all of the data. The information that I was given is each value (numbers -4 through 10) in one column, and their number of occurrences in another column.1

I was able to find the average of the data by multiplying the value by its occurrence, then summing and dividing, but I'm having trouble finding what to do for standard deviation. Is there a way to calculate it directly, or maybe I could make a new data table with only the values?

2 Answers

You can calculate them using these formulas:

Mean:

=SUMPRODUCT(A2:A11,B2:B11)/SUM(B2:B11)

Variance:

=SUMPRODUCT((A2:A11-E1)^2,B2:B11)/(SUM(B2:B11)-1)

Standard Deviation (square root of Variance):

=SQRT(E2)

You can convert your data into a linear list and take the standard deviation of this list.

Say the values are in column A and the number of occurrences are in column B. In C1 enter 1 and in C2 enter:

=C1+B1

and copy C2 downwards. In D1 enter:

=IF(ROWS($1:1)>SUM(B:B),"",INDEX(A:A,MATCH(ROWS($1:1),C:C,1)))

and copy downwards. Column D is an "expanded" version of column A. Finally use:

=STDEV(D:D)