Equilibria in Excel

The matrices defining the Model and the p-values are contained in the upper part of the spreadsheet. The given total concentrations of M and L are collected in the columns A and B, row 8 downwards. Initially guessed values for the component concentrations [M and [L] for each solution are in the respective rows of columns D and E. The next two entries, [ML] and [ML2], are calculated from the component concentrations and the respective formation constant, according to equation (3.23). Next, the calculated total concentrations are computed, making sure the stoichiometric coefficients are incorporated correctly, equation (3.30). The task is to juggle the initially estimated component concentrations [M and [L] in columns D and E until the calculated total concentrations collected in the columns I and J match the known concentrations in the columns A and B. The reader is encouraged 

The actual task of finding the correct free concentrations [M and [L] is undertaken by the Solver. The Solver is a very powerful tool in Excel. It can be employed to maximise and minimise functions of many variables and to find solutions to functions of many variables. The Solver can be found in the Tools menu. If it is not there, it has to be installed as an Add-In, also found 

In the Matlab programs, every single absolute difference between known and computed total component concentrations defines the termination criterion. In Excel, the Solver termination criterion is the sum of all the absolute 

A few comments and observations are appropriate. The quality of the initial guesses for the free concentrations of the components is more critical than in the Matlab Newton-Raphson routine introduced previously. The main disadvantage of the Solver, however, is the fact that it can only be applied to one instance. It cannot be dragged around on the spreadsheet like most other functions of Excel. It means, for our present example, that for each solution the Solver needs to be set up individually, defining the Set Target 

the By Changing Cells and also the Subject to the Constraints list. A 

---

It is also possible to estimate activity coefficients, usually as a function of ionic strength, size and charge of the ions. We give one example of such calculations in Equilibria in Excel (p. 60). 

It is possible to resolve complex equilibria in Excel. One feasible way of setting up a spreadsheet is represented in the example of Figure 3-14. It computes the equilibrium concentrations in a titration of a solution of a metal M with a ligand L. Two complexes are formed ML and ML2. It is a 2-component 4-species system. 

---