Solver with intermediate formula steps not working

Your intermediate functions (and, as a result, your final objective function) are "step" functions -- they stay constant at 0 or 1 until the value in D4 crosses over the value in column B. As noted in the solving method description, GRG non-linear really does not like step functions or other non-smooth functions.

You could try an evolutionary engine and see if that helps. Reading between the lines, the sample you provide seems to be a greatly simplified representation of your real problem. Assuming your real problem is also a step function, an evolutionary engine may be able to find a solution.

You also might consider how you can change the calculation procedure to see if you can make a smooth objective function for solver to work with.

tl:dr -- At this point, I would say that, if simply switching to an evolutionary engine is enough to get Solver to reliably find a solution for you, then leave what isn't broken alone. Assuming Solver is reliably finding a solution, it may be easy enough to round Solver's solution to the nearest 0.01 after Solver finds its solution.

continue reading for further thoughts on the problem:

Without knowing more about your actual computation scheme and how the simplified (oversimplified?) example represents the computation scheme, it is difficult to talk about better ways to formulate the problem.

Looking at the problem in the sample sheet, it reduces down to "find a number that is both greater than 3 and greater than 1." Obviously, the trivially easy answer without using Solver is, "any number greater than 3." I could easily put =MAX(B9:B10)+0.01 into E4 and make a comment that anything larger than E4 is a solution. If I wanted to get Solver to solve this problem, I would probably set Solver up to Set D4 to a minimum by changing D4 subject to the constraints that D4>=3 and D4>=1. After Solver runs and returns 3 as the result, I add a small amount to D4 to get my final answer, since I don't actually want a result exactly equal to 3 (Maybe in E4, enter =D4+0.01). But, obviously, I don't know how either of those strategies would translate into your actual calculation scheme.

As for the "limit solutions to 2 decimal places" constraint. I usually prefer not to impose that kind of constraint on Solver if at all possible. I feel like it is usually easier to let Solver find a solution without that kind of constraint, then round Solver's result after Solver has finished running. Something like =ROUND(D4,0.01) or =ROUNDUP(D4,0.01) in E4. Solver finds the unconstrained optimum value in D4, then I can easily round that to the desired precision after Solver runs. But that kind of strategy doesn't always work for some problems, so you have to know enough about your actual problem to decide whether that kind of strategy works or not. When it doesn't work, the usual strategy is to move the by changing cell to C4, constrain C4 to be an integer, then have D4 be =C4/100. This approach adds more discontinuities to the function, which often makes it harder for Solver to find a solution, so I don't like to do it this way unless it is necessary.

Originally Posted by richybee

MrShorty - you're correct, I have a more complex actual dataset.

Can you share more details about both the dataset and what precisely you are trying to achieve? (context and objective)

We don't need personal informations, just a small, representative sample. This seems to be an example of an XY problem.

With the setup in your file, the problem is trivial: since the intermediate formula step in column D is an IF condition that returns either 1 or 0, and you are maximizing their sum in F5, we can just look for the smallest number x in column B and choose x-eps, for instance

=MIN(B9:B10)-0,01

and @Alf has already offered a VBA solution to check it. (Hi Alf!)

On the step functions, I don't know of a way I could redesign them to be objective functions - not overly sure what these are but I will certainly need if statements and not sure if there are workarounds for these.

There exist workarounds, we need a deeper understanding of your problem to help you, assuming that Solver is really required.

HTH,

Francesco