How to Calculate Weighted Average in LibreOffice Calc
Calculating a weighted average in LibreOffice Calc is essential when
different data points contribute unequally to the final result, such as
in graded coursework, investment portfolios, or inventory costs. This
guide outlines the mathematical functions available in LibreOffice Calc
to compute weighted averages efficiently, focusing on the dedicated
AVERAGE.WEIGHTED function, the versatile
SUMPRODUCT method, and dynamic array calculations.
1. The
AVERAGE.WEIGHTED Function
LibreOffice Calc includes a built-in function designed specifically
for weighted calculations: AVERAGE.WEIGHTED. This is the
simplest and most direct method.
Syntax:
=AVERAGE.WEIGHTED(Values, Weights)
- Values: The range containing the numbers to be
averaged (e.g.,
A2:A6). - Weights: The range containing the corresponding
weights assigned to each value (e.g.,
B2:B6).
Example: If your scores are in A2:A6
and their corresponding weights are in B2:B6, enter the
following formula: =AVERAGE.WEIGHTED(A2:A6, B2:B6)
2. The
SUMPRODUCT and SUM Combination
The standard mathematical approach across all spreadsheet software
uses the SUMPRODUCT function divided by the
SUM function. This method is universally compatible across
older versions of Calc and other spreadsheet tools.
Mathematically, a weighted average multiplies each value by its weight, adds those products together, and divides the total by the sum of the weights.
Syntax:
=SUMPRODUCT(Values, Weights) / SUM(Weights)
Example: Using values in A2:A6 and
weights in B2:B6:
=SUMPRODUCT(A2:A6, B2:B6) / SUM(B2:B6)
SUMPRODUCT(A2:A6, B2:B6)calculates(A2*B2) + (A3*B3) + ... + (A6*B6).SUM(B2:B6)calculates the total weight.- Dividing the two gives the accurate weighted average.
3. Array Formula with
SUM
You can also use a custom array formula utilizing only the
SUM function to perform the multiplication and summation in
one step.
Syntax:
=SUM(Values * Weights) / SUM(Weights)
Example:
=SUM(A2:A6 * B2:B6) / SUM(B2:B6)
Note: In older versions of LibreOffice Calc, you must press Ctrl + Shift + Enter instead of just Enter to evaluate this as an array formula.