'excel weighted average volume
I was wondering how to pull a weighted average of Score1 and Score2 based on Score1 Volume and Score2 Volume. The combined score should be closer to "50" than "100" since the Score1 Volume (50) is greater than Score2 Volume (25). What would be a good weighted formula that would help derive this result. The answer should be around 60 (at least closer to 50). answer in Excel would be appreciated. Thanks!!
Solution 1:[1]
You can use a helper column where you multiply your weight to your values or use the sumproduct formula
This YouTube video I made shows a quick example
Solution 2:[2]
Assuming 100 is in D2:
=(C2*F2+D2*G2)/(F2+G2)
It may be easier to comprehend with a more tangible example. Say Score is the number of eggs in a box and ScoreVolume is the number of boxes. To calculate the average number of eggs per box you need the total number of 'eggs': (C2*F2+D2*G2) divided by the total number of boxes: (F2+G2).
Solution 3:[3]
Here is another option using SUMPRODUCT(). This works well with large data sets.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | cigien |
| Solution 2 | pnuts |
| Solution 3 | z33Will |