lordlofgren
New Member
- Joined
- Aug 9, 2022
- Messages
- 1
- Office Version
- 2016
- Platform
- Windows
Hey everyone,
I'm trying to calculate the weighted average of several index baskets for construction projects. My problem is that the sheet containing index data isn't matching the rows of the index baskets. I've been trying to use a PRODUCTSUM without success. The PRODUCTSUM formula probably needs to be combined with kind of INDEX/MATCH and/or SUMIF formula, but not sure how to do it.
I simplified my data with examples in the attached picture. As you can see, each index basket contains many different indexseries, but no basket contains alla series. Data for the indexseries are downloaded monthly into a common sheet containing extra rows for titles.
As an example, the weighted average of the STREET basket for jan-22 is now calculated manually as: 10%*117+5%*135+50%*105+35%*109 = 109,1
Anyone know how I can create a single formula to be used on for the table "Weighted average index per basket" instead of doing all caluculations manually?
Thx in advance for any advice!
/ Martin
I'm trying to calculate the weighted average of several index baskets for construction projects. My problem is that the sheet containing index data isn't matching the rows of the index baskets. I've been trying to use a PRODUCTSUM without success. The PRODUCTSUM formula probably needs to be combined with kind of INDEX/MATCH and/or SUMIF formula, but not sure how to do it.
I simplified my data with examples in the attached picture. As you can see, each index basket contains many different indexseries, but no basket contains alla series. Data for the indexseries are downloaded monthly into a common sheet containing extra rows for titles.
As an example, the weighted average of the STREET basket for jan-22 is now calculated manually as: 10%*117+5%*135+50%*105+35%*109 = 109,1
Anyone know how I can create a single formula to be used on for the table "Weighted average index per basket" instead of doing all caluculations manually?
Thx in advance for any advice!
/ Martin
