Iterate until cell values match

Iterate until cell values match

The thread posted today "Recalculate sheet repeatedly until condition met" is similar but I don't know how to apply the code in that thread to my calculation. What I am trying to do is described below.

The following expression calculates the standard deviation of a linear regresion fit to a data set.

The calculation is performed in cell R14: =KMAX/STDEVP($I$8:INDEX($I$8:$I$500,R11))/50 and yields the standard deviation value which is

typically in the range 0-3.0.

The desired standard deviation input is in cell R15. How can the variable KMAX be iterated until the desired standard deviation value

is achieved in cell R14 matching the desired standard deviation in cell R15?

Depending on the data, the variable KMAX typically varies between 0 and 1000.

Can anyone help?

Thanks,

Art

How about Solver?

Using Solver has been suggested. Honestly, I prefer to be "closer" to the calculation being performed. Using a VBA routine is also more flexible.

Art

Unless you have a closed-form solution, you'll need to write something that does the same thing Solver does (using Newton-Raphson or other root-finding methods). Depending on the behavior of the dependent data, that could range from straightforward to complex.

I think that it's easier than than that. If I manually increment the variable KMAX in integer steps, I can get to the value of the desired standard deviation. I think that if the loop simply incremented KMAX in a similar manner, i.e., integer steps, the two cells would match quickly.

Art

Could be. If (simplest case) the STDEVP part isn't influenced by KMAX, you can just solve it in closed form.

That's the key, STDEVP isn't influenced by KMAX. Any suggestions on how to set up a loop to do this?

Art

Thanks for pointing this out. I don't know How I missed it. Guess I had it in my head that I was solving for the standard deviation.

Thanks,

Art