VLOOKUP from Dependant Dropdown with Weighted Averages

[Phrick]

Hi guys,

Hopefully a relatively quick one tonight but we'll see!

I'm hoping to calculate the weighted average of three separate results produced from a VLOOKUP (searching from a different sheet), from a dependant drop down which are multiplied by percentages. Even writing it it sounds confusing but I'm sure the answer will be straight forward...at least I hope!!

I tried:
=SUMPRODUCT((VLOOKUP($B$3:$B$5,PerformanceFigures!$B$2:$G$8,2,0)))/SUM(C3:C5)

Which I know is wrong but can't figure out what I'm missing.

I've attached an example workbook with a simplied version of what I'm trying to do; it is cells E4:I4 that I'm hoping will include the calculation.

Testworkbook2.xlsx

As always, I'd be stuck without you guys and appreciate any help you are willing to give.

Kind Regards

[etaf]

=VLOOKUP($B22,PerformanceFigures!$B$2:$G$8,2,FALSE)*$C22

thats for the 1st column and then the ,2, needs to change to a ,3, as it moves across

see attached

[Phrick]

Hi etaf, thanks very much for your reply!

The formula you gave provides the percentages based on the figures but I'd ideally like the formula to also total those figures and provide a weighted average based on them.

Do you know if this is possible?

If not, I'll create a hidden table with your formula but was hoping for a way of grouping all the calcs together, something like:

=SUMPRODUCT((VLOOKUP($B$3:$B$5,PerformanceFigures!$B$2:$G$8,2,0)),C3:C5)/SUM(C3:C5)

But that just returns #VALUE

Thanks again for your time,

Rick

[etaf]

The formula you gave provides the percentages based on the figures but I'd ideally like the formula to also total those figures and provide a weighted average based on them.

can you give an example of what you mean
not as a formula perhaps showing the calculation for example

aberdeen 20%

0-12 = 8.39

so =8.39*20%

so what would the calculation look like in your example

[Phrick]

Sorry I'm probably not explaining myself clearly, long day! Really appreciate your help.

I think I found a solution:
=SUM(VLOOKUP($B3,PerformanceFigures!$B$3:$G$11,2,0)*$C3)+(VLOOKUP($B4,PerformanceFigures!$B$3:$G$11,2,FALSE)*$C4)+(VLOOKUP($B5,PerformanceFigures!$B$3:$G$11,2,FALSE)*$C5)

which gives the weighted average I need.

This is basically just adding all of your formulas together to give the desired result.

Funny how a solution just creates more problems though right? Now I've got to figure out how to cope when there isn't a fund name selected (i.e. blank cell) and a way that more rows can be added which then changes the formula to include them (think thats macro territory though!).

However, perhaps something to attempt when I've had more sleep!

Thanks again for your help,

Rick