Yes, I use Solver all the time for water problems. The thing that makes water chemistry tough is the presence of a weak acid, carbonic, and its anions, bicarbonate and caqrbonate. In order to determine what is going on one has to satisfy the "proton condition" i.e. account for all the protons that may be released or absorbed when carbon dioxide, a bicarboate or carbonate salt is added to the water and in the case of carbonate, an acid to get it to dissolve. To do this requires hypothesizing a pH and then computing r1 = 10^(pH - pK1) and r2= 10^(pH-pK2) where the 2 pK's are those of carbonic acid. If lactic or phosphoric or citric or any other weak acids are being used then one r must be computed for each pK of the each acid. The r's are used to compute mole fractions for, in the case of carbo, carbonic, bicarbonate and carbonate species and from the mole fractions the deficit or surplus of protons can be computed. The object is to find the pH which satisfies the proton condition (no deficit or surfeit). Water chemists refer to pH as the "master variable" because of this. As there is plenty of non linearity here it's a perfect job for the Solver.
The Solver is also great for figuring out what to add to match a profile and it can simultaneously do this and satisfy the proton condition if you use the proton condition as a constraint and the weighted mse as the thing to be minimized over. It should be clear that where salt/acid/CO2 additions are being computed that there should be a constraint that each addition be >=0.
It's also great for solving other problems like designing buffers, figuring out what titration pH is necessary to give a particular bicarbonate level for a specified alkalinity and so on.
Originally Posted by mabrungard
I could have included Solver in Bru'n Water, but that would assume that the brewer had access to all the minerals.
In matching profiles I use p[target_concentration] - p[realized_concentration] reasoning that control of percentage error is more significant that absolute. If I'm doing a beer with noble hops shooting for 10 mg/L sulfate and get 20 (difference of 0.3 in the p's) I think that's a lot more significant that shooting for 100 calcium and getting 110 (0.04 difference in the p's). I can also weight the delta p's. I find that using this procedure I can replicate most electrically balanced profiles very well with calcium sulfate, calcium chloride, sodium chloride, magnesium sulfate, calcium carbonate and CO2. That's because, in most cases, these are the salts and acid nature uses. In some cases sulfuric or hydrochloric acid may be required as well.
Originally Posted by mabrungard
I've also seen cases where solver comes up with some pretty bazaar answers, so I don't think having Solver provide an answer is the way to go.
The Solver will settle in on a local minimum - it can find an infinite number of solutions if you ask it to find the value of x that minimizes abs(sin(x))- and it is the responsibility of the user to determine whether the minimum it has found is the global one. This is usually pretty easy to do by "roughing in" the approximate answers by hand and then letting Solver refine it.
Given the nature of the chemistry I sketched above iterative solution is required. Solver is Excel's way of giving you iterative solution and thus is really necessary for anything other that approximate results. The problem lies, I think, in the phrase "it is the responsibility of the user to determine whether the minimum it has found is the global one". This requires a pretty good "gut feel" for the underlying chemistry and guys just starting out in water treatment aren't going to have that. As I am now an advocate of KISS in water treatment I don't recommend Solver.
Note that Solver can be used with any
spreadsheet if it is part of your Excel installation. There is/was some nonsense with Mac users having to get it directly from its manufacturer as Microsoft isn't/wasn't supporting it on the Mac. Don't know what the status on that situation is currently.