Finding Max value?

[SRO]

Does anyone know how to find the value in one cell that will result in an equation in another cell producing its max value?

SIMPLIFIED EXAMPLE:

A B
1 ? -(X-3)^2
2

A B
1 3 0.0
2

Again this is a simplified example. My actual spreadsheet is pretty complex so dragging A1, & B1 down as A1 increases really isn't an option. And I've had enough of the trial & error technique.

 

[GregLocock]

This is not possible in general. The non existent function FINDROOTSOFANDEVALUATE() would have to be able to perform differentiation and then decide which of the stationary points is the maximum. Also, how would it report if there were two equal maxima?

Solver may have a decent stab at it, depending on the complexity of your response space. In this case your target would be to maximise B1 by varying A1.

Otherwise, read many of the threads below on optimisation.





Cheers

Greg Locock

 

[melone]

Use the solver function..... Tools->Solver...

 

[SRO]

Solver is God's gift to Excel!!!! Thank's for showing me the most useful tool in the history of anything.