带权重实时更新加权平均公式求解：含0值、忽略空白单元格

Hey there, let’s fix this weighted average problem once and for all! The core issue with your existing formulas is either ignoring weights, counting blank cell weights, or excluding valid 0 values. Here’s the single formula you need that checks all your boxes:

=SUMPRODUCT(C3:C5, A3:A5*(C3:C5<>"")) / SUM(A3:A5*(C3:C5<>""))

How this formula works: #

• The (C3:C5<>"") part generates an array of TRUE/FALSE flags: TRUE if the value cell isn’t blank, FALSE if it is. When multiplied by the weights in column A, blank value rows turn to 0 (since FALSE equals 0 in Excel), so they don’t affect either the numerator or denominator.
• Numerator: SUMPRODUCT(C3:C5, A3:A5*(C3:C5<>"")) calculates the sum of (value × weight) only for non-blank value cells. This includes 0 values because 0<>"" evaluates to TRUE, so their corresponding weights are retained.
• Denominator: SUM(A3:A5*(C3:C5<>"")) sums only the weights linked to non-blank value cells, excluding weights for blank rows entirely.

Let’s verify with your examples: #

1. When Time C’s cell is blank:
   • Numerator: 9*2.5 + 3*3.5 = 22.5 + 10.5 = 33
   • Denominator: 2.5 + 3.5 = 6
   • Result: 33/6 = 5.5 (correct, as blank rows are ignored)
2. When Time C’s cell is 0:
   • Numerator: 9*2.5 + 3*3.5 + 0*3 = 33 + 0 = 33
   • Denominator: 2.5 + 3.5 + 3 = 9
   • Result: 33/9 ≈ 3.67 (correct, as 0 and its weight are included)

Alternative for Excel 365/2021 users: #

If you’re on a newer Excel version, the FILTER function makes the formula more readable while doing the same job:

=SUMPRODUCT(FILTER(C3:C5,C3:C5<>""), FILTER(A3:A5,C3:C5<>"")) / SUM(FILTER(A3:A5,C3:C5<>""))

This filters out blank value rows first, then computes the weighted average on the remaining valid data.

Both formulas will update automatically in real-time as you enter new data—no manual recalculation required!

内容的提问来源于stack exchange，提问作者Prezza