Example of using the Calculator to calculate the weighted mean and the standard deviation

If you know the formula for a statistic, you can use the Calculator to calculate it. For example, you can use the Calculator to calculate the weighted mean and the weighted standard deviation for a column of numbers.

Calculate the weighted mean

  1. Choose File > New > Worksheet.
  2. In the name cell for C1, type Data.
  3. Copy and paste, or type the following data into C1.
    49.01
    2.20
    16.70
    93.97
    6.16
    27.75
  4. In the column name cell for C2, type Weights.
  5. Copy and paste, or type the following data into C2.
    5
    12
    8
    14
    9
    4
  6. Choose Calc > Calculator.
  7. In Store result in variable, type Weighted Mean.
  8. In Expression, copy and paste, or enter SUM(C1*C2)/SUM(C2)
  9. Click OK.

Results

The weighted mean is 36.2898.

C1 C2 C3
Data Weights Weighted Mean
49.01 5 36.2898
2.20 12
16.70 8
... ...

Calculate the weighted standard deviation

You must calculate the weighted mean before you calculate the weighted standard deviation.

Note

The square of the weighted standard deviation is the weighted variance.

  1. Choose Calc > Calculator.
  2. In Store result in variable, enter Weighted SD.
  3. In Expression, copy and paste, or enter SQRT(SUM(C2*(C1-C3)^2 )/((SUM(C2/C2)-1)*SUM(C2)/SUM(C2/C2)))
    Note

    If the Weights column contains a 0, you will get an error because you can't divide by 0.

  4. Click OK.

Results

The weighted standard deviation is 41.0060.

C1 C2 C3 C4
Data Weights Weighted Mean Weighted SD
49.01 5 36.2898 41.0060
2.20 12
16.70 8
... ...