Weighted Average (Dynamic Range)

[garypea123]

Good Afternoon,

I do not have XLSBB available on computer so I cannot upload this.

I have simplified by raw data below but please see below request.

Calulation of RSP = F divided by E

My issue is that sometimes in a promo the RSP will be lower and so I need to create a weighted average when this occurs.

The weighted average should be based on the SKU and Week.

In the below example the calculation will weight average SKU 1 with cust202219 (SUM of F divided by SUM of E) and then weight average SKU 1 with cust202220 (SUM of F divided by SUM of E).

I can do this manual by doing it for each individual week, but there must be a smarter way of doing this.

Kind Regards

 

[Marcelo Branco]

Try

I2 copied down
=SUMIFS($F$3:$F$16,$B$3:$B$16,B3,C$3:C$16,C3)/SUMIFS($E$3:$E$16,$B$3:$B$16,B3,C$3:C$16,C3)

M.

 

[garypea123]

Perfect - I understand that fully.

Mine was a similar, but was using a SUMIF. The SUMIFS I need to keep remembering

Thank you very much

 

[Marcelo Branco]

You're welcome. Glad to help

M.