• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Monthly Weighted Average Cost for Multiple Materials by Vendor

Mix1325

New Member
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.
  1. Calculate the total purchases made from the vendor of interest for the desired month
  2. Calculate the weight of each component/material purchased from the total purchases calculated in Step 1
  3. Multiply the weight of each component/material calculated in Step 2 by the corresponding cost included in the list of costs
  4. Sum the results obtained in Step 3 to arrive to the weighted average cost for all the purchases made from the vendor of interest
Please note that the cost for each material/vendor is constant during a month. It only changes on a monthly basis.
The main issue is that I would like to make this calculation in a single cell based on Vendor and Month-Year 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

Last edited:
  • Like
Reactions: Hui

Hui

Excel Ninja
Staff member
Mix

This is a very good question.

It is rare that we actually get new and challenging questions, so I appreciate it.

I hope some of the other Ninja's and others in general will have a go as I am sure there are easier ways to solve this than the way I have

But solve it I have:

The Total Cost is:
=SUMPRODUCT(($D$3:$D$9)*($A$3:$A$9=$M$3)*($C$3:$C$9=$N$3)*TRANSPOSE(($H$3:$H$8=TRANSPOSE($B$3:$B$9))*($G$3:$G$8=TRANSPOSE($A$3:$A$9))*($J$3:$J$8=TRANSPOSE($C$3:$C$9))*($I$3:$I$8))) Ctrl+Alt+Enter

The Weighted Average is

=SUMPRODUCT(($D$3:$D$9)*($A$3:$A$9=$M$3)*($C$3:$C$9=$N$3)*TRANSPOSE(($H$3:$H$8=TRANSPOSE($B$3:$B$9))*($G$3:$G$8=TRANSPOSE($A$3:$A$9))*($J$3:$J$8=TRANSPOSE($C$3:$C$9))*($I$3:$I$8)))/SUMIFS($D$3:$D$9,$A$3:$A$9,$M$3,$C$3:$C$9,$N$3) Ctrl+Alt+Enter
 
Last edited:

Mix1325

New Member
Thank you for your kind words and the solution provided, Hui. I believe your formulas work but, in the meantime, I believe I found a simpler solution by using the so called conditional sumproduct - formula included below. It took some further research on the internet, but I believe it provides the correct result.
=SUMPRODUCT(--(C3:C9="ZZ")*(A3:A9="Aug-19"),D3:D9,F3:F9)/SUMIFS(D3:D9, C3:C9,C4,A3:A9,A4)
 

Attachments

Last edited:

Hui

Excel Ninja
Staff member
Mix1325

Yes, Adding the helper column simplifies the solution substantially

I had assumed that that wasn't an option?
 
Top