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
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