Solver

[mrbradley1]

Guys,

Anyone know how to put this into excel 2013 and use solver to determine the answer? This has been done on matched but I think the same can be done with excel

Thanks

[MrShorty]

Is there a specific part of this that you need help with? I see a few steps to divide an conquer:

1) Set up cells to compute the different quantities, such as lambda11 and lambda22 and lambda1
2) I usually like to set it up as a "root finding" problem, so I will have a cell that computes lambda1-0.6.
3) Call Solver and tell it to:
a) set target cell -- cell with your desired objective function. in this case lambda1-0.6
b) to a value of 0
c) by changing -- whatever cell you have put alpha in.

[shg]

That's not the solution I get, but I may have ham-handed the formula:

Row\Col	A	B	C
1	alpha	4.8473328	B1: Solver
2	lambda1	0.6	B2: Input
3	lambda2	0.2	B3: Input
4	lambda11	0.5959951	B4: =1.25/(alpha - 2.75)
5	lambda22	1.4444932	B5: =alpha*lambda11/2
6	Function	-5.76E-09	B6: =lambda11 + (1 - lambda11) * ((lambda22 - lambda2)/lambda22) ^ (1.85 * alpha ^ 1.785) - lambda1

Make B6=0 by changing B1

[mrbradley1]

Bare with me on this as I'm not great on excel

[shg]

Bare with me on this

I'm very perticaler about who I get nekked with ...

[mrbradley1]

Right:-

1) I have cells which have defined lambda1, lambda11, lambda 22 etc.
2) I have a cell which defines the formula
3) I tell solver to:- set objective cell lambda1 to = 0
4) by changing alpha cell

where do I use the cell for the formula then?

[mrbradley1]

Thats it guys cracked it....Nice one

[shg]

Good job .

[shg]

And indeed I did get the formula wrong:

Row\Col	A	B	C
1	alpha	6.2666171	B1: Solver
2	lambda1	0.6	B2: Input
3	lambda2	0.2	B3: Input
4	lambda11	0.3554552	B4: =1.25/(alpha - 2.75)
5	lambda22	1.113751	B5: =alpha*lambda11/2
6	Function	-4.79E-08	B6: =lambda11 + (1 - lambda11) * (1 - lambda2/lambda22) ^ (0.185 * alpha ^ 1.785) - lambda1

[mrbradley1]

Using the same principle, is there a way to determine lambda11 by changing alpha.

Reason for the questions alpha has a maximum figure based on lambda11.

[shg]

lambda1 is a constant. If it were a variable, you would have one equation and two unknowns -- so no.

[MrShorty]

Is this a constraint to be added to the same Solver model, or is this a new kind of problem?

If this is a constraint to be added to the same Solver model, then, yes I'm sure there is a way. The details depend on exactly how the constraint should be defined, then determine how to best incorporate the constraint into the Solver model.

If this is a new problem, then it should be as easy as: a) Set target cell -> lambda11
b) to a value of -- desire value, or have a cell lambda11-value then set this to a value of 0
c) by changing -- alpha.

I would also note that lambda11 is a relatively simple algebraic function of alpha. If this latter is the new problem, then it should be easy to solve for alpha at a given lambda11 algebraically and enter that formula into Excel.