Just got back into brewing and have been tinkering with brewing water spreadsheets and was wondering if anyone has used the excel solver add on. I know its a bit of a pita to set up, but once done you can find the mineral/salt additions and dilution rates with a click of a button.
spreadsheets and excel solver add on
- Thread starter ALF
- Start date
Help Support Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum:
I started with John Palmer's spreadsheet that is available online and started using solver with it. The only problem with using solver is that it needs a single number to try and minimize. I ended up minimizing the sum of absolute deltas between each water characteristic to come up with a solution. It works and it gets you closer than just guessing. I have not actually adjusted any of my mash waters yet, I just wanted to see if solver could be used, it can, it's just not perfect...I am also not a pro with solver, maybe someone else has some advice?
I've done plenty of multi-variable solutions with Solver for some of my engineering problems. You just have to have enough equations by which to resolve the solution.
I could have included Solver in Bru'n Water, but that would assume that the brewer had access to all the minerals. 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.
I could have included Solver in Bru'n Water, but that would assume that the brewer had access to all the minerals. 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.
artbrau
Well-Known Member
I would also add a set of constraints setting the maximum absolute deviation around each ion.
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.
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.
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.
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.
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.
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.
Similar threads
