How to compute grouped mean and SD in Excel:

To compute mean and standard deviation for a regular list of date (not grouped data): If the data are in a single column, say column A, starting from row 1 and having 100 numbers, then you type (in some cell where you wish to store the mean)

 =AVERAGE(A1:A100)

 and for the SD,

 =STDEV(A1:A100)

 The data don’t have to be in a single column or row, however. If they are in five columns of 10 numbers in each column, then you can type

 =AVERAGE(A1:E10)

 and

 =STDEV(A1:E10)

 For grouped data it’s a bit more complex. Click here for the spreadsheet that we used in class do demonstrate grouped data. The rest of this page explains this spreadsheet. There’s a few things to point out here:

Column A contains the x-values (x_i)
Column B contains the frequencies (f_i)
In Column C, we form the products f_i*x_i. Starting with row two, type in cell C2 the formula

 =A2*B2

 Now you can copy this cell and paste it in the rest of column C; Excel automatically changes the number 2 to the correct row number each time.

 We now have the products f_i*x_i, and can calculate their sum in cell C11:

 =SUM(C2:C9)

 Or, if we had already entered the sum formula for column B, you can copy and paste the formula in cell B11 to cell C11. Again, Excel automatically changes “B” to “C” when you do this.

 We can now calculate the average by typing, in cell G3:

 =C11/B11

 Next, to get the SD, we need to compute the squared deviations from average and multiply each by the frequency. This is Column D. In cell D2, type

 =B2*(A2-$G$3)^2

 And then copy this cell and paste down the rest of the column. To get the total in cell D11, copy and paste from either B11 or C11.

 Notice the $ signs in the last formula. This tells Excel that when copying and pasting, it should not change the “G” and the “3” with each new row number, but keep them fixed.

 Finally, we get the SD in cell G6 by typing

 =SQRT(D11/B11)