CODEWITHSUNDEEP
excel
user3228901
user3228901

Reputation: 29

Find the weighted average of two rows based on another containing dates

I have a table in which one row contains dates, another row contains AHT (Avg Handle Time) and the third row contains no of calls handled.

I have situation where I need to find the Weighted average for each week in another table. I am able to find simple average for each week. However not getting this weighted average for each week.

enter image description here

Thanks

Upvotes: 2

Views: 158

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34220

This should work in most versions of Excel

=SUMPRODUCT(INDEX($3:$3,MATCH("Week "&A9,$1:$1,0)):INDEX($3:$3,MATCH("Week "&A9,$1:$1,0)+6),
INDEX($5:$5,MATCH("Week "&A9,$1:$1,0)):INDEX($5:$5,MATCH("Week "&A9,$1:$1,0)+6))
/SUM(INDEX($3:$3,MATCH("Week "&A9,$1:$1,0)):INDEX($3:$3,MATCH("Week "&A9,$1:$1,0)+6))

enter image description here

May need to be array-entered pre Excel 365.

Notes

The weighted mean formula is

Weighted mean = Σwx/Σw

where are the weights and x are the values. So in this case, from OP's comment, the third row is the weights (number of calls) and the last row is the values (AHT).

The easiest way to get Σwx is to use Sumproduct, and to get Σw is just to use Sum. So the basic formula for (say) week 40 would be simply

=SUMPRODUCT(A3:G3,A5:G5)/SUM(A3:G3)

However, I reasoned that it would be inconvenient to re-write this formula for each different week, so I have used Match to find the starting column of each week from row 1 , then index to find the corresponding position in either row 3 or 5 (let's call it startpos), then index again to find the position six places to the right of startpos (let's call it endpos). The required range to be placed in each part of the short formula above is therefore startpos:endpos (I can use this notation because startpos and endpos, the values returned from the Index function, are both references).

If Excel 365 is available, this can all be expressed much more succinctly and clearly using Let to assign variables names to each part of the formula.

=LET(startCol,MATCH("Week "&A9,$1:$1,0),
startWeight,INDEX($3:$3,startCol),
endWeight,INDEX($3:$3,startCol+6),
startValue,INDEX($5:$5,startCol),
endValue,INDEX($5:$5,startCol+6),
weightRange,startWeight:endWeight,
valueRange,startValue:endValue,
SUMPRODUCT(weightRange,valueRange)/SUM(weightRange))

Upvotes: 3

Related Questions