Mega Formula to calculate results based on several conditions

Hi,

I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4).

I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking.

For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05)

The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%

Can someone please help?

Thanks!!!

based on your earlier narrative perhaps something like:

Please Login or Register  to view this content.

It is the double unary operator and is used to coerce Boolean output (True/False) to Integer equivalent (1/0 respectively in Native XL)

For more info. on Sumproduct & Coercion see the link in my sig. to Bob Phillips' white paper.