# Iterative solving

#### artz

##### Well-known Member
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

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### iliace

##### Well-known Member
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

##### Well-known Member

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

##### MrExcel MVP
{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

##### Well-known Member
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,817
Messages
5,855,806
Members
431,765
Latest member
RedleoUK

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back