I want to get an Excel weight average across two ranges of cells that are broken apart by some numbers I do not want included and tried the following:
=SUMPRODUCT(AQ6:AQ50,AQ75:AQ256,AT6:AT50,AT75:AT256)/SUM(AT6:AT50,AT75:AT256)But in Excel for Mac v16.12 that gives me a #VALUE!, even though either range by itself works:
=SUMPRODUCT(AQ6:AQ50,AT6:AT50)/SUM(AT6:AT50)
=SUMPRODUCT(AQ75:AQ256,AT75:AT256)/SUM(AT75:AT256)How can I create the weight average across both ranges of cells in a single formula?
12 Answers
The ranges inside the SUMPRODUCT need to be the same size, do two:
=(SUMPRODUCT(AQ6:AQ50,AT6:AT50)+SUMPRODUCT(AQ75:AQ256,AT75:AT256))/SUM(AT6:AT50,AT75:AT256)Edit: In OP's formula, SUMPRODUCT() will try to multiply all four arrays together, then sum the elements of the resulting array. That's not what needs to be done, and besides, it throws an error for the reason @ScottCraner mentions.
In plain English, a weighted average is calculated as the sum of the (numbers times their weights), divided by the sum of the weights. SUMPRODUCT() can be used to do that with two arrays of numbers, but it's not as easy to understand what the formula is actually doing.
This array formula gives the correct result:
=SUM(AQ6:AQ50*AT6:AT50,AQ75:AQ256*AT75:AT256)/SUM(AT6:AT50,AT75:AT256)
As an array formula, it must be entered with CTRLShiftEnter, rather than just Enter.
5
