Water: A New Brewing Water Spreadsheet

Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum

Help Support Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum:

This site may earn a commission from merchant affiliate links, including eBay, Amazon, and others.

Big Monk

Trappist Please! 🍷
Joined
Dec 24, 2015
Messages
2,192
Reaction score
1,151
Here is the link to the blog post and files:

http://www.********************/uncategorized/water-a-new-water-chemistry-spreadsheet/
 
I'll check it out scotty and compare to Bru'n predictions. Any testing feedback so far?


In our trials at least, we are closer to predictions than other software. Using the Extech pH110 meter with 0.01 resolution and accuracy we are within 0.01 of estimates consistently. As AJ has noted though, we do not take buffer tolerance into consideration, so there is potential for a small accuracy hit there, but that would go for anyone using estimations in any software, with any meter.
 
Thanks I read some of the AHA thread, looks like it's using the same color formulas for specialty malts but relying on base malt DI ph at a known ratio which is interesting. I'll have to check this out, we might have a new contender for mash ph!
 
I have it up now, I'll be playing with my upcoming brew to see just how it compares to the other programs I have been playing with/learning. This whole water adjustment part of brewing is new to me, so its been interesting to see all of the work put into these sheets.
 
Thanks for sharing scotty! I've been looking for an alternative to Bru'N since it does not take into account the added mineralization from low oxygen chemical contributions, and I've not been overly happy with the pH predictions from Bru'N I've been seeing lately with my low oxy additions. Immediately, just from running the numbers, I can see that this spreadsheet is suggesting acid amount adjustments in the correct direction (i.e. more) than when Bru'N has been estimating, which should put me closer to my desired pH. I'll post back when I've brewed up a couple using your spreadsheet. Again, thanks for sharing!
 
Thanks for sharing scotty! I've been looking for an alternative to Bru'N since it does not take into account the added mineralization from low oxygen chemical contributions, and I've not been overly happy with the pH predictions from Bru'N I've been seeing lately with my low oxy additions. Immediately, just from running the numbers, I can see that this spreadsheet is suggesting acid amount adjustments in the correct direction (i.e. more) than when Bru'N has been estimating, which should put me closer to my desired pH. I'll post back when I've brewed up a couple using your spreadsheet. Again, thanks for sharing!


Awesome. Please, by all means keep feeding me comments. I try and incorporate as expeditiously as possible.

My only desire is to not overly complicate the sheet. I want to leave the feature set as it is and just strengthen it. I will not, however, turn down any good suggestions for content.
 
Awesome. Please, by all means keep feeding me comments. I try and incorporate as expeditiously as possible.

My only desire is to not overly complicate the sheet. I want to leave the feature set as it is and just strengthen it. I will not, however, turn down any good suggestions for content.

One good thing for beginners (like me) might be to incorporate some quick notes.

ex.. when looking at the water profile, if you scroll over Ca, Mg, Na, SO4 etc, I think it would be helpful if it loaded some kind of quick briefing of what that mineral adjusts.
 
Boil-off rate expressed as percent/hr? No... please modify to support a flat gal/hr value. Otherwise we always have to do math for each specific batch using the pre-boil volume.

And Water-to-Grain ratio... need the option for "all" so BIAB full volume mashers don't have to calculate it every time.
 
Boil-off rate expressed as percent/hr? No... please modify to support a flat gal/hr value. Otherwise we always have to do math for each specific batch using the pre-boil volume.

And Water-to-Grain ratio... need the option for "all" so BIAB full volume mashers don't have to calculate it every time.


Percent p/hr is what I use but since this is for public consumption I can change that easily.

As far as WtG I'll make it a calculated value based on a strike water user input.

I'm revising tomorrow.
 
One good thing for beginners (like me) might be to incorporate some quick notes.



ex.. when looking at the water profile, if you scroll over Ca, Mg, Na, SO4 etc, I think it would be helpful if it loaded some kind of quick briefing of what that mineral adjusts.


Check the pH reduction tab. The mineral table is commented.
 
two quick observations - rye malt cannot be inputted from drop down
biab difficult to input as no calcs for full volume mash
 
two quick observations - rye malt cannot be inputted from drop down
biab difficult to input as no calcs for full volume mash


I can add rye malt for sure.

As far as full volume mash, I'm confused, but willing to accommodate as stated above. I'll make WTG a calculated value and have strike water be a user input.

Why wouldn't you just increase WTG until you hit your desired volume? I brew no sparge and that's my MO.
 
Percent p/hr is what I use but since this is for public consumption I can change that easily.

I spent a few minutes, unprotected the sheet, and took care of it on my own downloaded copy. Thanks for posting this! I look forward to checking it out. I like how it scales so easily when you modify the starting volume.
 
Thanks Scotty - I am a spreadsheet dumbo so bear with me. Will try the WTG but like strike input more.

BTW - not a MS guy so using Libre Calc (open source) rather than Behemoth Excel
 
Biscuit, Special Roast, Aromatic, Victory, Pale Chocolate, and Melanoidin are "Cara" malt types? Most are roasted malts. Or is this just an idiosyncrasy of your formula?

I'm having some trouble with the pH Reduction Data tab/Grain Acidity Table, Result column not picking up the matching pH reduction amount. Only the base grains are registering as non-zero; the specialty malts are all zero. So the calculated mash pH on the main tab stays incorrectly high. I'm using MS Excel for Mac.
 
Biscuit, Special Roast, Aromatic, Victory, Pale Chocolate, and Melanoidin are "Cara" malt types? Most are roasted malts. Or is this just an idiosyncrasy of your formula?

I'm having some trouble with the pH Reduction Data tab/Grain Acidity Table, Result column not picking up the matching pH reduction amount. Only the base grains are registering as non-zero; the specialty malts are all zero. So the calculated mash pH on the main tab stays incorrectly high. I'm using MS Excel for Mac.


Those are all cara Malts for the purpose of pH estimation.

I am working on a fix for the table. Someone at the AHA pointed it out too. I'm revising in the morning.
 
Thanks Scotty - I am a spreadsheet dumbo so bear with me. Will try the WTG but like strike input more.

BTW - not a MS guy so using Libre Calc (open source) rather than Behemoth Excel


I need to install a big fix to help you open source guys. I'll be doing that tomorrow.
 
I spent a few minutes, unprotected the sheet, and took care of it on my own downloaded copy. Thanks for posting this! I look forward to checking it out. I like how it scales so easily when you modify the starting volume.

I added it to the updated sheet shown below.

I'm having some trouble with the pH Reduction Data tab/Grain Acidity Table, Result column not picking up the matching pH reduction amount. Only the base grains are registering as non-zero; the specialty malts are all zero. So the calculated mash pH on the main tab stays incorrectly high. I'm using MS Excel for Mac.

I fixed this issue and it should be working OK now.

Thanks Scotty - I am a spreadsheet dumbo so bear with me. Will try the WTG but like strike input more.

BTW - not a MS guy so using Libre Calc (open source) rather than Behemoth Excel

I took care of the WtG and Strike issue. I also made some edits to the Data Validation and sheet links that should solve an libre office/open source office issues.

two quick observations - rye malt cannot be inputted from drop down
biab difficult to input as no calcs for full volume mash

Rye malt and flaked rye, as well as some other flaked adjuncts have been added.

Boil-off rate expressed as percent/hr? No... please modify to support a flat gal/hr value. Otherwise we always have to do math for each specific batch using the pre-boil volume.

And Water-to-Grain ratio... need the option for "all" so BIAB full volume mashers don't have to calculate it every time.

%/hr is now a calculated value based off of the new gal/hr user input. Strike water is now a user input but WtG ratio is still calculated for reference.

Here it is:

http://www.********************/uncategorized/water-a-new-water-chemistry-spreadsheet/
 
Is there a way to input a fourth base grain? Example .. 2 Row, Dark Munich, Rye Malt and Flaked Rye all have base grain calcs, but there is a max of 3 base grain entries. Thanks in advance
 
Is there a way to input a fourth base grain? Example .. 2 Row, Dark Munich, Rye Malt and Flaked Rye all have base grain calcs, but there is a max of 3 base grain entries. Thanks in advance


Redownload the sheet. I added an extra field for a 4th base malt.
 
Using the Extech pH110 meter with 0.01 resolution and accuracy we are within 0.01 of estimates consistently. As AJ has noted though, we do not take buffer tolerance into consideration, so there is potential for a small accuracy hit there, but that would go for anyone using estimations in any software, with any meter.

I fear you are still missing the point. I use the buffers merely as a means of making it quite clear that even if you got everything else perfect you would be unable to realized ±0.01 accuracy because the buffers are not that good.

The main sources of error in programs such as yours is error on modeling the malts' titratable acidity. This is

A = a*(pH - pHDI) + b*(pH - pHDI)^2 + c*(pH - pHDI)^3

To get a correct value for acidity, upon which mash pH prediction rests, you must have correct values for a, b and c and especially for pHDI. A bit of analysis (set out in another thread) shows that the most important of these is pHDI if b and c are small so that what you are doing here, giving the user the opportunity to enter the actual DI pH can be significant if he can get the actual DI mash pH. But he can't unless he measures it himself. The best he can do otherwise is accept a number measured by the maltster if the maltster measures it and puts the number in his spec sheet. If we want to bring in buffers here we have to point out that the maltster will take his measurements with ±0.02 buffers and you, in checking on the performance of this spreadsheet, will use ±0.02 buffers so that if the spreadsheet is otherwise perfect you will have rms error of ±sqrt(2)*0.02 = ±0.028. And the spreadsheet isn't otherwise perfect. It has to guess at a, b and c. If it is like most it will assume b = c = 0 which they aren't. More error.

Now this particular spreadsheet may in fact turn out to be better than the others because it gives one the opportunity to measure and enter actual DI malt mash pH but it is extremely disingenuous to suggest that it has accuracy approaching ±0.01 under any circumstances.
 
What AJ said in regards to the ±0.028 is unavoidable instrumental error, and will be present no matter what the spreadsheet predicts, or what is measured.

Now even if you have 500 ph predictions, and 500 ph measurements, and they all lie within ±0.01, that instrumental error is still present.

RPIScotty seems to be using experimental error, which is the accuracy and precision between two experimental measurements (or a true measurement and an experimental measurement).
 
We OBSERVE, consistently, pH measurements taken with a properly maintained and calibrate Extech pH110.

The observations show, across a relatively small data set of ~50 brewing sessions, that we MEASURE, with the same meter values that fall in a range of +\- 0.01.

AJ has repeatedly pointed out that this just isn't possible. It may be true that when you take into account the fact that one may only have the maltsters DI pH (which he did not measure), an error of +\- 0.02 from calibration buffers, as well as instrument error, that the actual measured values are skewed. That's fine. I've accepted that and I NO LONGER advertise accuracy of +\- 0.01.

All of this great discussion does little to change the fact that WE have OBSERVED such a range in OUR empirical measurements. This includes properly maintaining and calibrating the meter per OEM specifications, taking measurements 25-30 minutes into the mash, using Low alkalinity water, paler grist, etc.

I respect you very much AJ, and I am appreciative of your work and cite it extensively. I am by no means trying to lock horns with you and have backed off accuracy claims since we began corresponding.

At the end of the day I feel this spreadsheet will be beneficial. Is it perfect? No. You've said it yourself: until maltsters give a greater range of acidity values (if they ever do), we are stuck with less than perfect calculations. I think this is a step in the right direction.
 
I certainly am not looking for an argument here but it is pretty clear that you are missing something as it is not possible to attain a sequence of measurements that are consistently better than the capabilities of the measurement system and yet you have done that. As an engineer this should be very disquieting to you and you should be trying to find out why it is you are getting impossible results.

Somewhere in engine school you should have been introduced to the concept of a 'Error Budget' which attempts to quantify how much error is contributed by each of the components of a system to its overall accuracy. This isn't the place to go over the whole nine yards but perhaps a couple of comments will trigger some memory.

You've got two major components here. First is a pH value predicted by your algorithm and the second is a measured pH. Call them pP and pM. If you run the algorithm against a mash and measure its pH you will find a difference between the two and this is the error for that run
E = pM - pP. Obviously in pM and pP are identical the prediction was perfect and the measurement was perfect and E = 0. But neither is perfect. pM = pMt + Em where pMt is the measured pH if the measurement is perfect and Em the measurement error. Also pP = pPt + Ep where pPt is the correct predicted pH and Ep is the prediction error. Clearly pPt is numerically equal to pMt and so when one differnces a predicted and measured pH the difference is E = Ep - Em. Can Ep ever equal Em. Yes, that is possible but the probability of it happening is very small.

Clearly to assess the value of this (or any) spreadsheet we can't rely on one run. We must make many and publish statistics on E. The world will expect you to publish the rms value which is the square root of the average square of E. As for a single run we have E^2 = Ep^2 + Em^2 + 2Ep*Em the rms error will be Erms = sqrt(Ep_^2 + Em_^2). Ep_ and Em_ represent, respectively, the average rms errors in prediction and measurement. The cross term is gone because Ep and Em are both assumed to be 0 on average. If they aren't your measurements are biased (and you will fix that) or your algorithm is biased (and you need to fix that).

With careful study of pH measurement we determine that, with the best equipment and practices, we can expect at mash pH (and the accuracy we can get does, in fact, depend on the pH range being measured relative to the buffer pH) that we can get slightly better than the buffer tolerance which is, for most of us ±0.02. Calling it ±0.02 allows a bit for imperfect practice and the fact that most of us are not using meters that cost $1000 sitting on a lab bench.

That means that Erms = sqrt( (0.02)^2 + Ep_^2) ) > 0.02. Thus the lower bound on measured errors in attempting to asses the accuracy of your (or any spreadsheet) is 0.02 pH rms.
You consistently get measurements better than the lower bound so something is wrong. I don't know what it is but you need to find out.

At this point we have shown that there is a measurement problem but have not addressed accuracy issues with the pH estimation algorithm itself which will give us a WAG at Ep_. To do this we note that the pH shift from an acid addition A is dpH = A/B where B is the buffering capacity of the mash. The buffering capacity of the mash is m1*(a1*D1 + b1*D1^2 + c1*D1^3) + m2*(a2*D2 + b2*D2^2 + c2*D2^3) + ... in which m1 is the mass of the first malt, a1, b1, c1 its titration coefficients and D the difference between its DI mash pH and the target pH. This is clearly getting out of hand fast so I won't give more detail but will note that the rms error in estimated pH from this addition alone is
m1*(Ea1*D1 + Eb1*D1^2 + Ec1*D1^3 + a1*ED1 + 2*b1*ED1^2 + 3*c1*ED2^3) + m2*(...)
in which Ea1 is the error in knowledge of a1 etc and ED1 is the error in knowledge of the DI mash pH because D = pHz - pHdi where pHz is the target pH. As before we will square this term and take the square root with the result that the rms error is going to be

sqrt(m1^2*Ea1_^2*D1^2 + ... m1^2*a1*ED1_^2 +....)

In addition to errors introduced by all these malt factors we need to recognize that if you think you are mashing 10 lbs of malt and you are actually mashing 10.1 lbs that's going to effect the pH estimate too.

There's not much point in doing more algebra here. This should be enough to make it clear where the errors are coming from. What we can do at this point is put together a spreadsheet that predicts mash pH perfectly accurately if we give it perfect data and the a, b, c model is perfect, induce small errors in the various parameters and see how big and error in pH prediction it makes for a particular grist. After doing a little of this it's pretty clear that the DI mash pH for the malts is a big contributor to estimated pH error. For a typical pale malt mash of 90% base and 10% 20L grains the pH estimation error from this component is about equal to the DI pH error. Another big contributor would be the combined error of measurement and knowledge of strength of an acid addition. A 5% error in those would lead to about .01 error in the estimate. There are dozzens of other errors from, for example, not including b and c or having the wrong value for a. A ten percent error in knowledge of a leads to about 0.005 rms error in estimated pH.

Now let's suppose we have the best possible knowledge of DI mash pH for the base malt. That would be the DI mash pH we measured ourselves. Since we know that we can't measure pH to better than ±0.02 we put that into my spreadsheet and find that it induces about 0.0175 error in the pH estimate. With some other findings:
Due to error of ±0.02 in measurement of base malt DI pH 0.0175
Due to 10% error in a1 0.005
Error due to ignoring b1 0.001
Error due to ignoring c1 0.006
5% error in strength/measurement of lactic acid 0.011

There are lots of other errors but they are all going to be small relative to 0.0175. The rms of these listed errors is
sqrt(0.0175^2 + 0.005^2 + 0.001^2 + 0.006^2 + 0.011^2) = 0.022119
clearly dominated by the error in measurement of the base malt DI mash pH. Yes, they do add up but the DI mash pH is ruling and that is why allowing the brewer to add a measured DI mash pH is such a plus here.

Now if we go back to the original Erms = sqrt( (0.02)^2 + Ep_^2) ) > 0.02 we can put in 0.022 for the prediction rms and have Erms = sqrt( (0.02)^2 + (0.022)^2 ) = 0.029 as an estimate of what we could reasonably expect from the experiments you are doing.

So clearly if you get 0.01 as an rms error it is not to be believed. Clearly something is rotten in the state of Denmark and you should try to find out what it is. I hope the above rambling is helpful. Nonetheless the concept is demonstrably sound to the point where you should release it to people and let them see how well the predictions compare to their measurements. They won't get ±0.01 but they should get better than they do with the other spreadsheets that estimate DI pH from color.
 
Maybe it's best to dispense completely with comments on accuracy.

I'll say that we have had great results using this spreadsheet. I urge others to try it. We find we come close to our estimates using the measuring tools we have. I make no claims about the accuracy other than our actuals are very consistent.

Try it and report back!
 
Yes. What you get and what others get is going to be different. Emphasize that you have driven down a major error source and let the users see how much improvement that makes for them.
 
Yes. What you get and what others get is going to be different. Emphasize that you have driven down a major error source and let the users see how much improvement that makes for them.


Will do. Thanks for all the great information AJ.
 
@RPIScotty, modifying the boil time or boil-off rate changes the estimated mash pH... that's very odd. And I can see it's doing that because adjusting those boil values changes the mineral gram values.

The g/gal mineral additions should all be relative to the strike water; they shouldn't have anything to do with the boil volumes.

Am I misunderstanding something?
 
@RPIScotty, modifying the boil time or boil-off rate changes the estimated mash pH... that's very odd. And I can see it's doing that because adjusting those boil values changes the mineral gram values.

The g/gal mineral additions should all be relative to the strike water; they shouldn't have anything to do with the boil volumes.

Am I misunderstanding something?


I'll take a look. That is odd.
 
It's because of the way you're calculating the raw gram weights in B31:F31 based on the grams/gallon values in B30:F30. I believe you should be using gallons = strike + sparge. But instead you are subtracting the boil-off amount from that, or gallons = strike + sparge - boil-off. This is the case for all of the mash mineral cells.

For mash acid additions, you are using only gallons = strike. So there is some inconsistency in these formulae that you might want to take a look at.
 
It's because of the way you're calculating the raw gram weights in B31:F31 based on the grams/gallon values in B30:F30. I believe you should be using gallons = strike + sparge. But instead you are subtracting the boil-off amount from that, or gallons = strike + sparge - boil-off. This is the case for all of the mash mineral cells.

For mash acid additions, you are using only gallons = strike. So there is some inconsistency in these formulae that you might want to take a look at.


Thank you for the heads up. I'll take a look Monday morning and clear it up.

One thing to understand is that I revamped our typical calculations for this sheet and expanded them greatly to give people the required set of inputs.

If you guys stick with me on this, I'll work out the kinks for sure.
 
Still not working in Linux using LibreOffice 5.2.4 for me. If others are using this combination please let me know if it is working for you. Perhaps I'm just doing something very incorrectly.
 
Still not working in Linux using LibreOffice 5.2.4 for me. If others are using this combination please let me know if it is working for you. Perhaps I'm just doing something very incorrectly.


What issues are still occurring?
 
Back
Top