Excel parametric sweep

Occasional Visitor

I have an worksheet which can calculate multiple parameters based on some input variables (also entered in the same worksheet). The calculations are iterative (100 iterations). 

I want to generate a table where on of the input variables are swept from a starting value to a final value at a specified linear steps and one of the output paraemter values are populated/generated from the already existing calculation in the worksheet. The end goal is to generate a plot of the parameteric sweep automatically. 

Is it possible? If yes, please help me.

1 Reply

@sdam123 

It sound like one of those calculations that may be possible but, equally, could go badly wrong.  Can you demonstrate that the iterative calculation is sure to converge for each parameter set?  Also, be aware that it is possible that an iterative scheme will converge up to a particular size array and then diverge.

 

There are ways of examining elements of your calculation before plunging into the unknown of iterative calculation.  For example, you could use a row to capture the current state of the calculation and use the next row to take it forward one iteration.  If you have Excel 365, the REDUCE function may be used to perform such a step and provide insight as to the nature of your proposed solution.

 

There are mathematical techniques to improve and stabilise iterative calculation, (Runge-Kutta) but these would normally be implemented as compiled code rather than Excel.  To perform such calculations in Excel might well be ground-breaking.  Or you could simply do the equivalent of 'hold your nose and jump' and let us know what happens!