Forum Discussion
Calculate performance based on proportional holdings
Thanks. In B2:
=SUMPRODUCT(INDEX('Company Performance'!$B$2:$Z$100,0,MATCH(B$1,'Company Performance'!$B$1:$Z$1,0)),INDEX('Client Holding Size'!$B$2:$Y$100,0,MATCH($A2,'Client Holding Size'!$B$1:$Y$1,0)))
You can modify the ranges, but the last row (100 in the above formula) should be the same in 'Company Performance'!$B$2:$Z$100 and 'Client Holding Size'!$B$2:$Y$100.
Fill down.
Hi Hans,
https://1drv.ms/x/s!AtMRQbHIJJWxuB6bSmUcK0Hc5G9d
Can you try that and see if it works? I've left in the long-winded formulae on the first tab, but I'm sure there's a better way of doing it.
Also there are likely to be hundreds more clients and many more companies than just the 3 I have used in the sample spreadsheet. I need to it be able to be added to and grown without making it more difficult to manage.
Thanks,
Thanks. I have downloaded the workbook, but I don't have the slightest idea what it has to do with your question. The worksheet doesn't contain any formulas.
- That's because I'm an idiot and uploaded the wrong file. Can I downvote my own reply? Sorry!
https://1drv.ms/x/s!AtMRQbHIJJWxhaR84kaXA_W7kkN05AThanks. In B2:
=SUMPRODUCT(INDEX('Company Performance'!$B$2:$Z$100,0,MATCH(B$1,'Company Performance'!$B$1:$Z$1,0)),INDEX('Client Holding Size'!$B$2:$Y$100,0,MATCH($A2,'Client Holding Size'!$B$1:$Y$1,0)))
You can modify the ranges, but the last row (100 in the above formula) should be the same in 'Company Performance'!$B$2:$Z$100 and 'Client Holding Size'!$B$2:$Y$100.
Fill down.
- Amazing, thanks so much Hans. I'm glad I was at least on the right track with the functions required, thanks for fleshing out the exact details it's really appreciated.