spreadsheets and excel solver add on - Home Brew Forums
Register Now For Free!

Home Brew Forums > Home Brewing Beer > Brew Science > spreadsheets and excel solver add on

Thread Tools
Old 03-11-2010, 04:38 AM   #1
Mar 2010
Posts: 55

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.

Reply With Quote
Old 04-05-2011, 08:51 PM   #2
Apr 2011
Pearland, TX
Posts: 9

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?

Reply With Quote
Old 04-05-2011, 09:25 PM   #3
mabrungard's Avatar
Feb 2011
Carmel, IN
Posts: 4,309
Liked 681 Times on 528 Posts

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.
Martin B
Carmel, IN
BJCP National
Foam Blowers of Indiana (FBI)

Brewing Water Information at:

Like Bru'n Water on Facebook for occasional discussions on brewing water and Bru'n Water

Reply With Quote
Old 04-05-2011, 10:30 PM   #4
Apr 2009
Morristown, NJ
Posts: 104
Liked 6 Times on 4 Posts

I would also add a set of constraints setting the maximum absolute deviation around each ion.

Reply With Quote
Old 04-06-2011, 04:29 AM   #5
Aug 2010
McLean/Ogden, Virginia/Quebec
Posts: 9,426
Liked 1565 Times on 1191 Posts

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 View Post
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 View Post
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.

Reply With Quote
Thread Tools

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lets see your brewing Excel Spreadsheets LS_Grimmy General Beer Discussion 19 03-22-2011 10:57 PM
Water-building software/spreadsheets Stevorino General Techniques 1 06-02-2009 04:53 PM
Fun with excel uwjester General Beer Discussion 5 02-14-2009 12:14 AM
Brewing Spreadsheets. Orfy General Techniques 8 12-30-2006 02:29 PM
Problem solver Cheesefood Drunken Ramblings and Mindless Mumbling 2 08-19-2006 05:25 AM

Forum Jump