Author Topic: Potential issue with Water Profile Tool - Calculate Best Additions  (Read 3333 times)

Offline SCWells72

  • BeerSmith Apprentice Brewer
  • **
  • Posts: 20
Several months ago I suggested integrating the optimization logic from the Excel-based Mash Water Chemistry Calculator that uses the Solver library to find the best solution for adjusting a source water profile to approximate a target water profile.  Brad, I know you replied that you had implemented a least squares technique for accomplishing the same thing, so I decided to play with it.  I'm a bit confused by the results I'm seeing, though.

For example, if I choose a base profile of Dallas, TX for 10g of water, a target profile of Munich, Germany, and no dilution at all, then click Calculate Best Additions, I get recommended additions for 2.5 grams of baking soda and 6.2 grams of chalk which affects the water profiles as follows:

Profile
Ca
Mg
Na
SO4
Cl
HCO3
Dallas, TX
24.0
3.0
17.0
44.0
34.0
45.0
Munich, Germany
75.0
20.0
10.0
10.0
2.0
200.0
Adjusted
89.2
3.0
35.1
44.0
34.0
190.5
Difference
14.2
-17.0
25.1
34.0
32.0
-9.5

The Excel Solver worksheet for these same source and target profiles and zero dilution recommend 5.20 grams of epsom salt, 4.08 grams of baking soda, and 4.83 grams of chalk which affects the water profiles as follows:

Profile
Ca
Mg
Na
SO4
Cl
HCO3
Dallas, TX
24.0
3.0
17.0
44.0
34.0
45.0
Munich, Germany
75.0
20.0
10.0
10.0
2.0
200.0
Adjusted
75.1
16.5
46.5
97.5
34.0
199.9
Difference
0.1
-3.5
36.5
87.5
32.0
-0.1

Obviously these are two different ways of trying to minimize the total difference in a series of fairly sophisticated and inter-related equations, but I thought I'd point out the rather notable differences for anyone coming from one of the other water chemistry optimizers.  Brad, do you happen to know why the results are as different as they are?  Is it because your placing a higher emphasis on minimizing the difference in sulfate vs. some of the other components?  I know that the way the Excel Solver library is used, each component/addition has a weighted emphasis based on the author's perceived importance.  I wonder if it might be possible to expose some type of tuning variables for your optimizer (or even pre-canned "profiles") that would let the user choose the relative importance of each component.

Anyway, just an observation and some thoughts here...

Offline BeerSmith

  • Brewer, Author, Patriot
  • BeerSmith Administrator
  • BeerSmith Grandmaster Brewer
  • *****
  • Posts: 5570
  • BeerSmith - take the guesswork out of brewing!
    • BeerSmith
Re: Potential issue with Water Profile Tool - Calculate Best Additions
« Reply #1 on: June 13, 2011, 02:35:21 PM »
Hi,
  In a nutshell - my solver does a non-negative least squares best fit I believe.  As you point out there are many other algorithms possible for finding the minima on a multivariate equation.

Brad
Get a free trial of BeerSmith 3 here