Iterative solving

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,543
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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
Joined
Aug 11, 2002
Messages
791
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
{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
Joined
Aug 11, 2002
Messages
791
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,089
Messages
5,599,667
Members
414,326
Latest member
Aerith

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top