Forum Discussion

Deleted's avatar
Deleted
Sep 06, 2018
Solved

Weighted Average with multiple criteria and looking for specific name

Hi, I'm trying to figure out how to get the weighted average price of multiple products within the same class.  For example see the sheet below.  I want to find the weighted average price of all coke...
excel
Excel Help
  • Haytham Amairah's avatar
    Haytham Amairah
    Sep 07, 2018

    Hi Blake,

     

    Please try this formula:

    =AVERAGE(AVERAGEIFS(D2:D8, A2:A8,{"Coke","VanillaCoke","CherryCoke"}))

    With this formula, you don't have to manually calculate the Qty.

     

    But the best way is to create another column in the table to hold the class for each product.

    This will make the calculation easier so that you don't have to hard-code each product in the formula.

     

    This can be done as follows:

     

     

    Please find the attached workbook

    Hope that helps

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Sep 07, 2018

Hi Blake,

 

Please try this formula:

=AVERAGE(AVERAGEIFS(D2:D8, A2:A8,{"Coke","VanillaCoke","CherryCoke"}))

With this formula, you don't have to manually calculate the Qty.

 

But the best way is to create another column in the table to hold the class for each product.

This will make the calculation easier so that you don't have to hard-code each product in the formula.

 

This can be done as follows:

 

 

Please find the attached workbook

Hope that helps

nuecesryan's avatar
nuecesryan
Copper Contributor
Jan 27, 2021

Haytham Amairah 

 

This example/solution is just the simple average and not the weighted average.  The calc for weighted average is as attached and would need a column with total cost per item calculated (cost per unit X units).  This column can then be hidden for presentation purposes if desired.

Resources