Goal Seek with Multiple Variables - Excel 2013

[davidtaylor598]

Hi,

I have recently been upgraded to Excel 2013 and have not used Goal Seek in about ten years. After a few clicks I can get it working fine but am unsure of how to tackle a problem when there are multiple variables.

I have 4 benchmarks (columns B:E) broken down into 6 different figures, so a table 5 by 7 (A1:E8).

eg. the Balanced Index (row E) has 17.5 in Bonds (E2), 37.5 in UK Equities (E3), 30 in Overseas Equities (E4) ,... and so on, totalling 100.

Below this I have the four benchmarks with a weighting beside each one, which will determine the output above. So as an example, beside Bonds I have the formula: "=(B$19*B2+B$20*C2+B$21*D2+B$22*E2)/100".

Underneath this table I have my desired result for each figure, and beside it I have the output.

How would I go about solving the best possible combination of the benchmarks to come to a result closest to the desired result?

Sorry if I haven't described this well, it is hard without attaching the workbook and I am not sure how to do this as I cannot install the add-in MrExcel suggests.

Thanks,

David

 

[DRSteele]

Try using Solver (the Solver Add-in in Excel which you may need to activate). Why can't you install the HTML Add-in? It's straightforward.