Forum Discussion

Copper Contributor

Feb 10, 2022

Solved

Calculate performance based on proportional holdings

Hi, I am trying to calculate monthly performance for a group of clients where each client holds a different proportion of a selection of companies. I have the following datasets: Client %...

excel

Formulas and Functions

HansVogelaar
Feb 11, 2022
Phil_Kirwan

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.

Phil_Kirwan

Copper Contributor

Feb 11, 2022

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_W7kkN05A

HansVogelaar

MVP

Feb 11, 2022

Phil_Kirwan

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.

Phil_Kirwan
Copper Contributor
Feb 11, 2022
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.