Bru'n Water Spreadsheet question

[ajdelange]

Well, for example:
Ca:50 Mg:5 Na:27 SO4:50 Cl:60 HCO3:85

There is, assuming they balance, nothing wrong with that. It is true at any pH. The problem is that a user trying to synthesize a profile will do so by adding sodium bicarbonate (at least I think that's the way it works) either as a a source of sodium or of the bicarbonate which the profile has led him to believe he needs. That approach is only correct for a synthesis at pH 8.38. It is not correct for a synthesis at pH 7 or any other pH.

As you point out, its really only the alkalinity that varies and I've elected to represent alkalinity with only bicarbonate. The other ionic concentrations are consistent.

The problem there is that the relationship between alkalinity and bicarbonate is not a constant. It depends on source water pH - not strongly, but the dependence is there. IMO that problem pales in comparison to the problem of confusion of the user who adds some lime to his RO water to be told it now has bicarbonate in it.

I believe I've mentioned this in the past; I use bicarb since its the species that predominantly exists in the range of typical mashing pH. That should be a reasonable assumption.

I guess the other non-concern I have with the bicarb not being accurate, is that alkalinity (bicarb) content is the component that I feel is the first to adjust in our quest for proper mashing pH.

Yes, but how do we dispose of alkalinity? We add acid in an amount equal to the alkalinity - not the alkalinity as measured in the lab to pH 5.4 or 5.5 but the alkalinity between the source pH and the mash pH. If we assume pH 7 is nominal for source water and look at the variation between 6.5 and 10 (probably the maximum range of source water pH's a brewer might expect to see) the variation in that alkalinity is ±5% and thus you will have error of that magnitude in the amount of acid a program that ignores source pH will recommend. I know you are firmly in the "Better is the enemy of good enough." school but I am not. It's a philosophical difference.

 

[mabrungard]

I know you are firmly in the "Better is the enemy of good enough." school but I am not. It's a philosophical difference.

Fair enough.

In the case of sparging water where the source water pH would have an effect, its in Bru'n Water. However, in the case of mashing water where the buffering of the malt is going to dominate, would source water pH really have much effect?

 

[Big Monk]

Fair enough.

In the case of sparging water where the source water pH would have an effect, its in Bru'n Water. However, in the case of mashing water where the buffering of the malt is going to dominate, would source water pH really have much effect?

The way I have been interpreting all the new and exciting information flowing through the forum in the last month or so is like so: When you have a bunch of things, in this case calculations, that each introduce only a small percent error, but when summed up add to a significant error, it's advantageous to root those out and eliminate them.

This is especially true for the majority of pH estimation software users who really are "flying blind" and putting trust in the estimations themselves.

I think A.J. pointed out when the discussion of what we are referring to as "Gen II" software came about that the strength of using tools like Excel and other software tools is their ability to quickly do calculations that would be cumbersome otherwise. From that standpoint, you may as well knockout all contributors of even small percent errors, so that the estimation as a whole doesn't suffer from their combined errors.

 

[Silver_Is_Money]

Wouldn't a multitude of random errors be far more likely to somewhat cancel each other out than to be always 100% additive.

If software is guessing the direction and magnitude that the mash pH will take for each of 5 malts in a grist, and it overestimates for 2 and underestimates for 3, the result will not be the same as for if it had underestimated (or overestimated) for all 5.

 

[sixhotdogneck]

Not sure if this is related - law of propagation of uncertainty - some pretty grizzly stuff unless you're a statistician.

 

[ajdelange]

In the case of sparging water where the source water pH would have an effect, its in Bru'n Water. However, in the case of mashing water where the buffering of the malt is going to dominate, would source water pH really have much effect?

The malts exhibit buffering in both the mashing and sparging phases. Depending on the properties of the water and the malts their relative effects on the pH of the mix will vary. In no case is this a huge effect.

As the post after yours points out there is really no reason given the better understanding of the chemistry we now have and the amazing computing power of today's desktops and capabilities built into even the humble spreadsheet to stamp out these little errors. Other than philosophical ones.

 

[ajdelange]

Wouldn't a multitude of random errors be far more likely to somewhat cancel each other out than to be always 100% additive.

The probability of them being 100% additive is 0. The probability that they will exactly cancel is also 0. This is why we use rms error when dealing with error budgets.

If software is guessing the direction and magnitude that the mash pH will take for each of 5 malts in a grist, and it overestimates for 2 and underestimates for 3, the result will not be the same as for if it had underestimated (or overestimated) for all 5.

If we had a model for each malt and knew the first model produced an error of 1, the second an error of 2, the third 3, the fourth 4 and the 5th 5 we would say that if equal amounts of the malts were analyzed using these models we would, subject to certain assumptions, often made when they really aren't justified, that the rms error would be sqrt(1^2 + 2^2 + 3^2 + 4^2 + 5^2) = 7.4162. One of the assumptions is that the individual model erros are independent of one another and random such that, for example, the error of 1 stated for the first implies that the actual error returned by it is between -1 and 1 with 68% probability, between -2 and 2 with 96% probability and between -3 and +3 with probability 99%. The combined error we would similarly interpret as meaning that an estimate base on those 5 models would be show error between - 7.42 and + 7.42 68% of the time.

See some further comments on the post after yours.

 

[ajdelange]

Not sure if this is related - law of propagation of uncertainty - some pretty grizzly stuff unless you're a statistician.

Yes it is absolutely related and the bread and butter of any engineer trying to design a measurement or estimation system and of course lots of other people too (including statisticians). For example in trying to determine the accuracy of a pH meter reading we look at the accuracy of the buffers, the electrical noise introduced by the electronics, the temperature measurement error and the "geometry" of the problem (the J matrix in the linked article) and try to figure out which are the big contributors to the overall error and go after them.

To try to put this into perspective relative to the problem of pH estimation - there are several sources of error with Gen 1 programs. One is malt models. Another is failure to consider source water pH. A third is failing to realize that acid and bases proton releasing/absorbing powers are not constants. The errors caused by these errors could be entered into an error budget. The overall error of the program would be rss'ed as in the example in my last post:

sqrt(1^2 + 2^2 + 3^2 + 4^2 + 5^2) = 7.4162

Note that
sqrt(0^2 + 2^2 + 3^2 + 4^2 + 5^2) = 7.34847

The point being that because of the sqrt of sum of squares process eliminating the smallest errors does not decrease the rms error much. Thus fixing the source water pHs problem in Brun water would not improve it much if it continues to use color based models for malt acid/base properties. Nor will a Gen II program than has eliminated the pHs problem altogether be appreciably better if it bases its malt properties model on color alone.

 

[Tribe Fan]

Wow - I didn't mean to start something like this question ended up to be.

Back to my question....
I just used a different desired profile (brown/full - hopfully OK for the Yoopers Oatmeal Stout) and think I have it close enough now. I kept futzing with the numbers by hand. I spent way more time on this than I wanted though.
I should be good here now, right?

View attachment 585711

I feel your pain.

FWIW: I input your treated water values and grain bill into MME and it told me I needed to add .3lb of acid malt to hit the 5.4 mash pH. With no acid malt it comes in at 5.48

 

[ajdelange]

I will implement a Genetic Algorithm and you can implement Moore-Penrose PseudoInverse with Singular Value Decomposition.

May the best algorithm win.

And how will we know which is best?

Actually, the reason I am posting tonight is because whilst in the shower this morning I remembered that the very first time I solved this problem (i.e. matching a target ion profile with a handful of salts) I did it with a heuristic algorithm! I had totally forgotten this. And I did it for the reason mentioned in the genetic algorithm web page linked in No. 44: I didn't have a clue as to how to solve the problem. That was quite a while ago. The thing ran in FORTRAN overnight on an Apple laptop with a black and white screen but it did give good answers. So if anyone thinks I'm poo-pooing heuristic algorithms because I don't think they will work disabuse yourself of that notion. They do work and are actually very easy to program - you are just making semi educated guesses as where to go next in the solution space. It's pretty easy to stay away from absolutely correct but absurd solutions (ones with negative salt additions) this way. The challenge I face with the MPP approach is directing the search to a correct but reasonable solutions using it. It works with GPS, it ought to work here.

 

[Silver_Is_Money]

I feel your pain.

FWIW: I input your treated water values and grain bill into MME and it told me I needed to add .3lb of acid malt to hit the 5.4 mash pH. With no acid malt it comes in at 5.48

Tribe Fan, in viewing your MME attachment it is clear that you have seriously messed up the input Lovibond colors for a number of your malt inputs into MME.

1) Maris Otter is 2.8L, and you are using 9L
2) Victory is 28L and you are using 9L
3) Acid Malt is around 3-4L and you are using 20L (though for this case it doesn't matter to the outcome)
4) I must presume that 200L for Chocolate represents something like Fawcett's Pale Chocolate, otherwise 200L is rather seriously low for most chocolate malts.

5) Also, a 50/50 blend of flaked Oats and flaked barley would be about DI_pH 5.9, but you manually over-road this with 6.55. Flaked Oats and Flaked Barley have their own individual drop-downs, so no need to conflate them and then guess at their combined DI_pH.

I'm not certain as to your intended goal here, but the old saying is 'garbage in, garbage out'.

 

[Tribe Fan]

Tribe Fan, in viewing your MME attachment it is clear that you have seriously messed up the input Lovibond colors for a number of your malt inputs into MME.

1) Maris Otter is 2.8L, and you are using 9L
2) Victory is 28L and you are using 9L
3) Acid Malt is around 3-4L and you are using 20L (though for this case it doesn't matter to the outcome)
4) I must presume that 200L for Chocolate represents something like Fawcett's Pale Chocolate, otherwise 200L is rather seriously low for most chocolate malts.

5) Also, a 50/50 blend of flaked Oats and flaked barley would be about DI_pH 5.9, but you manually over-road this with 6.55. Flaked Oats and Flaked Barley have their own individual drop-downs, so no need to conflate them and then guess at their combined DI_pH.

I'm not certain as to your intended goal here, but the old saying is 'garbage in, garbage out'.

I must have been delirious with joy from Cody Allen finishing an inning without blowing the game.

Here's the adjusted grain bill. The chocolate malt L is from the screeenshot grain bill from the OP. It says .4# of acid malt is needed to bring the mash pH to 5.4. Without is is at 5.51



Intended goal here was to just give the OP some feedback on his initial question, because you know, beer.

 

[Silver_Is_Money]

Better, but improvements can still be made:

1) Victory is a specialty malt.
2) Roast Barley is a Roasted Barley, not a Roasted Malt (there is no malting done to roast barley).
3) And for a bit of nitpicking, I still prefer to let the software determine the acid malt addition rather than adding it directly into the grain bill, as the weight/quantity calculation is more precise that way.

What OS and spreadsheet version are you using? I can see where it isn't properly handling the font types/sizes for you as it does for me and I may need to revise them to suit your spreadsheet. One of the perils of my not owning a copy of Excel perhaps.

 

[Tribe Fan]

Better, but improvements can still be made:

1) Victory is a specialty malt.
2) Roast Barley is a Roasted Barley, not a Roasted Malt (there is no malting done to roast barley).
3) And for a bit of nitpicking, I still prefer to let the software determine the acid malt addition rather than adding it directly into the grain bill, as the weight/quantity calculation is more precise that way.

What OS and spreadsheet version are you using? I can see where it isn't properly handling the font types/sizes for you as it does for me and I may need to revise them to suit your spreadsheet. One of the perils of my not owning a copy of Excel perhaps.

I'm using Google Sheets. v3.1 MME.

I wondered on the acid malt addition because it is slightly different when you put it on the actual grain bill.

 

[Silver_Is_Money]

Ah, then Google Sheets has issues with the fonts. Unless you are using a Chromebook or something like it, whereby installing software isn't possible, you might want to consider a free download and install of LibreOffice, as it will clean things up and look more presentable. I don't necessarily want to fight with modifying MME in an attempt to make it look nice in Google Sheets.

 

[rkhanso]

OK Tribe Fan and Silver Is Money (and everyone else)
I think that I have Bru'n Water figured out, me futzing with the numbers enough to add 4.4g of Gypsum, 4.4g of CaCl and 3.6g of Baking Soda for my 14.5 gallons of water before mashing to reach the near the suggested numbers. This I can understand.

But when using MME, I enter the same grain bill and you're telling me that I don't need to add any water additions and I still can get a similar mash pH? But I also thought that mashing is better or more efficient when these minerals are added and you end up with better tasting beer. Am I wrong here? Are minerals good to add, or not? Why don't we all just brew with RO water and no minerals added?

And yes, the dropdown options for the grains confused me as well - I don't know what types of grain apply to which dropdown option. But that's my lack of knowledge by being a new all-grain brewer. I'm sure I'll get there eventually.

Here is what I get with Bru'n Water and MME:


The bottom image is what MME calculated before I changed the water additions to zeros. Is it that MME says I only need to add a small amount of Baking Soda. Is that grams/gal? OR grams for the entire 14.5 gallons of water?


And in the last screenshot, telling MME to use the same additions in the same quantity as Bru'n Water - different Baking Soda amounts and I end up with different mash pH. I just figured out that MME is calculating the acids/base to add automatically, based on my pH goal?




I also noticed that when I entered the same additions to MME as I chose in Bru'n Water, the ppm numbers were very similar.
How can I change the Baking Soda amount to change the estimated pH in MME?

Since Bru'n Water has some suggestions on goals for ppm numbers, I have to say I like that better. Even though I don't know if those suggestions are correct or not. But being a new all-grain brewer, I don't have the knowledge to tell what my goals should be.

These water calculators have a tough job... Be easy enough for people like me who have no clue what Grist Buffering Capacity is or how to get that info and complex enough for people who really know what they're talking about.

 

[Silver_Is_Money]

MME uses grams per batch, and not grams per Liter or grams per Gallon. And yes, MME is forecasting acid/base amounts to be added to hit your target pH for the "batch", and not by the gallon or the liter. It tells you what is required. In MME you do not hunt and peck and guess at acids or bases in a frustrating effort to hit upon what is required to be added in order to approach your desired mash pH target. As opposed to MME, most other of such software doesn't even ask for your input as to your desired mash pH target. In effect the others are making you do all of the work, whereas MME instantly does it for you.

Mash Made Easy is not informing you to add zero minerals. It does not guide as to mineral additions at all. They are strictly your personal preference, and are to be added at your discretion. Add them for flavor. Then adjust the mash pH. All MME is saying is that your grist can mash at right close to 5.4 pH even when no minerals are present. Although others have done it, I would not personally make a beer with zero minerals, as it would likely result in a dull and characterless beer as to its flavor. Add minerals for flavor and then acids or baking soda only as needed to achieve a mash pH target. The two are mutually exclusive in my thinking (opinion). I.E., I would not personally chase a mash pH target by playing with adding or subtracting minerals. To do so alters the originally intended flavor goals, and why would you mess with flavor merely to hit a pH target?

You can alter the recommended Baking Soda addition by altering your personally selected mash pH target, or by altering your mineralization, or by altering your grist, or by altering your primary base malts DI_pH selector (via the drop down cell in lower right), or by altering the buffering (which I do not recommend, but which you may find useful for fine tuning if you find that the default value here is driving your mash pH downward strictly with regard to added Ca++ and/or Mg++ mineralization by more (or less) than you actually hard measure for such minerals via a pH meter).

 

[sixhotdogneck]

And how will we know which is best?

Actually, the reason I am posting tonight is because whilst in the shower this morning I remembered that the very first time I solved this problem (i.e. matching a target ion profile with a handful of salts) I did it with a heuristic algorithm! I had totally forgotten this. And I did it for the reason mentioned in the genetic algorithm web page linked in No. 44: I didn't have a clue as to how to solve the problem. That was quite a while ago. The thing ran in FORTRAN overnight on an Apple laptop with a black and white screen but it did give good answers. So if anyone thinks I'm poo-pooing heuristic algorithms because I don't think they will work disabuse yourself of that notion. They do work and are actually very easy to program - you are just making semi educated guesses as where to go next in the solution space. It's pretty easy to stay away from absolutely correct but absurd solutions (ones with negative salt additions) this way. The challenge I face with the MPP approach is directing the search to a correct but reasonable solutions using it. It works with GPS, it ought to work here.

I just said that more tongue in cheek than anything else.

I suppose one could come up with some criteria, though it may be rather artificial, to determine if one algorithm is "better" than another. If some of those criteria had practical application like "algorithm z finds 20x more solutions y times faster than algorithm k" or perhaps something based on the quality of the solutions found, etc...

But like I mentioned, I was just joking around more than anything.

BTW, that's a neat story about your experience with a heuristic algorithm.

 

[Tribe Fan]

The MME water profile I used already included your mineral salt additions, 5g of gypsum, 5g of cacl, 4g of baking soda to 14.5 gal of RO water at your initial profile. That's why the screenshot has 0 in the additions area.

Apologies if I added any more confusion here. Not advocating one SS over another. I use MME so I thought I would input the grain bill there to give another reference point.

As you stated, you'll get the same ppm profile from both spreadsheets. MME and BW use different calculations for the mash pH.

 

[Silver_Is_Money]

The MME water profile I used already included your mineral salt additions, 5g of gypsum, 5g of cacl, 4g of baking soda to 14.5 gal of RO water at your initial profile. That's why the screenshot has 0 in the additions area.

Apologies if I added any more confusion here. Not advocating one SS over another. I use MME so I thought I would input the grain bill there to give another reference point.

As you stated, you'll get the same ppm profile from both spreadsheets. MME and BW use different calculations for the mash pH.

What Tribe Fan is saying is that on the "Water" sheet for MME he added the ppm's for all of your added minerals, so in doing it that way (his preference) he did not also need to add them on the initial screen, as doing so would be double dipping on the mineral additions, and would double them as to ppm, and seriously mess up the mash pH forecast accordingly. To my knowledge, the same can be done with any of the available mash pH assistant software currently out there.

 

[ajdelange]

We've overlooked some pretty important things here which derive from the fact that the problem is underdetermined (more salts available than ions we wish to match). In the simplest case we only add salts - no acids or bases (including sodium bicarbonate) so the pH can't change. The problem is find x such that A*x = b where A is the matrix that tells us how many moles of chloride and how many moles of calcium come from calcium chloride (2 and 1 respectively), x is a vector of salt additions (in moles) and b is a vector of the desired ion concentrations (moles). As the rank of A is less than the number of elements in b there are an infinite number of solutions and the matrix doesn't have an inverse but we can get the particular solution with the smallest norm using the MPP call it x_p. But this solution may call for negative calcium chloride for example depending on whether b represents something achievable with the selected salts. The complete solution set is x_p + a1*v1 + a2*v2 +... where a1 and a2 etc are arbitrary coefficients and v1 and v2 etc are vectors which span the nullspace of A. In words, the complete solution set is the particular solution plus any that maps to the null space of A. SVD factors A into the product of three matrices: A = U*w*V_T and the pseudo inverse of A is A# = (A_T*A)^-1*A_T = V*w^-1*U_T so that x_p = (A_T*A)^-1*A_T*b = V*w^-1*U_T*b and it's clear the the shortest solution is easy to find given that A has be decomposed.But one of the goodies that comes free with SVD is that the basis vectors for the null space are the columns of V that correspond to the 0 singular values in w. Thus SVD gives the complete solution set in one step - the singular value decomposition of A. If number of solutions found per second is a criterion then MPP-SVD wins hands down.

But given that we have all the solutions we are in a quandary. There are an infinite number of them an infinite number of which will be valid and an infinite number invalid. x_p may not be a valid solution (but it may - it would be easy enough to test it). We need to find a valid one. More to the point, when we include the addition of acids and bases the pH changes and we want to keep the synthesis pH within bounds. We cannot simply add pH to the target vector and solve A*x = b because pH is not linearly related to the salt/acid/base contents. What we have to do, therefore, is linearize the problem about some solution x0, map it to b0 by A*x0 = b0 and then solve J*∆x = b-b0 for ∆x. Here b -b0 is the residual (error) vector which, clearly we want to minimize and the matrix A has been replaced by the Jacobian, J, matrix of partial derivatives of the ion concentrations and pH with respect to the salts/acid/bases. ∆x is found from the SVD of J as above. It is then added to x0 to give x1 = x0 + ∆x which will reduce the residuals. We can dot ∆x with a vector pointing into the portion of solution space we'd like to be in and thus control the nature of the solution we get (a particular chloride to sufate ratio, for example). Thus we are using a directed search for one of the solutions with some properties we want. Once we have that there is no point in looking for other solutions but there is nothing to stop us from doing that if we want.

With an heuristic algorithm you are doing the same thing with the difference being that you are moving around in the solution space heuristically whereas with MPP you are moving about deterministically. Thus the latter will converge to a solution faster and that will be its main advantage over an heuristic algorithm. Neither will give solutions better than the other. Both will give a many solutions as you want but there is no advantage to having more solutions.

 

[bolus14]

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.

While everyone might not be able to have Excel, I'm guessing mainly because it's not affordable. Using Google Sheets works just as well, unless there's something in this spreadsheet that requires Excel. Could also use Open Office.

I haven't played with adjusting my water because getting all the right info from my local water board hasn't been easy. But, there's alternatives to Excel that have 95% of the functionality.

 

[mabrungard]

Some Bru’n Water users tell me that LibreOffice provides good operability to Bru’n Water. Its a freebie.