Bru'n Water Spreadsheet question

[rkhanso]

Hello,
I'm trying to figure out how to get minerals correct to the recipe for Yoopers Oatmeal Stout recipe. I doubled the recipe for a 11 gallon batch.

I entered Yoopers water numbers as a custom water profile. Before I add anything to the water, the pH is in the correct range. Whenever I add anything to the water, the pH number goes down and gets out of range. How can I correct this?

Or, should I just not use the custom water profile that Yooper posted on her info? But, I'm not sure if doing that will make any difference since the pH will still go down whenever I add any water salts, etc.

I do electric BIAB. No Sparge. Start with RO water since my city water sucks.

Here are some screenshots:

 

[sixhotdogneck]

In the "Percent Dilution" cell enter "100" to indicate that you're using 100% RO water.

Add minerals to match the profile, then add some Sodium BiCarbonate (baking soda) to bring the pH back up.

(As an aside, in this day and age, the water calculators should just solve the problem with readily available algorithms and present the end user with a list of choices (amounts of minerals/acids in their unit of preference) that will satisfy the profile and the pH. Why in the name of Pete/John/Bill/Ted are people still having to fiddle with numbers to add minerals? This is unacceptable.)

 

[mabrungard]

Why in the name of Pete/John/Bill/Ted are people still having to fiddle with numbers to add minerals?

Because not everyone has Excel and its Solver capability. While that self calculating function could be incorporated, I'm impressed that its too much trouble for some people to take less than a minute to get their additions figured out. Sorry for the disappointment. There are too many brewers that don't have the resources to own Excel.

 

[rkhanso]

In the "Percent Dilution" cell enter "100" to indicate that you're using 100% RO water.

Add minerals to match the profile, then add some Sodium BiCarbonate (baking soda) to bring the pH back up.

(As an aside, in this day and age, the water calculators should just solve the problem with readily available algorithms and present the end user with a list of choices (amounts of minerals/acids in their unit of preference) that will satisfy the profile and the pH. Why in the name of Pete/John/Bill/Ted are people still having to fiddle with numbers to add minerals? This is unacceptable.)

Thanks for that info. I thought that since I was not diluting the water I was starting out with (RO water) that this field should be zero. I guess I need to read each of the cells with the red box in it...

So - this should be adequate?

 

[rkhanso]

Because not everyone has Excel and its Solver capability. While that self calculating function could be incorporated, I'm impressed that its too much trouble for some people to take less than a minute to get their additions figured out. Sorry for the disappointment. There are too many brewers that don't have the resources to own Excel.

Once I get a couple correct uses of the spreadsheet under my belt, I'm sure the questions will stop.

 

[sixhotdogneck]

Thanks for that info. I thought that since I was not diluting the water I was starting out with (RO water) that this field should be zero. I guess I need to read each of the cells with the red box in it...

So - this should be adequate?
View attachment 585437 View attachment 585438

If that's the profile and pH you're aiming for, but it doesn't match the original very well (though it's not crucial to match the original). You'll need to practice manually adding minerals to match original.

 

[sixhotdogneck]

Because not everyone has Excel and its Solver capability. While that self calculating function could be incorporated, I'm impressed that its too much trouble for some people to take less than a minute to get their additions figured out. Sorry for the disappointment. There are too many brewers that don't have the resources to own Excel.

Wrong on several points. Take the gentlemen whose thread we're overtaking, he's going to require *a lot* more than a minute to get his minerals and pH sorted, because he doesn't have the practice. Sure you can do it in a minute because you've had the time, experience and practice.

Image if all he had to do was input the recipe, profile and the desired pH and out popped a list of minerals/acid amounts and all he had to do was select one from the list.

I'm not sure the built in Solver has the ability to find solutions to multivariable problems, but if it does it would do spreadsheet implementers well to make use of it even if not all have access to Excel proper.

The final problem is spreadsheets themselves. For some reason this community revolves around "spreadsheets". It's because they're easy - easily convoluted that is . The "right" way is to develop software using a platform - .NET, Java etc... - with a GUI front end.

 

[sixhotdogneck]

Hrm... could be wrong but I don't think the built in Excel Solver can be used to output multi-variable answers (perhaps as an array?), it seems to only accept multi-variable inputs and output one answer.

For this problem you'd need to input the types of minerals and acid to consider and output a list of best fits to the target profile/pH.

I believe Genetic Algorithms can be used to obtain the answers needed.

 

[Silver_Is_Money]

There are too many brewers that don't have the resources to own Excel.

I'll never own it.

 

[sixhotdogneck]

Apparently open office has a "Solver" also.

 

[Silver_Is_Money]

Apparently open office has a "Solver" also.

But it can't solve non-linear equations, and it you add in the experimental non-linear add-on it often gives strangely erratic and/or unstable results, with such being why LibreOffice refuses to add this module to their standard package.

That said, I added it, and LibreOffice barfs on the VBA code before it can even invoke Solver.

 

[sixhotdogneck]

But it can't solve non-linear equations, and it you add in the experimental non-linear add-on it often gives strangely erratic and/or unstable results, with such being why LibreOffice refuses to add this module to their standard package.

Well yes, but eventually these will be out of beta and should operate sufficiently for the purpose of a brewing water spreadsheet.

 

[Silver_Is_Money]

Well yes, but eventually these will be out of beta and should operate sufficiently for the purpose of a brewing water spreadsheet.

This module has been available for many years. I'm not going to hold my breath.

I haven't even been able to assess if the non-linear Solver in LibreOffice gives decent results, since each time i've attempted to run one of the VBA coded macros I've ended up stuck in an endless loop of error messages and having to kill LibreOffice in order to get it to stop and close.

 

[rkhanso]

If that's the profile and pH you're aiming for, but it doesn't match the original very well (though it's not crucial to match the original). You'll need to practice manually adding minerals to match original.

Isn't only the Chloride and bicarbonate the only two items that are far off? Well, I guess Sodium is also a bit off.
You're saying I can keep futzing with these numbers and get a better balance overall to Yoopers numbers?
I did up the Baking soda a bit more to get the pH up to 5.46.

I checked the Adjutment Summary tab....
Is 10.2g of MgCl too much to add to 14.5 gallons of mash/starting RO water for a BIAB Oatmeal Stout?

 

[Silver_Is_Money]

Isn't only the Chloride and bicarbonate the only two items that are far off? Well, I guess Sodium is also a bit off.
You're saying I can keep futzing with these numbers and get a better balance overall to Yoopers numbers?
I did up the Baking soda a bit more to get the pH up to 5.46.

I checked the Adjutment Summary tab....
Is 10.2g of MgCl too much to add to 14.5 gallons of mash/starting RO water for a BIAB Oatmeal Stout?

With (in some cases) the exception of alkalinity, trying to hit mystical water analytical numbers as if they hold the key to brewing magic is a fruitless exercise (since they don't). Getting in the ballpark is all that's required.

An analogy might be that accolades aplenty awaited the first guy to lap the Indianapolis Motor Speedway at the 100 and later the 200 mph markers, while the guys who went 99 and 199 mph are not even memories. But was there truly anything spectacular about such trivial improvements other than bragging rights?

And a brewing analogy is that people slave over their IBU's to hit exactly the recipe required amount of bitterness, while totally unaware that the error bars are on the order of +/- 35% for IBU's, so in reality slaving over IBU numbers to the Nth decimal point is rather foolish. For example, when you are shooting for 60 IBU's, you will likely hit somewhere between 44 and 80, (with the general likelihood favoring the lower side) so does it really make a difference if you settle for 62 or if you fiddle around for 15 more minutes with the software until you hit 60? Especially when no two IBU calculators seem to yield the same results anyway.

 

[sixhotdogneck]

Forget about the magnesium and sodium, concentrate on the sulfate, chloride, calcium (CaCl, CaSO4) and the alkalinity (baking soda or pickling lime).

My earlier point, (probably not well communicated) was that it's going to be difficult to match that profile by hand.

Something simple like:

.31g/gal CaSO4
.37g/gal CaCl
.6g/gal baking soda

 

[rkhanso]

I see there are many opinions on water additions and levels.

 

[sixhotdogneck]

I see there are many opinions on water additions and levels.

Not really, everyone is suggesting that you simplify and not try to match the profile exactly. See #16. It would take a computer program some time to match it exactly so don't beat yourself up about it. Your beer will be fine. Live long and prosper.

 

[Silver_Is_Money]

Water profiles which were originally derived from natural sourced water with its own inherent mineralization are going to prove nigh on impossible to duplicate with a handful of minerals and RO water. And blended city water averages are impossible to duplicate, because averages don't cation/anion balance to begin with.

Did you check the magical Yooper water for cation/anion balance to see if it even has the potential to be duplicated within the real word that we live in?

 

[rkhanso]

Did you check the magical Yooper water for cation/anion balance to see if it even has the potential to be duplicated within the real word that we live in?

Nope - don't have a clue what you're talking about.
I'm just going to go with what I have now.

 

[Silver_Is_Money]

Nope - don't have a clue what you're talking about.
I'm just going to go with what I have now.

Bru'n Water tells you if your cations and anions are in balance.

 

[rkhanso]

I put her numbers in Bru'n Water and found it isn't balanced. All the mineral numbers were not listed in the recipe.
Just:
Water was
Ca: 84
Mg: 26
Na 9
SO4 45
Cl 62
HCO3 228

But, should I even care? Is it close enough? She simply said the beer was fantastic and I wanted to brew as close to her profile and recipe to also make the same fantastic beer.

 

[Silver_Is_Money]

I can't read the cation and anion numbers. But if it doesn't balance (within reason at least), it isn't real. I forgot, but does Bru'n Water turn cells green when the balance is considered acceptable?

 

[Silver_Is_Money]

But, should I even care? Is it close enough? She simply said the beer was fantastic and I wanted to brew as close to her profile and recipe to also make the same fantastic beer.

View attachment 585477

Yooper's meticulous procedure likely has well more to do with the outcome than the water does.

 

[mabrungard]

Not really, everyone is suggesting that you simplify and not try to match the profile exactly. See #16. It would take a computer program some time to match it exactly so don't beat yourself up about it. Your beer will be fine. Live long and prosper.

Exactly! If your result is somewhere in the ballpark with respect to the various concentrations in the targeted profile, its good enough. I generally only get within 5 to 10 ppm for most ions. By that point, the mineral addition amount is getting to the point that I can't measure it fine enough.

Don't chase your additions down to zero-error. That is a waste of time.

 

[ajdelange]

A lot has flown by here in a couple of hours. I've been using Solver to find salt additions that best match a desired profile (which is a non linear problem) for over 10 years. To set up a problem in Solver one invokes it from a menu which brings up a form into which you enter instructions to do something to some cell by varying some other cells subject to constraints ton the values of other cells. To find a best matching salt additions set I tell it to minimize the weighted sum of [p(Ion concentration wanted) - p(Ion concentrated realized)]^2 at the pH I want (the rest of my spreadsheet calculates all that) subject to the constraint that the total charge is neutral. Thus I can use sulfuric acid as a potential source of sulfate without having to add calcium but of course it will call for some base, such as NaOH to neutralize the acid unless I am willing to accept lower pH. Which in the 0 Alkalinity method I might be willing to do. The p operator is the same old -log(•) operator we use in solving water problems all the time. The reason for the use of the p operator is to convert the errors to percentages. Thus we might hope to have less than 5% error in each ion's concentration which makes more sense than trying to get the error down to 5 mg each when you are dealing with sodium at the 20 mg level and sulfate at 200. But of course you can minimize on the basis of straight mg differences too if you want.

Now the Solver is INCREDIBLY powerful but it is a bit of a PITA to set up. Given its power it seems that the small time and effort it takes to set up is worth it. But not for doing water profiles. They are pretty much useless so I never use this feature unless answering a question here or somewhere else. Where it is worth it is when trying to estimate pH. One can easily compute the net proton deficit on a mash as a function of pH in a spreadsheet but as it is not a linear function of pH something like the Solver is required to do that and that's where I used it most. But it is still a bit of a PITA to set up each time you want to see what difference adding another 2% Biscuit would make. And it's incredibly over powered for this near linear problem so it would be nice if it could be made to go away. Another thing I've used it for is to find the 3 parameters that best fit a laboratory malt titration. You need three parameters because malt titration curves are not linear but believe it or not finding those three parameters is a linear problem. You do not need the power of SOLVER to solve it. And its a pain, though not that great a pain, to set it up.

So it would be nice to get rid of the SOLVER if we can and we can. I have done it for the mash pH estimation problem (nothing more complicated than Newton's method which you learned in High School) and the coefficient determination problem (a bit tougher - had to use Moore-Penrose) by writing VBA macros. In my prototype spreadsheet a change to any malt quantity, malt choice, water alkalinity, water volume, acid addition, calcium or magnesium salt addition etc. results in an instantaneous update of the displayed mash pH estimate without the need to invoke SOLVER. The same is true when computing water blends. The pH of the blend is instantly updated upon changing the relative proportions of the two sources or any of their properties.

Now I have not applied it to the problem of salt additions to match a desired ion profile. Since I consider the whole profile thing more or less of a joke I have to confess that my interest is purely one of "Can I do it?". There is a general approach to such problems (minimizing the norm of a residual vector) used to do things like estimate orbital parameters from observations of satellite range over time and I'm hoping that just setting pH as another parameter to be compared to a target might work. It's actually nothing more than mulidimensional Newton-Raphson but those of you familiar with Newto-Raphson know that if the error function is ill behaved it falls flat.

Don't have the "resources" to afford Excel? This is a joke, right? Drink 10 fewer beers in the pub this year and brew 10 pints more and you've got it covered. To those who think they are punishing Bill Gates by denying themselves something that could really help them a lot I don't know what to say. I've known Jewish families that would not buy German products but I can't imagine that Bill Gates has done anything to anyone on this forum that remotely compares to what the Nazis did to the Jews.

 

[sixhotdogneck]

A lot has flown by here in a couple of hours. I've been using Solver to find salt additions that best match a desired profile (which is a non linear problem) for over 10 years. To set up a problem in Solver one invokes it from a menu which brings up a form into which you enter instructions to do something to some cell by varying some other cells subject to constraints ton the values of other cells. To find a best matching salt additions set I tell it to minimize the weighted sum of [p(Ion concentration wanted) - p(Ion concentrated realized)]^2 at the pH I want (the rest of my spreadsheet calculates all that) subject to the constraint that the total charge is neutral. Thus I can use sulfuric acid as a potential source of sulfate without having to add calcium but of course it will call for some base, such as NaOH to neutralize the acid unless I am willing to accept lower pH. Which in the 0 Alkalinity method I might be willing to do. The p operator is the same old -log(•) operator we use in solving water problems all the time. The reason for the use of the p operator is to convert the errors to percentages. Thus we might hope to have less than 5% error in each ion's concentration which makes more sense than trying to get the error down to 5 mg each when you are dealing with sodium at the 20 mg level and sulfate at 200. But of course you can minimize on the basis of straight mg differences too if you want.

Now the Solver is INCREDIBLY powerful but it is a bit of a PITA to set up. Given its power it seems that the small time and effort it takes to set up is worth it. But not for doing water profiles. They are pretty much useless so I never use this feature unless answering a question here or somewhere else. Where it is worth it is when trying to estimate pH. One can easily compute the net proton deficit on a mash as a function of pH in a spreadsheet but as it is not a linear function of pH something like the Solver is required to do that and that's where I used it most. But it is still a bit of a PITA to set up each time you want to see what difference adding another 2% Biscuit would make. And it's incredibly over powered for this near linear problem so it would be nice if it could be made to go away. Another thing I've used it for is to find the 3 parameters that best fit a laboratory malt titration. You need three parameters because malt titration curves are not linear but believe it or not finding those three parameters is a linear problem. You do not need the power of SOLVER to solve it. And its a pain, though not that great a pain, to set it up.

So it would be nice to get rid of the SOLVER if we can and we can. I have done it for the mash pH estimation problem (nothing more complicated than Newton's method which you learned in High School) and the coefficient determination problem (a bit tougher - had to use Moore-Penrose) by writing VBA macros. In my prototype spreadsheet a change to any malt quantity, malt choice, water alkalinity, water volume, acid addition, calcium or magnesium salt addition etc. results in an instantaneous update of the displayed mash pH estimate without the need to invoke SOLVER. The same is true when computing water blends. The pH of the blend is instantly updated upon changing the relative proportions of the two sources or any of their properties.

Now I have not applied it to the problem of salt additions to match a desired ion profile. Since I consider the whole profile thing more or less of a joke I have to confess that my interest is purely one of "Can I do it?". There is a general approach to such problems (minimizing the norm of a residual vector) used to do things like estimate orbital parameters from observations of satellite range over time and I'm hoping that just setting pH as another parameter to be compared to a target might work. It's actually nothing more than mulidimensional Newton-Raphson but those of you familiar with Newto-Raphson know that if the error function is ill behaved it falls flat.

Don't have the "resources" to afford Excel? This is a joke, right? Drink 10 fewer beers in the pub this year and brew 10 pints more and you've got it covered. To those who think they are punishing Bill Gates by denying themselves something that could really help them a lot I don't know what to say. I've known Jewish families that would not buy German products but I can't imagine that Bill Gates has done anything to anyone on this forum that remotely compares to what the Nazis did to the Jews.

An all encompassing "input the recipe/desired water profile/target pH" -and- "output a list of possible mineral/acid amounts that would create the target water profile and hit the target pH" - dynamic calculation is what we're after here.

I don't think Solver or even Newton/Raphson would be a good choice for that problem.

It requires modern Genetic Algorithms to search such unbounded space in a reasonable time. Newton-Raphson may help establish bounds but I'm not certain I would credit it with anything beyond that.

I'm sure anyone with a modern multi-core processor would be able to use such a program.

 

[ajdelange]

An all encompassing "input the recipe/desired water profile/target pH" -and- "output a list of possible mineral/acid amounts that would create the target water profile and hit the target pH" - dynamic calculation is what we're after here.

What we are after is a program that will allow us, given a water profile, to explore options for formulation of a grist depending on our requirements for a given brew. If we have the recipe, the target pH and the water characteristics then it is trivial to come up with an addition or subtraction of a source/sink for protons to hit that pH. You don't even need Newton for that. You can choose however you want to do it. Add an acid or base, adjust the amount of a particular malt, add another malt. I think what you may be missing is that the brewer wants to be a part of that. He wants to decide how that adjustment is to be made. That is the way in which a program will be used most of the time. In using it in that way, however, the brewer is, in making his choices, going to want to see the effect of a proposed change. Suppose he increases the sauermalz by a percent. How much does that swing the pH etc. Or he may be sitting down with a blank sheet of paper in front of him with the intention of brewing a "Continental Pilsner". He may want to use certain malts and have an idea as to what the percentage of the malts vis a vis one another should be. He also has an idea as to where the mash pH should be so he will want to see what his proposed recipe's pH is likely to be. Thus he wants quick and easy pH estimates that reflect his original choices and the consequences of his adjustments. What would be the effect on pH of an extra 5% caramel malt? Can that be made up for with a little lactic acid or sauermalz. Newton - Raphson is ideal for this problem and converges in a couple of steps.

Now someone intent on authenticity may decide that he wants to go into the grist formulation process with a water that closely resembles Pilsen's. This is, IMO, kind of silly but someone may want to do it. Thus he may want to know how much DI water to blend with his tap water and how much of certain available salts and, if he wants a particular pH, acids and bases he should add to get that water's ion profile as close as possible to some target he has. That is entirely doable by Solver and I expect by Moore-Penrose Newton- Raphson but I won't really know until I do it (or try to do it). I've been using Solver to do this problem for years.

I don't think Solver or even Newton/Raphson would be a good choice for that problem.

Solver certainly is and given the nature of the problem (that the targets are near linear monotonic functions of the variables being optimized over) is probably doable by Newton-Rapson.

It requires modern Genetic Algorithms to search such unbounded space in a reasonable time.

I think you are trying to make the problem a lot harder than it is. The space is hardly unbounded. The problem is clearly separable into two parts and the independent variables in each are not that numerous. This is not a case of looking at video from the Times Square Subway station, identifying people and searching all extant data bases to see which ones have jihad in their hearts.

Newton-Raphson may help establish bounds but I'm not certain I would credit it with anything beyond that.

I've solved some pretty tough problems with it (e.g. modeling pedestal tilt, offsets and feed taper function from power measurements on a distant moving transmitter). But I do allow that this could be the first time it will let me down.

Now I don't know much about meta heuristic algorithms and that's for sure but I really don't see how they are going to help us here. The space to be searched just isn't that large. I have thought of using simulated annealing on this problem. That has never failed to find a solution for me (but then neither has Moore-Penrose Newton). But I'm going to try Newton first.

I'm sure anyone with a modern multi-core processor would be able to use such a program.

Not so sure. Those algorithms grope for a solution and use lots and lots and lots of CPU. They too have definite limitations and don't evidently scale very well

 

[Javaslinger]

Because not everyone has Excel and its Solver capability. While that self calculating function could be incorporated, I'm impressed that its too much trouble for some people to take less than a minute to get their additions figured out. Sorry for the disappointment. There are too many brewers that don't have the resources to own Excel.

Erm? Can't this be optional for those of us who do have Excel? I don't this it's as terribly uncommon as you think.

 

[ajdelange]

If you have Excel you should be able to kluge up the Solver solution. Put the concentration of the ions you want into a set of cells. Into another set of cells put the difference between what you want and what Brun gives you. Calculate the sum of the squares of these differences. Now ask the Solver to minimize that sum of squares by changing the salt entry cells.

 

[mabrungard]

Don't have the "resources" to afford Excel? This is a joke, right?

Sadly, its not a joke. Your white privilege is showing. Through my interactions with brewers around the world, I have been made aware of that fact that not everyone has the resources that I may have. It only takes a moment to do the math on someone's minimum (or less) wage and the realities of the cost of living in some places to see that. That luxury of going out to have those 10 beers in the pub is only a dream to some.

 

[Big Monk]

Sadly, its not a joke. Your white privilege is showing. Through my interactions with brewers around the world, I have been made aware of that fact that not everyone has the resources that I may have. It only takes a moment to do the math on someone's minimum (or less) wage and the realities of the cost of living in some places to see that. That luxury of going out to have those 10 beers in the pub is only a dream to some.

White privilege? Really Martin?

Let's be real blunt: brewing your own beer is not a cost savings endeavor. I think your intelligent and adept enough to know A.J. was talking about hobby brewers being able to afford Excel, not people below the poverty line in various parts of the world, even if they do brew.

I'd wager a guess that someone who is below the poverty line AND is brewing their own beer doesn't really give a hoot about pH estimates and the minutia we talk about on a regular basis.

I think you are combining two separate ideas here. I echo A.J.'s sentiments that if you have enough discretionary income to be able to brew your own beer in the first place, AND you are aware of and interested in manipulating all the levers at your disposal to make the best beer you can, then Excel is a drop in the bucket.

I think the distinction here is the difference between the American brewing hobbyist (who should have zero trouble procuring Excel) and those who brew more out

 

[sixhotdogneck]

What we are after is a program that will allow us, given a water profile, to explore options for formulation of a grist depending on our requirements for a given brew. If we have the recipe, the target pH and the water characteristics then it is trivial to come up with an addition or subtraction of a source/sink for protons to hit that pH. You don't even need Newton for that. You can choose however you want to do it. Add an acid or base, adjust the amount of a particular malt, add another malt. I think what you may be missing is that the brewer wants to be a part of that. He wants to decide how that adjustment is to be made. That is the way in which a program will be used most of the time. In using it in that way, however, the brewer is, in making his choices, going to want to see the effect of a proposed change. Suppose he increases the sauermalz by a percent. How much does that swing the pH etc. Or he may be sitting down with a blank sheet of paper in front of him with the intention of brewing a "Continental Pilsner". He may want to use certain malts and have an idea as to what the percentage of the malts vis a vis one another should be. He also has an idea as to where the mash pH should be so he will want to see what his proposed recipe's pH is likely to be. Thus he wants quick and easy pH estimates that reflect his original choices and the consequences of his adjustments. What would be the effect on pH of an extra 5% caramel malt? Can that be made up for with a little lactic acid or sauermalz. Newton - Raphson is ideal for this problem and converges in a couple of steps.

Now someone intent on authenticity may decide that he wants to go into the grist formulation process with a water that closely resembles Pilsen's. This is, IMO, kind of silly but someone may want to do it. Thus he may want to know how much DI water to blend with his tap water and how much of certain available salts and, if he wants a particular pH, acids and bases he should add to get that water's ion profile as close as possible to some target he has. That is entirely doable by Solver and I expect by Moore-Penrose Newton- Raphson but I won't really know until I do it (or try to do it). I've been using Solver to do this problem for years.
.
.
.
Now I don't know much about meta heuristic algorithms and that's for sure but I really don't see how they are going to help us here. The space to be searched just isn't that large. I have thought of using simulated annealing on this problem. That has never failed to find a solution for me (but then neither has Moore-Penrose Newton). But I'm going to try Newton first.

Not so sure. Those algorithms grope for a solution and use lots and lots and lots of CPU. They too have definite limitations and don't evidently scale very well

The problem is the term "a solution". There are *many* different solutions to the question "how much of which salts/acids can be used to hit the target water profile and pH". Perhaps one could artificially limit the output by having the user select which salts/acids they have, but even then there would be multiple solutions.

The output should be a list of solutions that the user can select from. Newton-Raphson, Moore-Penrose, Built-in Excel Solver don't live up to this expectation.

 

[sixhotdogneck]

If you have Excel you should be able to kluge up the Solver solution. Put the concentration of the ions you want into a set of cells. Into another set of cells put the difference between what you want and what Brun gives you. Calculate the sum of the squares of these differences. Now ask the Solver to minimize that sum of squares by changing the salt entry cells.

I don't believe one can execute Solver on Martin's spreadsheet because it is locked. Even if you could, you would only arrive at one solution of a multi-faceted answer, which may very well be good enough for most (i.e. they may accept that answer) save the most discerning among us.

 

[mabrungard]

White privilege? Really Martin?

Derek, you're right. Its not white privilege, its affluence. My apologies.

But you are still kidding yourself. I assure you that there are plenty of brewers that have a hard time justifying a $100+ software purchase.

 

[sixhotdogneck]

Incorporating such a feature even though some can't use it is beneficial for several reasons:

1.) It establishes a precedent
2.) It demonstrates the feasibility of such advanced features
3.) It paves the way for better algorithms in the future
4.) It saves the end user time
5.) It raises the perceived value of the spreadsheet
6.) It's just plain damn cool

I can't see a reason not to implement it but it's not my spreadsheet.

(P.S. Well there is one reason, and that's the fact that it might be a lot of work to implement, might break certain things in the spreadsheet, etc... but welcome to the wonderful world of "software").

 

[Big Monk]

But you are still kidding yourself. I assure you that there are plenty of brewers that have a hard time justifying a $100+ software purchase.

Sean Connery's character Jimmy Malone states to Costner's Eliot Ness in "The Untouchables", "What are you willing to do?" when Ness displays the required passion for justice without the stomach for the means in which to enact it.

Hobby brewing is not cheap. Given what some people spend on hops, extract, sugars, equipment, etc. to make a single batch of 5 gallons of beer, not being willing to part with $150 for a software as infinitely malleable and vital for brewing calculations such as Excel is at best comical and at worst negligent (heavy on the hyberbole of course).

If your brewing budget is on such a shoestring that you are unwilling to set yourself up with the tools to be most successful (not just Excel but all manner of Software choices) then you may not be as committed to quality as you say you are (The Royal You/We). In that case, why not just purchase commercial beer that is made to a higher standard and is cheaper? Again, I'm talking about hobby brewers here. We are very much a community comprised, by default, of people who can afford the luxury of brewing their own beer.

It's really a question of reasonable and intelligent allocation of hobby brewing funds. People seem more than willing to part with hard earned brewing budget funds for equipment and ingredients they don't need than for software solutions that can help them infinitely more.

I think of the parallel in the guitar playing community where people are constantly wasting money on this pedal or that pedal when practicing, listening, and absorbing the basics of the instrument typically trump all.

 

[ajdelange]

The problem is the term "a solution". There are *many* different solutions to the question "how much of which salts/acids can be used to hit the target water profile and pH".

Of course. We are trying to match perhaps 7 ion's concentrations and have perhaps 10 salts, acids and bases we can use. The problem is underdetermined and there are, therefore, an infinite number of solutions.

Perhaps one could artificially limit the output by having the user select which salts/acids they have,

I think it's pretty clear that a user would want to limit the salts the program can choose from to the ones he has in hand. We also find from use of the Solver that broadening the spectrum of input salts/acids/bases given to the program may improve the quality of the match. As an example if the guy wants extra sulfate but doesn't want any more calcium or magnesium he can allow the use of sulfuric acid but that will, of course, change the pH so that the program will also call for some sodium hdroxide, potassium hydroxide or sodium carbonate to compensate for that. He could, of course, just give the program sodium hydroxide or carbonate to work with and get the same result but we want to give him the option to make those choices.

but even then there would be multiple solutions.

If he restricts the number of salts to less than or equal to the number of ions he is trying to match then there is one solution only. But in the general case he should be allowed to select how many he wants to use and there will then be cases where there will be an infinite number of solutions.

The output should be a list of solutions that the user can select from.

Why? I'm missing something here. All he really cares about is getting a solution that gives him the best match to his desired profile given the salts/acids/bases he has available.

Newton-Raphson, Moore-Penrose, Built-in Excel Solver don't live up to this expectation.

The Solver certainly lets him choose which ions he wants to match and which salts/acids/bases he wants to use with any restrictions he wants to place on the amount, for example, of any given salt. It also allows him to weight the ions under consideration in any relative importance he chooses, to chose and arithmetic or geometric error criterion etc. What more could he want? One of the challenges will be making all those choices available to the user more conveniently than the Solver does. My focus right now is on the algorithm with the intention of making a function or subroutine available that will accept an desired water properties list, a list of weights to be assigneded to each of those, a list of chosen additions, a geometric/algebraic flag and a desired pH and return a list of addition quantities. It them becomes the task of a real programmer to interface that to a pretty, functional spreadsheet presentation.

Newto-Raphson and Moore Penrose are parts of the same algorithm - not distinct algorithms. In Newton's method we find how far off we are from a target, find the slope of the target function with respect to the parameter being optimized over, divide the error by the slope and correct the value of the parameter being optimized over by that ratio. That works fine where the error function is Q and the parameter is being estimated is the single parameter pH. Here we have multiple parameters being optimized and thus the slope isn't a simple derivative - it is a matrix of derivatives. You don't divide by a matrix, you multiply by its inverse and only square matrices have inverses. So we have to use a 'pseudo inverse'. MPP (Moore-Penrose Psuedo Inverse) is one of a class having the property that when the problem is overdetermined and the equations inconsistent it picks the best solution. When the problem is undetermined as it is here, it picks the best solution with the minimum norm. Thus it does exactly what we need here.

I was able to use MPP to solve the malt coefficient parameter estimation problem because while the relevant matrix A doesn't have an inverse (it is tall - i.e. not square) A_transpose*A does. Thus Excels built in matrix inversion routine can be used to find MPP. In the ion matching problem A_transpose*A, while square, doesn't have an inverse and so MPP requires SVD to compute. Excel doesn't have SVD capability. It is available, free, as a plug in but I don't realistically expect that even the more advanced brewers would be willing to accept my add in and the Real Statistics add in. The alternative would be for me to haul out Numerical Recipes and port the FORTRAN to VBA. Doable but it's a fairly lengthy piece of code.

 

[sixhotdogneck]

Why? I'm missing something here. All he really cares about is getting a solution that gives him the best match to his desired profile given the salts/acids/bases he has available.

Maybe there's a better match with salts he doesn't have but would/could easily obtain if he knew he could use them/they were beneficial.

Many combinations of salts can result in the same profile. Same thing with acids/bases. Go all baking soda or limit Na by adding in pickling lime, get some Mg from MgCl and some from MgSO4, get some Cl from NaCl and some from CaCl, etc....

Making software that helps the end user by making suggestions *is the purpose of software*. Making software that gives a one off answer and labels it as the only answer not only limits the end user but makes the software appear "dumb".

Imagine such software were in place now, users would come to the "Brew Science" forum, post a screen shot of the list of amounts of salts/acids/bases" and ask "I have the ingredients for #1 but would like #3 what benefit does it have?" As opposed to mumbling conversations about mEq/L of some inane proton not balancing. This would relegate knowledge to a select few but such is the current state of affairs.

 

[ajdelange]

To atone for my whiteness, affluence and privilege I am starting the "Impoverished Home Brewers Widows and Orphans Fund". PM me if you'd like to send a check.