Excel Solver has some limitations for production-ready data reconciliation, despite Excel being used often for process manufacturing production and operational accounting. To overcome these limitations, Resolver is an Excel add-in using the same model configuration as Solver, but using a solver that is optimized for the data reconciliation problem. This results in a fast, robust, and very accurate solver for linear, nonlinear, equality, and inequality data reconciliation problems. The combination of Excel and robust solver could bring ubiquity to process data reconciliation.

Resolver is an Excel add-in that performs data reconciliation using a high performance, accurate, and robust algorithm optimized for the data reconciliation problem. It uses the same problem setup as the Solver Excel-add-in. In fact the problems are interchangeable. You can setup and solve a problem in Resolver and then use the same setup and problem definition to try and solve it with Solver, just in case you want verification of the results!

Resolver came about as a result of trying to use Solver to solve process data reconciliation problems, as reported in Is Excel Solver suitable for Production Data Reconciliation?. As noted, Excel has a lot of advantages for setting up process data reconciliation problems, and is widely used for operational and production accounting. Along with some advantages, some issues were revealed which have been overcome with Resolver.

Excel Data Reconciliation Problem Setup

Resolver uses a reconciliation model defined via constraint equations expressed as Excel cell formula in the same way as Solver.

Solving with Resolver

The data reconciliation problem is defined in a very similar way to Excel Solver. In fact the model definitions are interchangeable, although Resolver can only solve for the Gaussian objective function, and Solver cannot produce the reconciliation diagnostics.

Resolving will write the results back into the variable cells, and also produces a report to help diagnose the reconciliation problems. This produces the well established gross, constraint, and measurement error diagnostics.

Solver versus Resolver Excel Process Data Reconciliation

Advantages of Resolver-based Data Reconciliation

• The use of symbolic differentiation of the constraint equations eliminates the need for numeric differentiation and its inherent inaccuracy
• Resolver does not rely on Excel to recalculate the objective function which makes it very fast.
• The constraint-sequential solution technique ensures solution stability even when the problem is poorly formulated, such as zero tolerances, and redundant constraints.
• Covariance factorization improves numeric stability and accuracy.
• Compatible (and interchangeable) with Solver problems
• Data reconciliation can take place within the same spreadsheet environment with which many production and operational accounting systems exist.
• Constraints are defined as Excel cell formula, not strings within Excel cells that use the reconciliation application’s own syntax.

Disadvantages of Resolver-based Data Reconciliation

• There is no visual modeler or designer of the flowsheet or problem.
• Constraint formulae need to be differentiatable.

Advantages of Solver-based Data Reconciliation

• “Very easy to prepare data within Excel and to post process the results for presentation.”
  • Resolver uses the same setup and problem definition. In fact they are interchangeable.
• “Solver is not limited to linear-only constraints, thus component balance (bi-linear) reconciliation problems can be handled, although they were not tested here.”
  • Resolver can handle the same range of problems which includes the ‘traditional’ linear balances, non-linear balances, and inequality constraints. To achieve its accelerated and robust convergence Resolver uses symbolic differentiation of the constraint formula provided in the Excel cells and their precedents. At present not all Excel functions are supported. Additional functions will be supported in further releases of Resolver.
• “Solver is not limited to equality constraints, thus for example all estimates can be forced to be positive.”
  • Resolver supports inequality constraints, as well as the convenient options that forces all variables to be positive (AssumeNonNegative)

Limitations of Solver-based Data Reconciliation

• “Solver uses a generic solver but is not optimized for the characteristics of a data reconciliation problem, such as linear or bi-linear problems, and quadratic cost functions.”
  • Resolver uses an algorithm⁴ optimized for the data reconciliation problem that is fast, accurate and robust.
• “It is easy to define a problem that becomes unstable. In the example, adding the redundant constraint of the ‘world’ causes solution instability.”
  • Resolver uses an algorithm[1] that is very efficient and robust. It is optimized to handle some of the quirks of the process data reconciliation problem such as variables that differ significantly in numeric value, unobservable variables, and variables that we wish to ‘fix’[2].
• “Irrespective of objective function, Solver took 25~75 iterations to converge. This could be an issue for realistically sized (100~1000’s of variables) reconciliation problems.”
  • Resolver will solve linear data reconciliation problems with equality and inequality constraints immediately. Nonlinear problems are usually solved within a few (5~15) iterations.
• “There is no access to such intermediate products such as the covariance matrix with which to perform more sophisticated gross error detection.”
  • Resolver produces all of the intermediate diagnostics needed to perform gross error detection, measurement error detection, and constraint error detection. These are made available so users can superimpose their own constraint and measurement removal algorithms.

Excel Data Reconciliation Model Setup

Model Constraints

Resolver uses a reconciliation model defined via constraint equations expressed as Excel cell formula in the same way as Solver.

Thus we can express the reconciliation model constraints as Excel formula within cells using the familiar Excel syntax. As usual Excel will display the numeric result of these formulae as shown below. I chose to organize the formula to show the total feed and total product in adjacent cells, but other formulations are possible.

However Excel also allows us to ‘Show Formulae’, offering the best of both worlds. Since I chose to use named ranges, the constraint formulae are very readable. Not only that, we can use ‘Trace Precedents’, and ‘Trace Dependents’ to track the relationship between constraints, a level of user interaction that is difficult to duplicate in dedicated reconciliation applications.

Also, of course, there is nothing to limit you to linear reconciliation problems in Excel.

Model Variables

Reconciled variables are organized as named cells so that the constraint equations are more readable. These will be the variables adjusted by Solver and Resolver. In principle these variables could be distributed throughout the spreadsheet. However it is more convenient to group them with the tolerances and estimates (measurements) in adjacent cells:

Furthermore the named ranges in the Labeled Variable column were in fact ‘arrays’ pointing to the Solution, Tolerance, and Estimates.

This is a convenient way of grouping the solutions with the corresponding tolerances and estimates in a way that is compatible with both Solver, that only requires access to the variable value, and Resolver, that needs access to the corresponding measurement and tolerance. As a reminder, to create an array variable select the cells as shown below and complete the edit with ‘Control-Shift-Enter’

This will be shown as follows in the Excel edit box. Note the curly-brace {…} indicating that this is an array formula.

Reconciliation objective function

Resolver minimizes the straight-forward sum-of-weighted-squares (Gaussian) formulation. Most reconciliation systems will use a straight-forward sum-of-weighted-squares (Gaussian) formulation for this function. It can be argued that the measurement errors are drawn from a Gaussian distribution. Aside from this fact the Gaussian formulation is popular mainly because, in the case of linear constraints such as mass balances, the objective function can be solved without the need for an iterative search.

For convenience the Resolver add-in includes a Gaussian function that will calculate the same objective function as used by Resolver. When a model is saved, a cell is created that contains a suitable target which Solver can use.

Reconciling with Resolver

Define and Solve a Reconciliation Problem

On the Data tab, in the Resolver group, click Resolver.

• If the Resolver command is not available, load the Resolver Add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.)

1. Click the Microsoft Office Button , click Excel Options, and then click the Add-ins
2. In the Manage box, click Excel Add-ins, and then click Go.
3. In the Add-ins available box, select the Resolver Add-in check box, and then click OK.

In the Subject to constraints frame, enter any constraints

(constraints: The limitations placed on a Solver problem. You can apply constraints to adjustable cells, the target cell, or other cells that are directly or indirectly related to the target cell.) that you want to apply.

• How to add a constraint or a range of constraints

1. In the Constraint frame box, under Subject to the Constraints, Click on the ellipsis (…) to identify the cell or range that defines the right-hand or left-hand side of the constraint.
2. In the Cell Reference box, enter the cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) or name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) of the cell range for which you want to constrain the value.

3. Repeat for the other side of the constraint, and select an alternative equality if required. This equality will be applied to all of the constraints within the selected range.

4. Click the Add button to add this constraint to the set of constraints that define the reconciliation problem. The constraint will be checked to ensure the same number of cells is defined in the ranges on the left-hand and right-hand side of the constraint.
5. This can be repeated until all constraints have been added to the reconciliation problem.

• How to update or delete a constraint

1. Select a constraint to be updated or deleted from the constraints list.

2. If Edit is chosen the constraint is transferred to the Constraint frame where it can be updated.
3. If Delete is chosen the constraint is removed from the model.

In the By adjusting variables frame, enter the variables that you want to reconcile.

• How to add a variable or range of variables

1. In the Variable frame click on the ellipsis (…) to identify the cell or range that defines the variables. These are the array formula cells described above. The use of an array formula provides consistency with Solver and the ability for Resolver to access the corresponding measurement and tolerance values.

2. Click the ‘Add’ button to add this variable or variables to the set of variables that define the reconciliation problem. This can be repeated until all variables have been added to the reconciliation problem.

• How to update or delete a variable

1. Select a variable or range of variables to be updated or deleted from the variable list.

2. If Edit is chosen the variable is transferred to the Variable frame where it can be updated.
3. If Delete is chosen the variable is removed from the model.

• How to let Resolver guess the reconciled variables

1. Use the Guess button to use the defined constraints to guess the adjustable (reconciled) variables.
2. Guess will add any additional variables to the set of variables. It usually does a good job, but success is not guaranteed.

• How to consolidate the list of reconciled variables

1. Use the Consolidate button to merge the variables into one or more ranges if the list of variables becomes fragmented.

Click Resolve to initiate the reconciliation.

• To use the reconciled values on the worksheet, click Keep Resolver Solution in the Resolver Results dialog box.
• To restore the original state, click the Restore Original Values.
• To create a report that is based on your solution, you can click Answer Report. The report is created on a new worksheet in your workbook. If Resolver does not finds a solution the report option is unavailable.

Save or load a problem model

1. In the Resolver Parameters dialog click Save Model or Load Model
2. When you click Save Model enter the reference for the first cell of a vertical range of empty cells in which you want to place the problem model. The number of cells used depends on the number of constraint ranges defined. The saved model is compatible with Solver, but has an additional cell that defines an equivalent objective function that Solver would need to use as a target.

1. When you load a model enter a reference for the first cell.

Control how Resolver finds solutions

1. In the Resolver Parameters dialog, click Options.

The Option button provides access to the convergence control options available to Resolver. These options are designed to be consistent with Solver.

• How to control solution time and iterations
  1. In the Max Time box, type the number of seconds that you want to allow for the solution time.
  2. In the Iterations box, type the maximum number of iterations that you want to allow.
• How to control convergence and accuracy
  1. In the Precision box, type the degree of precision that you want, the smaller the number the higher the precision. The precision is a measure of the magnitude of deviations of constraints.
  2. In the Convergence box, type in the amount of relative change that you want to allow in the objective function (Gaussian) before Resolver stops with a solution. The smaller the number, the less relative change is allowed.
• How to initialize estimates to improve convergence
  1. Select Initialize Values if you want Resolver to use the current values of the adjusted variables as a starting point. Otherwise it will use measurements as the starting point where ever available.
• How to add non-negativity constraints
  1. Select Assume Non Negative if you want Resolver to force all variables to be greater than zero. This is equivalent to adding additional inequality constraints, one for each adjusted variable.

Solver Options

You can control advanced features of the solution process. Each option has a default setting that is appropriate for most problems.

Max time Limits the time taken by the solution process. While you can enter a value as high as 32,767, the default value of 10 (seconds) is adequate for most problems.

Iterations Limits the time taken by the solution process by limiting the number of interim calculations. While you can enter a value as high as 32,767, the default value of 30 is adequate for most problems.

Precision Controls the precision of solutions by using the number that you enter to determine whether a composite measure of the constraint residuals is small enough. Precision must be indicated by a fractional number between 0 (zero) and 1. Higher precision is indicated when the number that you enter has more decimal places — for example, 0.0001 is higher precision than 0.01.

Convergence When the relative change in the target cell value is less than the number in the Convergence box for the last five iterations, Solver stops. Convergence applies only to nonlinear problems and must be indicated by a fractional number between 0 (zero) and 1. A smaller convergence is indicated when the number that you enter has more decimal places — for example, 0.0001 is less relative change than 0.01. The smaller the convergence value, the more time Solver takes to reach a solution.

Assume Non-Negative Causes Resolver to assume a lower limit of 0 (zero) for all adjustable variables.

Interpreting the Results

Error Detection Diagnostics

The diagnostics to detect gross measurement and balance errors are just as (if not more) important than the reconciled results. Thus Resolver provides a comprehensive set of diagnostics as follows:

Scope	Measure	Metric Names
		Measured (A Priori) Metric calculated based on measurements results and only use reconciled values where appropriate	Reconciled (A Posteriori) Metric calculated based on reconciled results and only use reconciled values where appropriate
Global	Cost	Measured Cost: The value of the objective function at initialization.	Reconciled Cost: The value of the objective function at convergence, as calculated by Resolver
	Critical Value	n/a	Global Critical Value: the global gross error detection value as defined by Madron[3]. If the Reconciled Cost exceeds this value, then there is likely to be a gross error … somewhere.
Variables	Value	Measured Value: the value of the measurement, if any.	Reconciled Value: the values of the adjusted variable at convergence.
	Tolerance	Measured Tolerance: the tolerance (95% confidence level), of the measurement	Reconciled Tolerance: the tolerance of the result as determined by Resolver.
	Test	Measured Test: the difference between the Measurement and Reconciled Value normalized by the Measurement Tolerance. This is used only as an indicator of the magnitude of the measurement adjustment.	Reconciled Test:  the difference between the Measurement Value and Reconciled Value, normalized by the Reconciled Tolerance. This should not exceed the Measurement Critical Value unless there is a gross error.
	Critical Value	n/a	Measurement Critical Value: the measurement gross error detection value as defined by Tamhane[4]. If a measurement error exceeds this value, then it is likely to be a gross error
Balance	Residual	Measured Residual: the residual of the constraint calculated using the original Measurement Value values where available and the Reconciled Value where not.	Reconciled Residual: the residual of the constraint using the reconciled results. This should be zero or close to zero. Closeness to zero is controlled by the Precision option.
	Deviation	Measured Deviation: the expected variability of residual errors as defined by the measured tolerances.	Reconciled Deviation:  the expected variability of residual errors as determined by Resolver.
	Test	Measured Test: the Measured Residual normalized by the Measured Deviation. This is used only as an indicator of the magnitude of constraint adjustments.	Reconciled Test: the Measured Residual normalized by the Reconciled Deviation. This should not exceed the Constraint Critical Value unless there is a gross error.
	Critical Value	n/a	Constraint Critical Value: the constraint or nodal gross error detection value as defined by Mah et al[5]. If a constraint error exceeds this value, then it is likely to be a gross error caused either by an associated measurement error or a missing variable from the constraint equation.

 

Reconciliation Report

The reconciliation report has three sections that provide both the results and the diagnostics.

1. Problem
   1. Total Constraints: The total number of linear, nonlinear, equality, and inequality constraints specified in the model.
   2. Linear: Number of linear equality constraints.
   3. Nonlinear: Number of nonlinear equality constraints
   4. Active Linear: Number of linear inequality constraints set to active.
   5. Active Nonlinear: Number of nonlinear inequality constraints set to active.
   6. Variables: Number of adjustable (reconciled) variables.
   7. Fixed variables: Number of variables that are fixed by virtue of a 0.0 tolerance.
2. Convergence
   1. Converged: True if converged within the specified precision and convergence.
   2. Termination: The condition that caused termination of convergence, whether or not successful.
   3. Time: Time in seconds to achieve convergence within the specified precision and convergence.
   4. Iterations: Number of iterations needed to achieve convergence.
   5. Precision: The precision at convergence.
   6. Convergence: The measure of convergence, the relative change of the cost, at convergence.
3. Target
   1. Cell: The cell containing the Gaussian objective function that would be minimized by Solver
   2. Reconciled Cost: The value of the objective function at convergence, as calculated by Resolver
   3. Global Critical Value: the global gross error detection value as defined by Madron[6]. If the Cost exceeds this value, then there is likely to be a gross error … somewhere.
   4. Redundancy Degree: the number of degrees of freedom in the defined model.
   5. Measurement Critical Value: the measurement gross error detection value as defined by Tamhane[7]. If a measurement error exceeds this value, then it is likely to be a gross error.
   6. Constraint Critical Value: the constraint or nodal gross error detection value as defined by Mah et al[8]. If a constraint error exceeds this value, then it is likely to be a gross error caused either by an associated measurement error or a missing variable from the constraint equation.
4. Adjusted Variables
   1. Cell: the cell address or name containing the array of solution, tolerance, measurement values.
   2. Reconciled Value: the values of the adjusted variable at convergence.
   3. Measured Value: the value of the measurement, if any.
   4. Solvability: the solvability of the variable:
      1. Unobservable: the variable is neither measured nor deducible from the model.
      2. Observable: the variable is unmeasured but is deducible from the model.
      3. Determined: the variable is measured but there is insufficient information in the model of other measurements to provide alternate estimates.
      4. Redundant: the variable is measured and there is information in the model and other measurements to provide alternate estimates.
      5. Fixed: the variable is effectively constraint by virtue of its 0 (zero) tolerance.
   5. Reconciled Test: the difference between the Measurement Value and Reconciled Value, normalized by the Reconciled Tolerance. This should not exceed the Measurement Critical Value unless there is a gross error.
   6. Measured Test: the difference between the Measurement and Reconciled Value normalized by the Measurement Tolerance. This is used only as an indicator of the magnitude of the measurement adjustment.
   7. Measured Tolerance: the tolerance (95% confidence level), of the measurement.
   8. Reconciled Tolerance: the tolerance of the result as determined by Resolver.
5. Constraints
   1. Cell: a simplified expression of the two cells and equality that define the constraint.
   2. Active: an indicator of the state of the constraint. Inequality constraints are False until admitted to the active-set.
   3. Measured Residual: the residual of the constraint calculated using the original Measurement Value values where available and the Reconciled Value where not.
   4. Reconciled Residual: the residual of the constraint using the reconciled results. This should be zero or close to zero. Closeness to zero is controlled by the Precision
   5. Measured Test: the Measured Residual normalized by the Measured Deviation. This is used only as an indicator of the magnitude of constraint adjustments.
   6. Reconciled Test: the Measured Residual normalized by the Reconciled Deviation. This should not exceed the Constraint Critical Value unless there is a gross error.
   7. Reconciled Deviation: the expected variability of residual errors as determined by Resolver.
   8. Formula: the constraint equation spelt out using the variable names, and including any dependent cells.

Resolver Frequently Asked Questions

Does Resolver always converge to the correct solution?

If the problem is linear, then convergence is guaranteed. In fact it will converge to the global minimum in a single iteration.

If the problem includes nonlinearities then convergence might take several iterations before it arrives at the minimum. There is no absolute guarantee that this is the global minimum, but for most data reconciliation-like problems it is invariably the correct solution. 

If the problem includes inequalities as well as nonlinearities then it is possible to define an infeasible problem.

Can Resolver access cells on other sheets?

No. All cells that are directly or indirectly referred to in the constraints equations need to be on the same worksheet.

What are the limits on the constraint equations?

The constraint equations need to involve only differentiable functions: +, -, /, *, ^, SUM(…), PRODUCT(…),… We are working on ways to handle the other, non-differentiable, functions.

Can I use my own user defined functions?

Not yet. Resolver needs to be able to differentiate the constraint, and this is not possible for user-defined functions.

Why do I need a license to run Resolver?

Resolver needs either a trial license valid for 20 days, or a full license usable for an unlimited duration but limited to a single machine. The license price is modest to encourage the use of process data reconciliation.

What Algorithm and methods used by Resolver?

The unique Resolver solution algorithm is best described as constraint sequential. It starts off with a solution surface based on the initial estimates and no constraints. It then takes one of the constraints and adjusts the estimates taking into account this individual constraint. This then moves the solution to that new position, and the process is repeated for the next constraint. If the constraints are equality or inequality linear, then each step is the same as ‘Newtonian’ convergence. The advantages of this algorithm are very high numerical stability, due in part to the UDUT covariance factorization, even for badly formed numerical problems. However, if the constraints are non-linear then Newtonian convergence can be too severe, the algorithm reduces the size of the step that the algorithm takes towards the optimum. At the same time the direction of the step swings away from ‘Newtonian’ to ‘steepest descent’ direction. As the step size reduces, the algorithm guarantees convergence, albeit more slowly.

One pass through all of the constraints is sufficient for linear-only constraints. If there are non-linear constraints then the solution is used as a new starting point, and the procedure repeated.

Inequality constraints are handled by the use of the active-set methodology. At each stage the most ‘active’ inequality constraints are added until no more inequality constraints are in violation.

References

[1] “Multi-frequency Testing of Nonlinear Systems”; Lawrence, P (Oct 1980); PhD Thesis, University of Wales Institute of Science and Technology, Chap 7

[2] “Filtering and Error Analysis via the UDUT Covariance Factorization”; Thornton, C; Bierman, G (Oct 1978); IEEE Trans AC-23 Issue 5, pp 901-907

[3] “Statistical Analysis of Material Balance of a Chemical Reactor”; Madron, T., Veverka, V., and Venecek, V. (1977); The American Institute of Chemical Engineers Journal, 23, 482-486.

[4] “A Note on the Use of Residuals for Detecting an Outlier in Linear Regression”; Tamhane, A. C. (1982); Biometrika, 69, 488- 489.

[5] “Reconciliation and Rectification of Process Flow and Inventory Data”; Mah, R. S. H., Stanley, G. M., and Downing, D. M (1976); Industrial and Engineering and Chemical Process Design and Development, 15, 175-183.

[6] “Statistical Analysis of Material Balance of a Chemical Reactor”; Madron, T., Veverka, V., and Venecek, V. (1977); The American Institute of Chemical Engineers Journal, 23, 482-486.

[7] “A Note on the Use of Residuals for Detecting an Outlier in Linear Regression”; Tamhane, A. C. (1982); Biometrika, 69, 488- 489.

[8] “Reconciliation and Rectification of Process Flow and Inventory Data”; Mah, R. S. H., Stanley, G. M., and Downing, D. M (1976); Industrial and Engineering and Chemical Process Design and Development, 15, 175-18

Excel is the application platform of choice for many process manufacturing applications. Conversely, data reconciliation is not as widely used as it should be despite demonstrable benefits. Can we use Excel Solver to provide production-ready data reconciliation within Excel? This article describes an Excel template that uses Solver for data reconciliation. And the conclusions are …?

Why is data reconciliation not ubiquitous?

Excel is ubiquitous in process manufacturing as the end-user development and reporting platform of choice. It is used for data collection, end of shift reporting, accounting, modeling, scheduling, planning and (too?) much more, often frustrating the efforts of corporate IT who want more formal analysis tools and applications. Conversely over the last 25 years data reconciliation has not achieved the same ubiquitous status although it is an application that provides proven benefits of reduced accounting losses, better yield management, unreported material movement detection, and improved meter management amongst many other operational improvements. Why?

There are many aspects to the answer, but perhaps if data reconciliation were part of Excel rather than Excel just being a host for the raw data and reconciled results it could be used more easily in conjunction with the many data collection, reporting, accounting, modeling, scheduling and planning spreadsheet applications already in use.

The core computational engine of data reconciliation is the solver. Excel has an excellent built-in solver called, not surprisingly, Solver!  Since Solver uses the “Generalized Reduced Gradient (GRG2) nonlinear optimization code , which was developed by Leon Lasdon, University of Texas at Austin, and Alan Waren, Cleveland State University…” it should be well suited for data reconciliation. Is it?

The best way to answer this question is to setup Excel using Solver to reconcile a typical reconciliation problem. Below is a description of my use of Solver which is contained in the attached Excel template, Reconciliation Example.xslm

Reconciliation model constraints

Although we use Excel to prepare reconciliation data or host the results of reconciliation, the reconciliation application will formulate its own model using its own syntax. I based this evaluation on this simple material balance:

By using Solver we can express the reconciliation model constraints as Excel formula within cells using the familiar Excel syntax. As usual Excel will display the numeric result of these formulae as shown below (extracted from the attached Reconciliation Example.xslm). I chose to organize the formula to show the total feed and total product in adjacent cells, but other formulations are possible.

However Excel also allows us to ‘Show Formulae’, offering the best of both worlds. Since I chose to use named ranges, the constraint formulae are very readable. Not only that, we can use ‘Trace Precedents’, and ‘Trace Dependents’ to track the relationship between constraints, a level of user interaction that is difficult to duplicate in dedicated reconciliation applications.

Also, of course, there is nothing to limit you to linear reconciliation problems in Excel.

Reconciled variables

I organized the reconciled variables as named cells so that the constraint equations are more readable. These will be the variables adjusted by Solver. In principle these variables could be distributed throughout the spreadsheet. However I chose to group them with the tolerances and estimates (measurements) in adjacent cells:

 

Furthermore the named ranges in the Labeled Variable column were in fact ‘arrays’ pointing to the Solution, Tolerance, and Estimates;  this is a convenient way of grouping the solutions with the corresponding tolerances and estimates.

Reconciliation objective functions

Solver minimizes (or maximizes or drives to a value) a target cell which contains the value of the objective function. Most reconciliation systems will use a straight-forward sum-of-weighted-squares (Gaussian) formulation for this function. It can be argued that the measurement errors are drawn from a Gaussian distribution. Aside from this fact the Gaussian formulation is popular mainly because, in the case of linear constraints such as mass balances, the objective function can be solved without the need for an iterative search. However Solver uses a numeric search algorithm so we are not restricted to this Gaussian formulation. For this reason I created 4 alternative objective functions as VBA functions that can be called from within Excel. This avoids having to duplicate complex cell formulae, and thus reduces the risk of those pesky cell formula mistakes! The objective functions I chose were as follows, all taking one or more ranges of variables as arguments. The Fair and ContaminatedGaussian functions have additional parameters.

These functions are described below:

Gaussian

The ‘normal’ reconciliation objective function

            e = (measurement-estimate)/tolerance

            Gaussian= minimize sum{ e^2 }

Fair

A ‘robust’ estimator which is a generalization of the maximum-likelihood estimator:

Fair = minimize sum{ c^2 ( abs(e)/c – log( 1 + abs(e)/c)) }

Where c is a tuning parameter with approximate values in the range 10~20

Lorentzian

This objective function tries to avoid weighting gross errors unduly so they do not distort the overall result

Lorentzian = maximize sum{ 1/(1 + e^2/2) }

ContaminatedGaussian or Tjoa-Biegler

This objective function attempts a balance between the probability of a gross and Gaussian error; ‘nu’ is the probability of gross errors, and ‘b’ is the ratio defining the larger variance of gross errors with respect to the normal errors. Thus a value of b=10 means that the variance of gross errors is 100 (10*10) that of the normal errors. A value of nu=0.05 means there is a 5% probability of a gross error.

ContaminatedGaussian = minimize sum{ – log((1-nu) * exp(-e^2/2)

+ nu/b * exp(-e^2/(2*b)) )

+ log(sqrt(2*pi) * tolerance) }

ErrorCriticalLevel

A helper function that calculates critical error (global, constraint, or measurement) level given the degrees of freedom (number of constraints or measurements), and confidence level required, typically ~ 0.05, or 5%.

Using Solver

To solve, select Solver to display the dialog in which you can define the target cell containing the objective function, the range of cells containing the variables to be adjusted, and the range of cells containing the constraints. This setup can be conveniently saved into a range of cells for re-use.

Options allow various Solver properties to be tuned to optimize solving. However I found that the default setting of Solver would not converge to the actual solution and would drift off to an entirely different solution! I eventually found that the following parameters would usually converge to the expected solution irrespective of starting conditions, which I set to the measurements where available.

Comments on Objective Functions

The objective of this evaluation was the feasibility of using Solver for production data reconciliation, but since I tested different objective functions I want to share my observations, admittedly from a very small sample.

1. Each objective function was given the same starting point (the measurements where available) and Solver options.
2. I knew that the recycle was in gross error; it should have been 1505.
3. Gaussian, Fair, and ContaminatedGaussian all produce approximately the same estimates with the Recycle at 1485.
4. Lorentzian was far better at ignoring this gross error on the Recycle measurement, but is this what we wanted because it simply produced erroneous estimates for the unmeasured variables?
5. All took about 25~75 iterations to converge which was not an issue given the size of this reconciliation but could be significant for larger problems.
6. Given the similarity of results between the Gaussian, Fair, and ContaminatedGaussian objective functions, is it worth the complexity of moving away from the Gaussian?

Advantages of Solver-based Data Reconciliation

• Very easy to prepare data within Excel and to post process the results for presentation.
• Solver is not limited to linear-only constraints, thus component balance (bi-linear) reconciliation problems can be handled, although they were not tested here.
• Solver is not limited to equality constraints, thus for example all estimates can be forced to be positive.

Limitations of Solver-based Data Reconciliation

• The built-in Solver allows only for a maximum of 200 variables, although one can upgrade to the full version
• Solver uses a generic solver but is not optimized for the characteristics of a data reconciliation problem, such as linear or bi-linear problems, and quadratic cost functions.
• It is easy to define a problem that becomes unstable. In the example, adding the redundant constraint of the ‘world’ causes solution instability.
• The Solver was very sensitive to the estimates and derivative estimation methods when applied to the example problem and objective functions. Unfortunately it still indicated converged solution even though the results were way off. In the example we know the ‘correct’ answer allowing us to tune the solver until it gets the ‘right’ answer; in a real problem we do not.
• Irrespective of objective function, Solver took 25~75 iterations to converge. This could be an issue for realistically sized (100~1000’s of variables) reconciliation problems.
• There is no access to such intermediate products such as the covariance matrix with which to perform more sophisticated gross error detection.

Recommendations

Despite loving the ability to define non-standard objective functions and the use of Excel formula and pre-and post processing, the unreliability of the convergence to the known ‘correct’ solution means that I could not recommend it for production use especially when an ‘expert’ is not on hand to scrutinize the results.

However if we could use the Solver problem formulation within Excel but use a different optimization engine tuned for reconciliation then we would have the perfect production data reconciliation application… watch this blog!