Excel "Solver" - Functionality - questions from my worksheet

[ateestructural]

Hi,

Please see attached worksheet

1) Now, look at the sheet titled: "IncrementalFit"
2) Go to Data->Solver
3) As you can see there are some consttraints
4) In the constraints L3<=1 and L3>0 i.e. L3 lies between 0 and 1
5) Similarly with L4
6) L3 and L4 are cells (hope I'm right)
7) I want make the value of L3 as 0.3 and L4 as 0.5 and then do the optimization of other parameters: R5,U3,U4
8) Can you tell me how to carry out step 7 above?

Thanks
Jo

[Hydraulics]

I want make the value of L3 as 0.3 and L4 as 0.5

This is easy: set them to the values you wish overwriting the cells, and then remove L3 and L4 from the list of variables in the Solver dialog (optional: remove the constraints, even though they are satisfied with those values).

and then do the optimization of other parameters: R5,U3,U4

This looks quite hard. There are too many discontinuous functions in your worksheet (IF, MAX...), and after some testing, not even the evolutionary engine can reliably find a solution.

HTH,

Francesco

[ateestructural]

In the consstraints dialog I set the values of L3 and L4 equal to 0.3 and 0.5 and I did get a reasonably fit. Why did you say it hard?

[Hydraulics]

Deleted comments since they are not meaningful.

Yes, my bad ,the model seems to point to a max value, even if with slightly different choices for U3, U4 and S5.

HTH,

Francesco

[ateestructural]

Thanks. I myself am getting familiarized with the sheet. Did you mean that retaining the original constraints you get th right values of AB4?

Also getting different values of AB4, did you mean the solution is returning a local minima?

[Hydraulics]

Sorry, I was editing my post.

I have played some more with the parameters of the evolutionary engine, increasing both mutation rate and population size, and the max of AB4 is always within a very narrow range around 2.6E-10.
The problem is that you can never know if the value found is really the best one.
However, if results make sense, I wouldn't worry too much.

HTH,

Francesco