Iterative solving

[artz]

I would like to perform a calculation that iterated until the calculation matches the value in a particular cell.

To start out, we find the minimum value in column I: = min(I8:I1000)

In this example, the minimum value is 28.63 found in cell I11/ (the minimum could be any cell in the range I8:I1000)

The iteration involved would be to step KMAX in cell O11 until O11= 28.63, matching the minimum in the range I8:I1000. The formula in O11 is: =IF(I11<>"",slope*$A11+intercept-KMAX/50,"")

Note: A slider is currently used to manually vary KMAX.

Formulas for O10 and O12, for reference, are:

O10 » =IF(I10<>"",slope*$A10+intercept-KMAX/50,"")
O12 » =IF(I12<>"",slope*$A12+intercept-KMAX/50,"")

KMAX would then be the iterated value of KMAX and would then be used to calculate all cells in the range of O8:O1000. A tolerance can be applied if it helps to cut down the calculation time or improves convergence.

Can this implemented as either a direct cell calculation or in a simple VBA routine without using Solver?

Thanks,

Art

 

[iliace]

Why O11? Is it a specific cell to be used each time, or because your example has I11 as the minimum in column I?

If this value is dependent on the row of the min of I, then wouldn't the value of the corresponding cell in column O always equal to that of column I? The formula looks like it would yield the same result. Or is the formula in I11 different from formulas in other cells in column I? Is the formula in O11 same as in O10 and O12?

How do you plan to treat blanks in column I?

What is the range of the slider for KMAX, and is that the range in which you want to look?

Why not use Solver?

 

[artz]

Thanks for your response.

Yes, because the example has I11 as the minimum in column I, I made the corresponding calculation in O11.

>>If this value is dependent on the row of the min of I, then wouldn't the value of the >>corresponding cell in column O always equal to that of column I? The formula looks like it >>would yield the same result. Or is the formula in I11 different from formulas in other cells in >>column I? Is the formula in O11 same as in O10 and O12?

No because the value of KMAX can be set independently. Currently, I manually set KMAX (via a slider) until the charted value minimum value from column I and the calculated value in O appear to coincide on the chart.

>> How do you plan to treat blanks in column I?

Data should be considered valid within the range of I8:I1000. A blank encountered in that range would signify that the last data point is in the previous cell.

>> What is the range of the slider for KMAX, and is that the range in which you want to look?

The slider is currently set for a range of 0-1000 in increments of 1. The range out to 1000 should be sufficient, however, to converge on the cell value, the increment may need to be .01.

>> Why not use Solver?

Since the calculation to (or manual adjustment) is performed overe and over, I thought that a VBA routine which does this "automatically" would be a better way to go.

Thanks,

Art

 

[tusharm]

{snip}
>> Why not use Solver?

Since the calculation to (or manual adjustment) is performed overe and over, I thought that a VBA routine which does this "automatically" would be a better way to go.

Thanks,

Art

Solver is designed by a company that specializes in optimization work. Use it. Once you set up the Solver model, it takes no more work than running a homegrown macro. And, it uses techniques from Operations Research that would require an expert to program in VBA.

 

[artz]

Tushar,

It strikes me as overkill to use Solver. When I think that all I need is a simple VBA routine to increment a counter so that the counter output value is used as a variable in a cell calculation. The counter would run until the value in calculation cell matches the value in another cell within a tolerance. Seems as though this should be easy code for a VBA programmer.

Apparently this is more difficult than I think.

Can Solver be called from a macro that will run the Solver routine whenever I update the data? How can I implement that?

Thanks,

Art