Hello,
I have a list of materials purchased in various quantities from various vendors and a separate list of monthly costs for each particular material purchased.
My goal is to determine the weighted average cost per unit of purchases from a particular vendor during each month. To do so, I believe the following steps should be performed.
The main issue is that I would like to make this calculation in a single cell based on Vendor and MonthYear if possible and I would like to avoid using VB. I have included an example for illustration purposes.
Thank you in advance for all your help and support in this matter.
I have a list of materials purchased in various quantities from various vendors and a separate list of monthly costs for each particular material purchased.
My goal is to determine the weighted average cost per unit of purchases from a particular vendor during each month. To do so, I believe the following steps should be performed.
 Calculate the total purchases made from the vendor of interest for the desired month
 Calculate the weight of each component/material purchased from the total purchases calculated in Step 1
 Multiply the weight of each component/material calculated in Step 2 by the corresponding cost included in the list of costs
 Sum the results obtained in Step 3 to arrive to the weighted average cost for all the purchases made from the vendor of interest
The main issue is that I would like to make this calculation in a single cell based on Vendor and MonthYear if possible and I would like to avoid using VB. I have included an example for illustration purposes.
Thank you in advance for all your help and support in this matter.
Attachments

16.4 KB Views: 13
Last edited: