How do I compute a weighted average for the following?

[LanceCasteau]

[TABLE="width: 480"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Store[/TD]
[TD]Average Daily Sales[/TD]
[TD]Inventory[/TD]
[TD]Days On Hand (Inv/Sales)[/TD]
[/TR]
[TR]
[TD]New York, NY[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Seattle, WA[/TD]
[TD]25[/TD]
[TD]200[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Detroit, MI[/TD]
[TD]75[/TD]
[TD]225[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Dallas, TX[/TD]
[TD]5[/TD]
[TD]50[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]125[/TD]
[TD]515[/TD]
[TD]4.12[/TD]
[/TR]
</tbody>[/TABLE]


How to calculate what each stores weight is in the aggregate Days On Hand (4.12)?

 

[Kenneth Hobson]

Welcome to the forum!

=SUMPRODUCT(B2:B5,D2:D5)/SUM(B2:B5)

 

[LanceCasteau]

Ken,

Thank you, that returns the weighted average across all stores, 4.12, which is obviosuly correct and matches my total line. What Im looking for, is the individual stores portion of that 4.12, ie Dallas has 10 days on hand, but they probably account for less than 1 day on hand for the total weighted average. Hopefully my follow up makes sense and hope you can help!

Thanks again

 

[sandy666]

maybe use PivotTable with CalculatedField (Inventory/Sales)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Store[/td][td=bgcolor:#DDEBF7]Sum of Average Daily Sales[/td][td=bgcolor:#DDEBF7]Sum of Inventory[/td][td=bgcolor:#DDEBF7]Sum of WA[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dallas, TX[/td][td]

5​

[/td][td]

50​

[/td][td]

10​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Detroit, MI[/td][td]

75​

[/td][td]

225​

[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]New York, NY[/td][td]

20​

[/td][td]

40​

[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Seattle, WA[/td][td]

25​

[/td][td]

200​

[/td][td]

8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

125​

[/td][td=bgcolor:#DDEBF7]

515​

[/td][td=bgcolor:#DDEBF7]

4.12​

[/td][/tr]
[/table]