Using Solver to extrapolate a value

[kd5649]

Hello,

I've been working through various examples of solver to attain the following worksheet (attached). I've now become a bit stuck in trying to solve the equation for a score of 80. What would be the logistic function that I enter into solver's target cell? And then would I need to change anything further in solver except for setting the value of 80?
Forum Example.xlsx
Any comments about the graph/fit itself would also be hugely appreciated as I'm not convinced it's right!

Any help is really much appreciated!

Thanks.

[MrShorty]

I'm not entirely certain what your question is, unless it is just general uncertainties about solving the problem.

Often when I do something like this, I simply make a copy of one of the rows into a row above or below the raw data. For example, maybe copy one of the existing rows down to row 30. Then, I will use this row for Solver. Target cell would be D30, to a value of 80, by changing B30. The function you have has a very narrow window where the result actually changes, you may need to take some care in selecting your "initial guess" before calling Solver. Solver may have trouble finding a solution if the initial guess is too large or too small.

While it is not directly related to Excel, I would also suggest you familiarize yourself with the Newton Raphson algorithm (Wikipedia has a decent article, though it might be more than you need to know). I find it very helpful, when using Solver, to have at least a basic understanding of the algorithm being used so I will know how the program is working, and be able to understand what kind of situations will cause the algorithm to have trouble or even fail.

[kd5649]

Ok thanks! Would you have any tips when selecting your "initial guesses"? Do they relate to the minimum. max, inflection and slope of the curve? I've read this previously but this doesn't seem to ring true for this example, hence my confusion!

To clarify my original question. If I wanted to extrapolate, visually from a score of 80, I would see from that graph that this would give me a speed of around 115. Would solver be able to get an exact value for me?

[MrShorty]

Would you have any tips when selecting your "initial guesses"?

The main tip I can think of is this (taken from http://www.cs.utah.edu/~zachary/isp/...ot/Newton.html)

Newton's method relies on the observation that this tangent line will often cross the x-axis at a point closer to a root than is the guess.

Because the important step in using the NR algorithm is computing the slope of the curve at x, using the graph to eyeball where the slope of the curve is most likely to lead closer to the solution rather than away from the solution is the key. Part of that process is something like you suggest, looking at the curve and eyeballing where the solution appears to be (though in this case, your guess of 115 seems to be too large).

I would suggest you spend some time with a tutorial (the UofU tutorial I linked to has a nice Java applet that illustrates many of the properties of the NR algorithm). The function you have is, in many ways, similar to example 7 in the tutorial. If your initial guess for x is too large or too small, the slope of the function is very close to 0, causing the algorithm to diverge. I think the key to the initial guess in your case is to pick an initial guess that does not give a slope too close to 0.

Would solver be able to get an exact value for me?

That depends on what you mean by exact. In the strictest sense of the word "exact", computers almost never give us "exact" solutions. Excel, like almost all computer programs/programming languages, is limited to double precision in displaying and using numbers, so you cannot get more than 15 digits accurately. A numerical algorithm like this will probably only be good to about 10 digits at most before you start to worry about having it fail to converge because of round off error. Most experienced programmers I've seen will program the NR algorithm to converge at part per million or part per billion precision because that is usually "good enough" even though it isn't "exact". Solver allows you to control the convergence criteria in the options dialog.

[kd5649]

Thanks, I'll do some reading over the weekend. They look really useful.

So, with respect to my example - how would I use solver to get a round about figure? This is where I am a bit lost.

Thanks again!

[MrShorty]

What part of using Solver are you having trouble with? Once you select a suitable initial guess (I used speed=96 because it was obvious from the graph that this point would be on the "upslope" section), it should be as easy as I described in post 2.

You might spend some time with the algebra/calculus of the function. I haven't tested the hypothesis, but I wonder if a good initial guess would be C (97 in this case). It looks like when x=C, then y will be halfway between the asymptotes y=A and y=A+B. If my hypothesis is correct, that would be a ready made initial guess provided by the regression routine.

If this is a one time problem, that might be good enough. If selecting the initial guess is going to be a real problem (especially if you are programming this for other users who may not have any clue about selecting the initial guess, then you might consider programming your own root finding algorithm rather than using Solver. A bisection method or false position method should be easy to program, and initial guesses for these algorithms should be easy to determine.