How to Calculate Weighted Average in Google Sheets

Key Insights

• The SUMPRODUCT/SUM combination (=SUMPRODUCT(values, weights) / SUM(weights)) works universally across spreadsheet applications, while Google Sheets’ native AVERAGE.WEIGHTED function offers cleaner syntax but less flexibility for complex scenarios.
• Weighted averages prevent misleading conclusions when data points have unequal importance—a 4-credit course should impact your GPA four times more than a 1-credit course.
• Always validate your weight column for blanks and zeros before calculating; a single zero weight or empty cell can silently corrupt your results or throw cryptic errors.

Introduction

A simple average treats every data point equally. That’s fine when you’re calculating the mean temperature over a week, but it falls apart when data points carry different levels of importance.

Consider a student with these grades: 95 in a 1-credit seminar and 75 in a 4-credit core course. A simple average gives 85. But that’s misleading—the core course should matter four times more. The weighted average correctly calculates to 79, reflecting academic reality.

Weighted averages appear everywhere: GPA calculations, investment portfolio returns, customer satisfaction scores with response volumes, pricing with quantity discounts, and survey results weighted by demographic representation. If you work with data in Google Sheets, you’ll eventually need this calculation.

Understanding the Weighted Average Formula

The mathematical formula is straightforward:

Weighted Average = Σ(value × weight) / Σ(weights)

In plain English: multiply each value by its weight, sum all those products, then divide by the sum of all weights.

Here’s the concept visually:

Values:    [90]    [80]    [70]
Weights:   [2]     [3]     [5]
           ↓       ↓       ↓
Products:  [180]   [240]   [350]
           \_______↓_______/
           Sum of Products: 770
           Sum of Weights:  10
           ─────────────────
           Weighted Average: 77

You can calculate this manually in Google Sheets using basic arithmetic. Assume values are in column B and weights are in column C:

| A        | B     | C      | D              |
|----------|-------|--------|----------------|
| Item 1   | 90    | 2      | =B2*C2         |
| Item 2   | 80    | 3      | =B3*C3         |
| Item 3   | 70    | 5      | =B4*C4         |
| Totals   |       | =SUM(C2:C4) | =SUM(D2:D4) |
| Result   |       |        | =D5/C5         |

This manual approach works but clutters your spreadsheet with helper columns. Let’s look at cleaner methods.

Method 1: Using SUMPRODUCT and SUM

The SUMPRODUCT function multiplies corresponding elements in arrays and returns the sum of those products. Combined with SUM, it handles weighted averages in a single formula without helper columns.

The syntax:

=SUMPRODUCT(values_range, weights_range) / SUM(weights_range)

Here’s a practical example calculating a student’s weighted GPA:

|   | A              | B     | C            |
|---|----------------|-------|--------------|
| 1 | Course         | Grade | Credit Hours |
| 2 | Calculus       | 3.7   | 4            |
| 3 | English        | 4.0   | 3            |
| 4 | Physics        | 3.3   | 4            |
| 5 | Art History    | 3.9   | 2            |
| 6 | Lab            | 4.0   | 1            |
| 7 |                |       |              |
| 8 | Weighted GPA   |       |              |

In cell B8, enter:

=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)

This returns 3.64—the correct weighted GPA.

Breaking down what happens:

1. SUMPRODUCT multiplies each grade by its credit hours: (3.7×4) + (4.0×3) + (3.3×4) + (3.9×2) + (4.0×1) = 50.8
2. SUM totals the credit hours: 4 + 3 + 4 + 2 + 1 = 14
3. Division gives the weighted average: 50.8 / 14 = 3.63

The SUMPRODUCT/SUM method has one major advantage: it works in Excel, LibreOffice Calc, and virtually every spreadsheet application. If you share files across platforms, stick with this approach.

Method 2: Using AVERAGE.WEIGHTED (Google Sheets Native)

Google Sheets introduced AVERAGE.WEIGHTED to simplify this exact calculation. The syntax is cleaner and more readable:

=AVERAGE.WEIGHTED(values, weights)

Using the same GPA example:

=AVERAGE.WEIGHTED(B2:B6, C2:C6)

That’s it. Same result (3.64), less typing, clearer intent.

The function accepts multiple value/weight pairs for complex scenarios:

=AVERAGE.WEIGHTED(values1, weights1, [values2, weights2], ...)

This proves useful when combining data from multiple ranges:

=AVERAGE.WEIGHTED(B2:B6, C2:C6, E2:E4, F2:F4)

One caveat: AVERAGE.WEIGHTED is Google Sheets-specific. If you export to Excel or share with users on other platforms, the formula breaks. For portable spreadsheets, stick with SUMPRODUCT/SUM.

Handling Edge Cases

Real-world data is messy. Your weighted average formulas need to handle blanks, zeros, and errors gracefully.

Blank Cells

Both methods handle blank cells differently. SUMPRODUCT treats blanks as zeros, which can silently skew results. AVERAGE.WEIGHTED throws an error if it encounters blanks in the weights range.

Filter out blanks explicitly:

=SUMPRODUCT((B2:B10<>"")*(B2:B10), (C2:C10<>"")*(C2:C10)) / SUMIF(C2:C10, "<>", C2:C10)

Or use FILTER for cleaner logic:

=AVERAGE.WEIGHTED(FILTER(B2:B10, B2:B10<>"", C2:C10<>""), FILTER(C2:C10, B2:B10<>"", C2:C10<>""))

Zero Weights

A weight of zero means “this value doesn’t count.” SUMPRODUCT handles this correctly—the value contributes nothing. AVERAGE.WEIGHTED also handles zeros properly, excluding them from the calculation.

However, if all weights are zero, you’ll get a division-by-zero error. Wrap your formula:

=IF(SUM(C2:C10)=0, "No valid weights", SUMPRODUCT(B2:B10, C2:C10)/SUM(C2:C10))

Error Handling with IFERROR

For production spreadsheets, always wrap weighted average calculations with error handling:

=IFERROR(AVERAGE.WEIGHTED(B2:B10, C2:C10), "Check data")

Or provide more specific feedback:

=IF(COUNTA(B2:B10)=0, "No values entered",
  IF(SUM(C2:C10)=0, "Weights sum to zero",
    IFERROR(AVERAGE.WEIGHTED(B2:B10, C2:C10), "Invalid data")))

Data Validation

Prevent errors at the source by adding data validation to your weight column:

1. Select the weights range (C2:C10)
2. Go to Data → Data validation
3. Set criteria to “Number greater than or equal to 0”
4. Check “Reject input” for invalid data

This stops users from entering negative weights or text values that would corrupt calculations.

Practical Example: Portfolio Return Calculator

Let’s build a complete investment portfolio tracker that calculates weighted average returns.

Set up your spreadsheet:

|   | A          | B           | C            | D              |
|---|------------|-------------|--------------|----------------|
| 1 | Ticker     | Value ($)   | Allocation % | Annual Return  |
| 2 | AAPL       | 15000       | 30%          | 12.5%          |
| 3 | GOOGL      | 10000       | 20%          | 8.2%           |
| 4 | MSFT       | 12500       | 25%          | 15.3%          |
| 5 | VTI        | 7500        | 15%          | 10.1%          |
| 6 | BND        | 5000        | 10%          | 3.2%           |
| 7 |            |             |              |                |
| 8 | Totals     | =SUM(B2:B6) | =SUM(C2:C6)  |                |
| 9 |            |             |              |                |
| 10| Portfolio Return (by value):                               |
| 11| Portfolio Return (by allocation):                          |

Calculate weighted return using dollar values as weights (cell B10):

=AVERAGE.WEIGHTED(D2:D6, B2:B6)

Or using allocation percentages (cell B11):

=AVERAGE.WEIGHTED(D2:D6, C2:C6)

Both should return approximately 10.67% if allocations match values.

Add validation to ensure allocations sum to 100%:

=IF(ABS(SUM(C2:C6)-1)>0.001, "Allocations must sum to 100%", AVERAGE.WEIGHTED(D2:D6, C2:C6))

Format the result as a percentage, and you have a functional portfolio tracker.

Summary & Quick Reference

When to use each:

• SUMPRODUCT/SUM: Cross-platform spreadsheets, complex conditional weighting, or when you need to modify the calculation logic.
• AVERAGE.WEIGHTED: Google Sheets-only projects where readability matters and you want the simplest solution.
• Manual calculation: Teaching scenarios or when you need to audit each step of the calculation.

Start with AVERAGE.WEIGHTED for simplicity. Fall back to SUMPRODUCT/SUM when you need portability or advanced filtering. Always wrap production formulas in error handling, and validate your weight column to prevent garbage-in-garbage-out scenarios.