I have been using the Libre Office Solver tool (similar to Excel Solver) to mass balance some chemical systems. I know the chemical compositions of the various phases in the system and the chemical composition of the system as a whole.

Briefly, I use Solver (in Excel or LibreOffice Calc) to find proportions of the various phases such that the computed sum of the chemical composition of the phases (weighted by the proportions) is as close to the known bulk chemical composition of the entire system. The Solver works to minimize the difference between the computed sum of each chemical component across all the phases (weighted by proportion of that phase) and the known amount of that component in the bulk system. A spreadsheet provided by Eric H. Christiansen lays this out very nicely, with some examples.

My question is how do I propagate uncertainty in the chemical composition of the phases in to an uncertainty in the phase proportions that Solver finds.

I hope this question is clear. I know that this is do-able as I have seen scientific literature in which the phase proportions have uncertainties that the authors say are based on the uncertainty in the phase compostions. E-mailing the author for more information has not proved fruitful and neither have my attempts at finding an explanation online.

Many thanks in advance, -R

  • $\begingroup$ Are you really good at programming VBA macros in Excel? If not, the first step is to change platforms to a programmable statistical package. That's an advisable step anyway. $\endgroup$
    – whuber
    May 18, 2017 at 21:51
  • $\begingroup$ No, have no VBA experience. Is there a way to do this in R, which I do have some experience with? $\endgroup$
    – ramesesjd
    May 18, 2017 at 21:53
  • 1
    $\begingroup$ Yes, because R includes Solver-like capabilities in its multivariate optimization functions. Depending on what you learn in the answers, I think it likely you will need to run simulations in which you perturb the data according to a probability model consistent with the uncertainties, obtain a new solution, store it, and repeat many times. That's why the programming capabilities will be so useful. It may be that some diligent reader will be able to articulate the model underlying the spreadsheet and use it to analytically approximate the output distribution. $\endgroup$
    – whuber
    May 18, 2017 at 21:54

1 Answer 1


Following on from whuber's comments, I sought a non-spreadsheet solution. Some creative searches turn up this discussion of a very similar problem. It appears to do what I need, though not simply.

Now I just need to figure out what it's doing and then extend it to my own situation. (Any tips or pointers are appreciated.)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.