Assuming I have 10,000 rows with different the unique identifiers such as chairs, tables, sofa etc that are repeated with different combinations of price and quantity,How can I find the weighted average price for the chairs as well as other unique identifiers without manually dividing the sum of prices by the sum of the quantity for chairs? Example, if the data has chairs where $12,$14,$22 are the prices and 28,13,5 are the quantity of chairs available respectively to the price. Thus, there are 28 chairs available for $12 and so on. Thanks.
0
votes
1 Answers
0
votes
It would be better if we had access to your data, but it seems that using a conditional sumproduct over a conditional sum solves your issue.
Here follows an example:
Assuming:
A = object's name
B = object's quantity
C = object's price
=SUMPRODUCT(--(A1:A10000="Chair"),B1:B10000,C1:C10000)/SUMIF(A1:A10000;"Chair";B1:B10000)
It's not very common to use a "conditional SUMPRODUCT" so I recommend that you read more about it here
