Electric Brewing Supply 30A BCS Giveaway!


Home Brew Forums > Home Brewing Beer > Brew Science > spreadsheets and excel solver add on
Reply
 
LinkBack Thread Tools
Old 03-11-2010, 03:38 AM   #1
ALF
Feedback Score: 0 reviews
Recipes 
 
Join Date: Mar 2010
Location: Houston
Posts: 55
Default spreadsheets and excel solver add on

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.

__________________
ALF is offline
 
Reply With Quote Quick reply to this message
Old 04-05-2011, 08:51 PM   #2
TX-Jeff
Feedback Score: 0 reviews
Recipes 
 
Join Date: Apr 2011
Location: Pearland, TX
Posts: 9
Default Water Spreadsheet w/ Solver

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?

__________________
TX-Jeff is offline
 
Reply With Quote Quick reply to this message
Old 04-05-2011, 09:25 PM   #3
mabrungard
HBT_LIFETIMESUPPORTER.png
Feedback Score: 0 reviews
Recipes 
 
Join Date: Feb 2011
Location: Carmel, IN
Posts: 2,764
Liked 192 Times on 165 Posts
Likes Given: 24

Default

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:
https://sites.google.com/site/brunwater/

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

mabrungard is offline
 
Reply With Quote Quick reply to this message
Old 04-05-2011, 10:30 PM   #4
artbrau
Feedback Score: 0 reviews
Recipes 
 
Join Date: Apr 2009
Location: Morristown, NJ
Posts: 134
Liked 3 Times on 3 Posts

Default

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

__________________
artbrau is offline
 
Reply With Quote Quick reply to this message
Old 04-06-2011, 04:29 AM   #5
ajdelange
Senior Member
HBT_SUPPORTER.png
Feedback Score: 0 reviews
Recipes 
 
Join Date: Aug 2010
Location: McLean/Ogden, Virginia/Quebec
Posts: 6,118
Liked 612 Times on 505 Posts
Likes Given: 19

Default

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.

Quote:
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.

Quote:
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.
__________________
ajdelange is offline
 
Reply With Quote Quick reply to this message
Reply



Quick Reply
Message:
Options
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-13-2009 11:14 PM
Brewing Spreadsheets. Orfy General Techniques 8 12-30-2006 01:29 PM