How do I compute a weighted average for the following?

[LanceCasteau]

Store	Average Daily Sales	Inventory	Days On Hand (Inv/Sales)
New York, NY	20	40	2
Seattle, WA	25	200	8
Detroit, MI	75	225	3
Dallas, TX	5	50	10
Total	125	515	4.12

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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)

Store	Sum of Average Daily Sales	Sum of Inventory	Sum of WA
Dallas, TX	5​	50​	10​
Detroit, MI	75​	225​	3​
New York, NY	20​	40​	2​
Seattle, WA	25​	200​	8​
Grand Total	125​	515​	4.12​