Monte Carlo in Excel where inputs must equal 1

[Tinkerz]

I have a scenario to run in which there are 5 inputs that will result in a output from a formula.

The 5 inputs must equal 1,ie they are pecentages.

How can I get then get output from the resulting formula to be better the lower it is?

scenario and goal seek don't seem to work.

Is there a monte carlo method that has restraints?

Can the output be on a data table?

Thanks

Tinkz

 

[Tinkerz]

any ideas

 

[ChrisOswald]

Have you taken a look at using the solver addin?

assume your inputs are in a1 - e1, and the function you're minimizing is in f1.

in e1 put in the formula =1-sum(a1:d1). This will make the sum of the 5 inputs equal to 1. Using the solver, add the constrants a1>=0, a1<=1,...,e1>=0,e1<=0.

set your target cell to be f1, and select the minimize option.

Hopefully this'll give you a start on the solution to your problem.