The py_SolveFS function calls the Scipy optimize.root function to solve multi-variable equations. Examples are found on the py_Solvers spreadsheet, see Scipy Functions with Excel and pyxll 4 – Solvers 2.

One problem with using this function is that the input data must be arranged to suit the Scipy root function, as shown on the help screen below:

The root function requires an initial guess for each of the unknown values (x0). Any additional known variables are passed in the args parameter, which is a tuple, that is a list or list of lists. If py_SolveFS is called from an interface function, that function can arrange the input in the required format, but if it is called directly from the spreadsheet, and there are many input values, it is convenient if the data can be arranged in a range, with each column being treated as a 1D array, that will be converted to a Python list.

To allow this to be used in a flexible way I have added an optional TransposeVals argument to the py_SolveFS function, with a default value of True. If input arrays are arranged row-wise on the spreadsheet this should be set to False.

The revised inputs for the function are:

If TransposeVals is omitted or set to False the Values data must be arranged in columns, either as a single column:

Or as a range with two or more columns:

In the example above the py_SolveFS function is called both indirectly with py_CurveatMA, and directly. For the indirect case the Values data is in Columns C and F. For the direct case the data has been copied to adjacent columns (J and K). In both cases the target values are passed as a single column array, which is combined with the Values data in py_SolveFS.

The new version of py_SolveFS is included in the Scipy download file at:

py_Scipy.zip

For information on the pyxll package, required to connect the Python code to Excel, see: https://newtonexcelbach.com/python-and-pyxll/.