Check out my water spreadsheet

[qmax]

As far as my knowledge goes, there are only a few brewing water calculators available.
EZ and Palmers are based on outdated info (water alkalinity to pH=4.3),
Bru'n Water is one step ahead, however it is closed-source, does not use malt titration models, looks very solid and professional but perhaps a bit bulky in my opinion. Also, you have to manually dial in your acid addition to hit the desired pH.
My spreadsheet is based on the most current approach to mash pH prediction described in the book "Water: A Comprehensive Guide for Brewers" by John Palmer, Colin Kaminski, and A. J. deLange.

For those who have already downloaded Q-Water, it's time to update to version 1.7 which includes an easy titration calculator for measuring malts and improving pH predictions.

I don't know how many updates I will be releasing. It will probably depend on your feedback.

Read more and download Q-Water here: https://sites.google.com/site/brewqwater/

 

[IslandLizard]

Great idea!
Looks pretty good on first sight.

I like open source. Would be nice to have a linked malt database.

 

[qmax]

The thing is I could not find any comprehensive source with malt DI pH and buffering capacities. I'd be happy if you link me to that data. On the other hand, that's why open source is great: you can readily plug in your own values.

 

[IslandLizard]

Brewer's Friend has some generic malt DI pH profiles embedded. There must be a more comprehensive some source around. Do the maltsters provide them? In your spreadsheet, reading from a table would be the easiest. And it's modifiable.

 

[qmax]

There must be a more comprehensive some source around.

I'll make sure to add more malts as soon as I find it
I don't know where on Brewer's Friend you found malt DI pH profiles, however just DI pH without buffering capacity is not enough anyway.

My spreadsheet already has the data for quite a few malts there. Specifically two different base malts, munich 10L, C20, C40, C80, Chocolate 500L, Black malt 500SRM, and Carapils. I think you can substitute Black Malt for Roasted barley or Munich 10 for Vienna, etc, especially if they are a small percentage of the grist.
Buffering capacities of three of these malts are based on the titration curves, which is a huge step forward.
Other water spreadsheets put a malt in either base, caramel, or roasted category and use its color rating to derive its DI pH and buffering capacity. This approach is bound to result in a very crude estimation.

What bothers me is that my spreadsheet predicts significantly more acid for mash acidification than Bru'n Water. For sparge water it's spot on. The difference is in malt parameters. I'd be interested to see someone with a pH meter comment on that.

 

[IslandLizard]

Brewer's Friend has the calculations in the Grist Info area, right above the Mash Report. No clue what they're based on, but most of their calcs are very good, and have been on par with Bru'n Water's.

[...] What bothers me is that my spreadsheet predicts significantly more acid for mash acidification than Bru'n Water. For sparge water it's spot on. The difference is in malt parameters. I'd be interested to see someone with a pH meter comment on that.

If you posted this in the Brew Science forum, I'm willing to bet AJ and Martin will be all over it.

 

[ajdelange]

These are the malt DI mash pH (pHdi) and buffering data I use. The model is that proton deficit WRT pH is

mEq/kg = a1*(pH - pHdi) + a2*(pH - pHdi)^2 + a3*(pH - pHdi)^3
The numbers listed below are, respectively, pHdi, a1, a2 and a3. Some of the malts list a pHdi to 4 significant decimal places and are followed by three coefficients. These are malts I have measured at near 50 °C and referenced to 20 °C (the extra decimal places come from the temperature adjustment). Those malts (most of them) that have only a1 are malts measured by Kai Troester. He measured at two pH's (pHdi and, I think, 5.7) and published the amount of acid or base it took him to get there. The a1 in the table is simply the amount of acid he reported divided by the pH difference and thus represents the average buffering over that range. As the discussion in the Palmer book shows the buffering can be quite non linear but I have found that using the Troester numbers gives me a reasonable result. IOW the errors introduced by using the average don't seem to be any worse than errors induced by the fact that Munton's MO is quite a different animal from Crisp's for example which probably doesn't match Fawcett's.

Weyermann Pneumatic Pils 5.6227 -40.69 14.821 -10.008
Chocolate 600L Crisp 4.69875 -76.43 -0.404 -3.837
Caramel 80L Briess 4.76565 -89.684 31.837 -10.056
Weyermann Floor Pils 5.85475 -31.299 3.869 -1.986
Weyermann Sauermalz 3.6165 -292.09 68.443 -5.3985
Briess Roast Barley 300L 4.69825 -35.351 -38.11 6.8614
Muntons Maris Otter 5.84177 -46.094 7.6213 -2.5325
Crisp Maris Otter 5.6894 -46.589 6.3516 -2.623
Flaked Barley 5.6437 -36.17 10.342 -2.48
Munich II Weyermann 5.54 -35 0 0
Munich I Weyermann 5.44 -32.30769231 0 0
Munich Light Franco Belges 5.62 -37.5 0 0
Vienna Weyermann 5.65 -32 0 0
Cara Munich III Weyermann 4.92 -40 0 0
Caramunich II Weyermann 4.71 -49.49494949 0 0
Cara Munich I Weyermann 5.1 -37.33333333 0 0
Cara Aroma Weyermann 4.48 -60.98360656 0 0
Crystal 10 L Briess 5.38 -30 0 0
Crystal 20L Briess 5.22 -29.58333333 0 0
Crystal 40 L Briess 5.02 -37.64705882 0 0
Crystal 60 L Briess 4.66 -48.46153846 0 0
Crystal 90 L Briess 4.77 -48.38709677 0 0
Crystal 120 L Briess 4.75 -48.42105263 0 0
Crystal 150 L Briess 4.48 -49.01639344 0 0
Roast Barley Briess 4.68 -38.82352941 0 0
Black Patent Briess 4.62 -41.48148148 0 0
Carafa III Weyerman 4.81 -39.7752809 0 0
Carafa I Weyermann 4.71 -42.42424242 0 0
Carafa I (sp) Weyermann 4.73 -47.83505155 0 0
Biscuit 5.08 -32.58064516 0 0
Sauermalz 3.43 -138.8546256 0 0
Sauermalz Weyermann 3.44 -158.4955752 0 0
DWC Cara Pils (measured years ago by AJ) 5.149 -11.787 -31.25 0

If you would like to do comparisons with the spreadsheet I use with these data (which obviously contain these data in more readable form than above) I can send you a copy (PM me). The algorithm behind it is certainly more robust than any of the spreadsheets out there now but that doesn't mean it is more accurate as robust treatment of garbage data still gives garbage results (GIGO).

 

[IslandLizard]

AJ, you always come through and not just during critical moments.

 

[robfar]

What bothers me is that my spreadsheet predicts significantly more acid for mash acidification than Bru'n Water.

Bru'nWater uses the work done by Kai Troester and others in predicting malt acidity. Kai found that the acid/color relationship of crystal malts and roast malts were very different from each other, but somewhat linear within the group. You can find all of that on http://braukaiser.com

 

[ajdelange]

The Brewers Friend calculator uses that data set and often gives answers quite different from Bru'n Water so I'm not sure that Brun' Water uses Kai's data and if it does apparently it doesn't use it in the same way. There is little point in speculating about what it does or doesn't do. If Martin wants us to know, he'll tell us.

 

[qmax]

Thanks AJ. I sent you a pm.
I'll probably update and reupload "q-water" tomorrow with all the new malts

I think the best solution for (home)brewers would be if maltsters provided just DI pH and one buffering capacity at pH 5.4 for each batch of malt. I'd imagine it's way more difficult to titrate from pH 4.6 to 6.6 like in the Water book. But what's the point when really the mash pH range is only ~5.3-5.5? I don't understand why it became a common practice to measure malt acidity by titrating to pH 8.3 or 4.3? It nicely fits the indicator color change pH, but what use is this number for the brewer?

 

[cannman]

Can someone pm me a link to the original spread sheet so I can get an Idea of what we're looking forward to?

 

[ajdelange]

Thanks AJ. I sent you a pm.
I'll probably update and reupload "q-water" tomorrow with all the new malts

I'll fire that off to you but probably not until tomorrow.

I hope you'll be able to figure out how to use it. The basis for the whole thing is that you specify malt amounts, water alkalinity, acid or base amounts calcium and magnesium amounts and a trial pH. It then calculates the proton deficit or surfeit (negative deficit) from each of those mash components and tells you what the total proton deficit is. If that's a positive number then your trial pH is too low (you'd have to add acid to reach it) and conversely if it is a negative number (there is a proton surfeit and you'd have to absorb protons to reach that pH). You have to keep tweaking the trial pH until you find the pH that zeroes out the deficit. This can be done by you using, for example, the techniques of root bisection or you can let Excels Solver do it for you automatically (vastly preferred).

I think the best solution for (home)brewers would be if maltsters provided just DI pH and one buffering capacity at pH 5.4 for each batch of malt.

You need to be able to calculate the amount of acid required to get to the target pH of interest. Thus you need at least two terms:
mEq/kg = a1*(pH - pHdi)
where the two terms are a1 and pHdi which can be derived from the kind of titration Kai did but a1 is the average buffering capacity between pHdi and whatever target pH you choose to measure at. It is not the buffering (slope) in the vicinity of the target pH. To get that you'd need to have a third term, a2 and
mEq/kg = a1*(pH - pHdi) + a2*(pH - pHdi)^2.

The buffering in the region of interest is then

d(mEq/kg)/dpH = 2*a2*(pHmash - pHdi)

You could, of course, do the expansion about the mash pH

mEq/kg = b1 + b2*(pH - pHmash)

but then what value would you pick for pHmash? It just seems natural to expand about pHdi. Whichever way you do it you will have to take at least 3 measurements one at pHdi, one at pHmash and another near pHmash.

I'd imagine it's way more difficult to titrate from pH 4.6 to 6.6 like in the Water book.

It is very difficult or if not difficult, time consuming.

But what's the point when really the mash pH range is only ~5.3-5.5?

As the numbers come in for each value of added or subtracted acid I do a mmse curve fit (find values for a1, a2 and a3 which minimizes the rmse between the curve constructed from those parameters and the measured data). One looks at the generated curve, the data the residuals and the standard errors in the estimates of a1, a2 and a3. There is a definite art to curve fitting. One keeps adding measurements until the residuals look good and one is sure he has the inflection points defined. Depending on the malt (or, in particular, how non linear it is) one may have to measure quite a few points. Keep in mind that one is sort of averaging down measurement errors in this process. If we can ever get maltsters on board doing this I expect experience will allow smarter methods for determining the coefficients and it may even turn out that only a1 and a2 are needed.

I don't understand why it became a common practice to measure malt acidity by titrating to pH 8.3 or 4.3? It nicely fits the indicator color change pH, but what use is this number for the brewer?

Those numbers do fit, respectively, phenolpthalein and methyl orange but they also represent the pH's at which, respectively, all carbonate/bicarbonate has been converted to carbonic acid and at which all carbonic acid has been converted to bicarbonate. Thus those titration end points are valid only for potable waters in which the only sources of alkalinity or acidity are derived from limestone and CO2. But take someone who isn't that sophisticated WRT the chemistry here and tell him you want to measure the acidity of malt. As far as he knows acidity is measured to pH 8.3 and so that's what he does.

Bottom line is that sufficient measurements must be taken at whatever pH's are necessary to obtain sufficient quality in a1, a2 and a3 such that those coefficients produce sufficiently accurate estimates of proton deficit in the region say 5.0 to 5.7.

 

[qmax]

I just re-uploaded my updated spreadsheet.
I think it should work great with all the new malts.
I was a bit surprised at how Muntons Maris Otter has twice the alkalinity of Weyermann Pneumatic Pils.
I'm quite tired, I'll continue the discussion tomorrow.

 

[ajdelange]

To pH 5.35, yes it is double but to pH 5.3 it is 1.8 times, to pH 5.5 it is 3.2 times and to pH 5.6 over 12 times.

 

[qmax]

a1 is the average buffering capacity between pHdi and whatever target pH you choose to measure at. It is not the buffering (slope) in the vicinity of the target pH.

Right. What I meant by buffering capacity at pH=5.4 was really the average buffering capacity from DI pH to pH=5.4. However to obtain it, you do not need to have a formula for buffering capacity and then integrate it. What you do is just divide your titration curve mEq/kg by (DI pH - 5.4). For maltsters that would mean measure how much mEq of acid or base it took to titrate (congress extract) to pH=5.4, and divide it by (DI pH - 5.4). This number we can use for our buffering capacity. And it should not create an extra work for maltsters. They titrate to pH 8.3 or 4.3 anyway, why not change that to pH 5.4?

I tried to see how much error for pH 5.3 and 5.5 this linearizion around pH=5.4 would create.
So here is the +- deviation in malt alkalinity for mash pH 5.3 or 5.5
Weyermann Pneumatic Pils 4.4%
Crisp Maris Otter 1.65%
Weyermann Floor Pils 1.7%
Muntons Maris Otter 2%
Chocolate 600L Crisp 0.8%

The full titration curve with 3 coefficients is obviously a better option, I just don't know if maltsters would be willing to do it.

There is a definite art to curve fitting.

Why can't you just use the default trend line fitting in Excel?
It can automatically fit a 3rd power polynomial to a set of points.

 

[ajdelange]

The full titration curve with 3 coefficients is obviously a better option, I just don't know if maltsters would be willing to do it.

They would if they thought it would boost malt sales.

A bloke from Russia posted here a couple of weeks ago saying that it was common practice in the Rodina for maltsters to publish pHdi and a single acidity number, I think it was P acidity but its a step in the right direction.

Why can't you just use the default trend line fitting in Excel?
It can automatically fit a 3rd power polynomial to a set of points.

You can but there is still art to it and it's much easier to see what's going on with a visualization program like IGOR which is what I use. It, as I mentioned yesterday, calculates and displays (among other things) the residuals, their statistics, the standard deviations of the coefficients, and confidence limits for the fits. You can also have it fit any set of functions you can program so if, for example, I couldn't get a good fit with a third order polynomial I could use, just to grab something out of the air, Chebychev polynomials. It also does fitting not just for polynomial fits but for offset polynomial fits (not sure Excel does that) which is what we have here (Taylor series expansion). When all is said and done it makes a presentable graph (e.g. Fig 17 on p88 of the Palmer book which shows the data set for Wyermanns pneumatic pils). Perhaps most important to me is that while I am doing the analysis IGOR is running the pH meter taking mV and temperature measurements from it and converting those to pH values which it records to a file and displays on the computer screen in large enough letters that I can read it from across the room if I am at the meter. Don't think Excel does that!

 

[qmax]

A bloke from Russia posted here a couple of weeks ago saying that it was common practice in the Rodina for maltsters to publish pHdi and a single acidity number, I think it was P acidity but its a step in the right direction.

I think I know that bloke you're talking about . This NaOH malt titratable acidity to pH=8.3 is found in more places than just Russia. For example, you can see it in Malt and Malting by D.E. Briggs, 1998 p.717 (available as a preview on http://books.google.com)

As I was just comparing how average buffering capacity with the end point pH=5.4 affects malt alkalinity in a wider pH range (5.3-5.5), the error in alkalinity was up to ~4%. What magnitude of error can I expect if I use buffering capacity averaged from DI pH to pH=8.3 and apply that with respect to mash pH=5.4? After quickly crunching the numbers for Weyermann Pneumatic Pils, I'd say the error can easily be something like ~20% or more deviation in alkalinity.
Is there a mistake in my thinking?

I'll fire that off to you but probably not until tomorrow.
I hope you'll be able to figure out how to use it.

I just had a look at your spreadsheet and even though there was a lot of stuff I did not understand, I plugged in a recipe and everything checked out nicely.

 

[ajdelange]

I think I know that bloke you're talking about .

Yes, of course!

This NaOH malt titratable acidity to pH=8.3 is found in more places than just Russia. For example, you can see it in Malt and Malting by D.E. Briggs, 1998 p.717 (available as a preview on http://books.google.com)

Yes, I see the table but it does not say how the titratable acidity is defined e.g. it doesn't say what the end point is nor how many grams of malt have the given equivalence. I don't find anything further on the subject in the chapter on analysis. He says the data came from Narziß so perhaps I need to look there.

As I was just comparing how average buffering capacity with the end point pH=5.4 affects malt alkalinity in a wider pH range (5.3-5.5), the error in alkalinity was up to ~4%. What magnitude of error can I expect if I use buffering capacity averaged from DI pH to pH=8.3 and apply that with respect to mash pH=5.4?

The only way to know that is to titrate out to 8.3 and see which I have not done because I never expect to do a calculation out to a pH that high. I only go high enough to get a good quality fit to a 3rd order polynomial in a reasonable range of interest. Going out further there is the risk that while the data over a reasonable range of pH are well fit by a third order polynomial the data out to pH 8.3 might not be and we would have to consider some other fitting scheme. I can see asking brewers to do a Taylor series expansion. I can't see asking them to do a Chebyschev polynomial expansion.

You can eyeball off Fig 17 in the water book if you like by extrapolating a polynomial fit outside the available data range is 'bold extrapolation' indeed.

After quickly crunching the numbers for Weyermann Pneumatic Pils, I'd say the error can easily be something like ~20% or more deviation in alkalinity.
Is there a mistake in my thinking?

No, I think it's sound.

I just had a look at your spreadsheet and even though there was a lot of stuff I did not understand, I plugged in a recipe and everything checked out nicely.

I'm guessing you will figure most of it out but if you have a particular question just ask. Any numbers you see way off by themselves are usually little auxiliary calculations I have done. Just ignore those.

 

[LandoLincoln]

I shall title this thread, "things that would take me about four years to comprehend just what in the hell they're talking about."

 

[qmax]

if you have a particular question just ask.

Hi again, AJ. I'm wondering what buffering capacity and DI pH you assume for flaked oats, malted and unmalted wheat, rye malt? Should I equate Special B to Caramel 120 L?

Also the other day I was listening to Basic Brewing Radio: High Gravity Mash Inefficiency. The point of the episode was that brewing calculators overshoot the OG for higher gravity beers. And so they needed to assume lower efficiency to get the OG right. I was just looking into this the other day, and my idea is that the curve of sugar density vs concentration becomes increasingly non-linear at higher densities. That's where the PPG model introduces an error. What are you thoughts on this?
I made an OG calculator that accounts for this non-linearity by using a trendline for sucrose, however to make it work I need to know dry basis extract and moisture content for each malt. Or perhaps convert PPG to as-is extract and go from there. Maybe you happen to have this data neatly tabulated as well?

 

[Singletrack]

qmax, I quickly looked at your spreadsheet. It is mean (e.g. what do all the different colors mean?), but I appreciate the open source concept. With time I hope it becomes friendlier and remains open source. I agree that most other available calculators either provide poor models or are clunky to use. For this reason, I use my own (cough) spreadsheet now -- with some help from Martin -- and it hits the pH every time. I've stopped measuring, because it is just a wasted effort. It is always right. This was not an easy exercise for me, and I doubt many would want, or be capable, of repeating it. Point is: You are doing a good thing, but you need to make it usable by others, without losing correctness. Good luck!

 

[qmax]

qmax, I quickly looked at your spreadsheet. It is mean (e.g. what do all the different colors mean?), but I appreciate the open source concept. With time I hope it becomes friendlier and remains open source. I agree that most other available calculators either provide poor models or are clunky to use. For this reason, I use my own (cough) spreadsheet now -- with some help from Martin -- and it hits the pH every time. I've stopped measuring, because it is just a wasted effort. It is always right. This was not an easy exercise for me, and I doubt many would want, or be capable, of repeating it. Point is: You are doing a good thing, but you need to make it usable by others, without losing correctness. Good luck!

Hm, Thanks for your feedback. I thought it would be easy to figure out.
Green is for input. Light blue cells are calculated. Neon blue is for headers.
Off-white is for names of entered values. Dark blue for names of calculated values.
Red and yellow are just to highlight some of the more important calculated values.

I'm wondering how it can be possible to hit your pH every time when there is so little data on malts' buffering capacities and even less on titrations.

 

[ajdelange]

Hi again, AJ. I'm wondering what buffering capacity and DI pH you assume for flaked oats, malted and unmalted wheat, rye malt? Should I equate Special B to Caramel 120 L?

Since I've never measured any of those I wouldn't know what to use so I'd pick the grain that either Kai or I have measured that seems most like the grain in question. In some cases (isn't rye rather acidic) I probably be way off but as the amounts of these specialties is usually fairly small you can usually get away with doing that.

Also the other day I was listening to Basic Brewing Radio: High Gravity Mash Inefficiency. The point of the episode was that brewing calculators overshoot the OG for higher gravity beers. And so they needed to assume lower efficiency to get the OG right. I was just looking into this the other day, and my idea is that the curve of sugar density vs concentration becomes increasingly non-linear at higher densities. That's where the PPG model introduces an error. What are you thoughts on this?

I don't use the pppg system but instead figure actual extract amounts from the malt specs. If the malt is specified as being able to produce 80% of its dry basis weight as extract I compute the pounds of extract from the amount I use based on 80% of the grain weight and a factor that represents what I can expect from my equipment and procedures. The ppppg thing just never made a lot of sense to me. It is linear and handy but using actual extract weight isn't that difficult. You have the ASBC polynomial to take you from SG to Plato and root finders or the Lincoln Equation to take you back.

I made an OG calculator that accounts for this non-linearity by using a trendline for sucrose, however to make it work I need to know dry basis extract and moisture content for each malt. Or perhaps convert PPG to as-is extract and go from there. Maybe you happen to have this data neatly tabulated as well?

Whichever system you use you have to have malt data. If you use the ppppg system you have to look up in a brewing book how many ppppg a given malt or sugar produces and reduce that by your system efficiency. If you use the extract system you have to have the maltsters data on the HWE, fine grind, dry basis and adjust for what you think the moisture content of the bag of malt you have before you is and the efficiency of your system. You are obviously going to be off by quite a bit if the only HWE data is like that given by Weyermanns on their website (i.e. HWE for their Pilsner malt but not for the particular lot you have) and moisture content is going to be a way unless you try to measure it but you won't run into errors introduced by assuming things are linear where they are not.

 

[qmax]

Since I've never measured any of those I wouldn't know what to use so I'd pick the grain that either Kai or I have measured that seems most like the grain in question. In some cases (isn't rye rather acidic) I probably be way off but as the amounts of these specialties is usually fairly small you can usually get away with doing that.

I used up to 25% oats and 40% wheat in my brewing, and it's tough without a pH meter. I was thinking of brewing a Ryeball Ale recipe from this site, which is 18% rye malt. Now I'm not so sure.

Once you've figured out the amount of extract in your wort, to calculate Plato you also need to know the mass of the wort, and you only know the volume (normally). What do you go about this?

and root finders or the Lincoln Equation to take you back

I find the root solver in Excel a bit cumbersome. The inverse function of ASBC polynomial is a cubic equation which can be solved directly. This equation SG=((P/(258.6-(P/258.2)*227.1)+1) also has accuracy R^2=0.9999964 in the range 0-32`P.

 

[ajdelange]

I used up to 25% oats and 40% wheat in my brewing, and it's tough without a pH meter. I was thinking of brewing a Ryeball Ale recipe from this site, which is 18% rye malt. Now I'm not so sure.

Yes, until someone gets some data on these all you can do is guess and be no better off than the other spreadsheets and calculators that guess based on color.

Once you've figured out the amount of extract in your wort, to calculate Plato you also need to know the mass of the wort, and you only know the volume (normally). What do you do about this?

Calculate the specific gravity from the Plato value, multiply that times the density of water (0.998203) and by the volume of the wort. That's the mass.

I find the root solver in Excel a bit cumbersome.

Yes, I suppose it is but I use it all the time.

The inverse function of ASBC polynomial is a cubic equation which can be solved directly.

Yes it can but a root bisector is much easier to code. With the direct solution you have to know which root to select (and I can give you more information on that if you want it).

Something that never occurred to me before this morning is that the Plato/SG relationship is very close to being linear. This should make Newton's method a good method for inversion and indeed it appears to be so. Here's a copy and paste from an Excel spreadsheet.

5.00 Target Plato
1.0500 SG First Guess
12.39 ° P
-7.39 Error
237.3789 Slope
-0.031121754 1sr corr
1.0189 SG 2nd guess
4.80 ° P
0.20 Error
250.3399 Slope
0.000797454 2nd corr
1.0197 SG 3rd guess
5.00 ° P
0.00 Error
249.9979 Slope
5.45559E-07 3rd corr
1.0197 SG 4th guess
5.00 °P

Plato is calculated from SG using the ASBC polynomial

°P =(((135.997*SG - 630.272)*SG + 1111.14)*SG - 616.868)

and the slope from the first derivative of this:

∂°P/∂SG =((3*135.997*SG - 2*630.272)*SG + 1111.14)

You could stick this column on another sheet or in a hidden column so your users wouldn't see it

This equation SG=((P/(258.6-(P/258.2)*227.1)+1) also has accuracy R^2=0.9999964 in the range 0-32`P.

Yes, the Lincoln equations are good too (both for conversion to and from Plato). But note that a very high r^2 doesn't mean that the Lincoln equation result is all that good. It does deviate from the ASBC polynomial somewhat in some regions but not enough to be a problem for home brewers certainly. An inversion by Solver or Newton's method can be much more accurate.

If the ASBC polynomial is being used then one is limited to the range the Plato commission studied (18 °P ?). To go beyond this a different polynomial is necessary. I've taken two approaches. One is to combine the ASBC polynomial with sucrose data from, for example, the CRC handbook. I've butted the ASBC curve together with the CRC data curve such that there is continuity in °P and slope at the switchover point. This, of course, compromises the data both above and below the switchover. The other is to use the ICUMSA polynomial for 20 °C. It's a bit of a mess to program/enter as there are lots of coefficients which make it possible to use at any temperature.

 

[qmax]

Calculate the specific gravity from the Plato value, multiply that times the density of water (0.998203) and by the volume of the wort. That's the mass.

I mean once I know how many kg of 100% extract I get from the grain and I know my post-boil volume, how do I find Plato? I've figured it out, and it's a big formula. Am I overcomplicating things?

With the direct solution you have to know which root to select

You don't need to know which root to select as the discriminant for this particular equation <0 for all possible Plato values, and hence only one real root.
Here is this equation, replace PLATO with your cell.

=(-1/3/135.997*(-630.272+1*(((2*-630.272^3-9*135.997*-630.272*1111.14+27*135.997^2*(-616.868-PLATO))+((2*-630.272^3-9*135.997*-630.272*1111.14+27*135.997^2*(-616.868-PLATO))^2-4*(-630.272^2-3*135.997*1111.14)^3)^0.5)/2)^(1/3)+(-630.272^2-3*135.997*1111.14)/1/(((2*-630.272^3-9*135.997*-630.272*1111.14+27*135.997^2*(-616.868-PLATO))+((2*-630.272^3-9*135.997*-630.272*1111.14+27*135.997^2*(-616.868-PLATO))^2-4*(-630.272^2-3*135.997*1111.14)^3)^0.5)/2)^(1/3)))

 

[ajdelange]

I mean once I know how many kg of 100% extract I get from the grain and I know my post-boil volume, how do I find Plato? I've figured it out, and it's a big formula. Am I overcomplicating things?

I'm not following the question. If you have x liters of wort containing y kg of extract there is no way to know the concentration unless you know the amount of water in the wort. What you usually want to do is compare the extract you realized as opposed to the extract you predicted in order to find out how efficient you are. Anyway, the obvious answer to your question is to find the Plato of your wort you measure the specific gravity the use one of the conversion schemes to convert that number to Plato. Then multiplying the specific gravity by the volume by 0.998203 gives you the mass of the wort which, multiplied by Plato/100 gives the mass of extract. This can be compared to the mass of the grain mashed or to the predicted extract for efficiency calculations.

You don't need to know which root to select as the discriminant for this particular equation <0 for all possible Plato values, and hence only one real root.

It's pretty clear there is only one real root so out of the three possible roots the closed form solutions offer you need to pick the real one. I don't recall exactly how that is done but in what follows, clearly I am calculating the real root.

Here is this equation, replace PLATO with your cell.

=(-1/3/135.997*(-630.272+1*(((2*-630.272^3-9*135.997*-630.272*1111.14+27*135.997^2*(-616.868-PLATO))+((2*-630.272^3-9*135.997*-630.272*1111.14+27*135.997^2*(-616.868-PLATO))^2-4*(-630.272^2-3*135.997*1111.14)^3)^0.5)/2)^(1/3)+(-630.272^2-3*135.997*1111.14)/1/(((2*-630.272^3-9*135.997*-630.272*1111.14+27*135.997^2*(-616.868-PLATO))+((2*-630.272^3-9*135.997*-630.272*1111.14+27*135.997^2*(-616.868-PLATO))^2-4*(-630.272^2-3*135.997*1111.14)^3)^0.5)/2)^(1/3)))

Omigosh!

I checked it, it works and so there is no reason not to use it but I'll show you how I do this which may or may not be appealing to you. First note that the term (-616.868-PLATO) appears in there 4 times. It is appealing to compute this once. In fact, as we shall see, it is appealing to compute (-616.868-PLATO)/135.997. Again I'll paste in a couple of columns from a spreadsheet:

10 Target Plato
135.997 alpha
-630.272 beta
1111.14 gamma
-616.868 delta
-4.634455172 (beta/alpha)
8.170327287 gamma/alpha
-4.609425208 (delta - P)/alpha
1.010935707 a
0.638940123 b
0.374601885 sqrt
0.380598832 A
-0.885390444 B
-0.504791612 A+B
1.040026779 soln

Alpha, beta, gamma and delta are the coefficients of the polynomial an beta/alpha through (delta - P)/alpha are those coefficients (with P subtracted off in the case of the constant term) normalized by the cubic term coefficient.

Then we compute
a = (3*gamma_ - beta_^2)/3
b = (2*beta_^3 - 9*beta_*gamma_ + 27*delta_)/27

where beta_, gamma_ and delta_ are the normalized coefficients (with P subtracted off first in the case of delta). From a and b we compute

SQ = sqrt(*b^2)/4 + (a^3)/27) )

then

A = ( -b/2 + SQ)^(1/3)

and

B = -(b/2 + SQ)^(1/3)

We take the real cube root in both cases.

Then

x = A + B

substituted into

S = x - beta_/3

gives the solution.

This method breaks the problem down into more manageable bits but is still pretty bad. Note that there are 15 cells used here whereas with Newtons method I can get an answer that returns 9.999999 °P in only 13. Of course you are doing it in 1 cell but what a cell!

 

[qmax]

I'm not following the question.

I know the grain bill for my beer, and I'm trying to make a prediction of OG based on the extract I get from those grains, not on PPG. How do you do this?

If you have x liters of wort containing y kg of extract there is no way to know the concentration unless you know the amount of water in the wort.

There is a way to do this. P=kg_of_extract/(volume_of_wort*SG*0.998203)
The simple answer is root solver. But with some math, you can get around that too. I used my own quadratic trendline for sucrose and ended up with a cubic equation. With ASBC, you'll probably get a 4th degree polynomial.

Of course you are doing it in 1 cell but what a cell!

I just did it the same way you did, and then condensed it into 1 cell. Most likely, I won't ever need to know the intermediate results.

 

[ajdelange]

I know the grain bill for my beer, and I'm trying to make a prediction of OG based on the extract I get from those grains, not on PPG. How do you do this?

Perhaps an example would make it clearer. Let's suppose we intend to mash 10 kg of grain using 30L of water. The grain has a fine grind, dry basis, HWE of 80% meaning that we would, under the ideal conditions of a laboratory mash, obtain 8 kg of extract. As home brewers we might expect to be 10% less efficient than the lab people and so we assume 70%. Thus we anticipate 7 kg of extract. Assuming that we'd add another 10L of water during sparge then we'd have a total of 40 L of water and 7 kg of extract so that the estimated OG would be:

OG = 100*7/(7 + 40) = 18.92 °P

I mean once I know how many kg of 100% extract I get from the grain and I know my post-boil volume, how do I find Plato? I've figured it out, and it's a big formula. Am I overcomplicating things?

There is a way to do this.

Not if you don't have information about the amount of water. This can come from keeping track of what you have added, potentially boiled off, and a retention estimate for the sparge or, and this would be the case 99% of the time, from a measurement you make on the wort.

P=kg_of_extract/(volume_of_wort*SG*0.998203)

So here you are assuming that you have made an SG measurement (though you didn't mention that in the previous post). All you have to do, if you have an SG measurement, is stick that measurement into the ASBC polynomial and you have °P. The amount of extract you estimated in planning the brew can't be used because it was only an estimate. You won't get the full HWEFG (you won't be fine grinding, your sparge isn't as efficient as the Congress mash sparge, there is moisture to account for....). But it is, of course, reasonable to want to know how much extract you actually got in order to compare it to your estimate in order to improve on future estimates (work out 'brew house efficiency'. The amount of extract is

extract_mass = (°P/100)*Volume*SG*0.998203

The simple answer is root solver. But with some math, you can get around that too. I used my own quadratic trendline for sucrose and ended up with a cubic equation. With ASBC, you'll probably get a 4th degree polynomial.

No root finding is required.

There is a situation where a 4th degree equation must be solved (in which case you will have to use a root finder) but this isn't it.

I just did it the same way you did, and then condensed it into 1 cell. Most likely, I won't ever need to know the intermediate results.

That's fine. Breaking the problem down just makes it easier to trouble shoot and very easy to paste in a new set of coefficients if, for example, you wanted to shift to an approximation to the ICUMSA (or another) polynomial for wider range of °P or you had your own fit to the Plato table (the ASBC polynomial is not the best fit to the ASBC table data).

 

[qmax]

P=kg_of_extract/(volume_of_wort*SG*0.998203)
So here you are assuming that you have made an SG measurement (though you didn't mention that in the previous post).

No, that's not it.

SG is a function of P. One example of such a function is the ASBC equation.

What you do is put this function P(SG) in the denominator instead of an SG value, and so you are left with only one variable P on both sides of the equation.

If I plug in my huge inverse ASBC equation above for P(SG) function, I probably won't be able to express P explicitly. So there comes the root finder.

As home brewers we might expect to be 10% less efficient than the lab people and so we assume 70%.

Does this mean you assume your efficiency = 7/8 = 87.5%?

Now to the example. I know my grain absorption is 1.2 L/kg.
I don't have any deadspace.
So I get 40-1.2*10=28 L in the first drain.
Then I batch sparge with 10 more L of water.
That leaves me with 38 L of wort (of what mass?) with 7 kg of extract pre-boil?

That's the way I was calculating it, but now I'm questioning if I could add volumes like this.

I guess this is confusing to me because I always thought of the whole process including grain absorption on the basis of volume.

What I knew for sure was I lose this volume of water on absorption and this volume on evaporation, and I end up with this volume post-boil. This worked for me.
When I thought of calculating OG, I instinctively went with kg of extract and post-boil volume as my variables.

 

[ajdelange]

No, that's not it.

SG is a function of P. One example of such a function is the ASBC equation.

What you do is put this function P(SG) in the denominator instead of an SG value, and so you are left with only one variable P on both sides of the equation.

I think the problem you must be trying to solve is, in effect:
I have put 126 grams of sucrose into a volumetric flask and made up to 1 L without measuring the amount of water I added. How much water did I add? From the answer to that question we can trivially compute °P, SG and density, the partial molar volume of sucrose at that concentration etc.

I think this must be the problem because the equation you posted solves that problem

P(SG) = 100*M/(V*SG*.998203)

Multiplying both sides by SG gives

SG*P(SG) = M/(0.998293*V)

and we then have to solve the 4th order equation in SG

SG*P(SG) -M/(0.998293*V) = 0

If I plug in my huge inverse ASBC equation above for P(SG) function, I probably won't be able to express P explicitly. So there comes the root finder.

There are closed form solutions for 4th order equations but here a root finder or Newtons method is much easier to implement. For the 100 grams in a 1000 mL solution.

0.126 Extract Mass
1 Volume
1.05 trial SG
0.38434652 trialFunction
-367.6201786 Slope
-0.001045499 Correction
1.048954501 New trial
0.110834741 New Trial Function
-367.4250912 Slope
-0.000301653 Correction
1.048652848 New trial
0.031930129 New Trial Function
-367.3687939 Slope
-8.69157E-05 Correction
1.048565933 New trial
0.00919606 New Trial Function
-367.3525721 Slope
-2.50333E-05 Correction
1.048540899 New trial
0.002648301 New Trial Function
-367.3478999 Slope
-7.20924E-06 Correction
1.04853369 New trial
-6.87555E-06 New Trial Function
12.03914164 °P
1.046649475 Mass of Solution
0.920641862 Mass of Water
8.62% Volume Expansion

The function is

(((135.997*SG - 630.272)*SG + 1111.14)*SG - 616.868)*SG-100*Mass/(0.998203*Vol)

and we want to drive that to 0. The slope is

((3*135.997*SG-2*630.272)*SG+1111.14)*SG-616.868

So the Newton method solution isn't bad though as the problem is now less linear it takes more iterations to get the function down to 5E-5 though note that the SG solution changes in decimal places beyond the 3rd after the 3rd iteration.

Is this what you had in mind?

Does this mean you assume your efficiency = 7/8 = 87.5%?

It's really moot as the numbers are for illustration only but I had intended to mean that rather than 80% extract you might get 10% less than that or 70% - 7 kg extract per 10 kg malt.

Now to the example. I know my grain absorption is 1.2 L/kg.
I don't have any deadspace.
So I get 40-1.2*10=28 L in the first drain.
Then I batch sparge with 10 more L of water.
That leaves me with 38 L of wort (of what mass?) with 7 kg of extract pre-boil?

Keep in mind that if you add 1 liter of water to a wort the volume of the wort will not change by 1 L but by something close to that because the sugar expands the volume of the liquid. In the example above we added 126 grams of extract to 920 grams of water and got 1 L of 12 °P solution. The volume of the water expanded by 8.62%. It is this volume expansion that makes things difficult.

That's the way I was calculating it, but now I'm questioning if I could add volumes like this.

What you can do is make adjustments to get a particular volume in the kettle. You can make up the kettle to 38 L if you want to and, if you know that this 38L really contains 7 kg extract calculate that the wort would be 17.22 P and that it contains 33.6 kg of water.

I instinctively went with kg of extract and post-boil volume as my variables.

It's really much easier to think in terms of mass. I even put load cells under my kettle. If I have 400 lbs wort at 12 °P I imediateley know I have 48 lbs extract and can quickly figure my efficiency by dividing that by the weight of the grain I mashed. You can, clearly, work in terms of volume too if you set the volume. However, as soon as you ask the question 'How strong is a sugar solution with x grams of sugar per liter you have to solve the 4th order equation.

 

[mtnagel]

I just started playing with it and it looks pretty good.

I plugged in two recipes I've brewed and compared them to Bru'n Water and actual results.

Hopefully this makes sense, but here are the results (assuming I entered everything correctly):

So the Bru'n water lactic amount is the amount it said to hit the target of 5.4 and the actual pH is what I got when I added the Bru'n Water lactic level.

So Bru'n Water was spot on for the 2nd batch and Q Water would have me adding a little more acid, which would lower the pH below the target.

For the first batch, Bru'n Water was off by 0.2 pH units high and Q Water would have me add more acid, which would probably move me closer to target.

A couple problems is I didn't see all the malts on there, but the recipes I entered did have all the ingredients, so that's why I picked them.

Also, do you know why switching from Crisp to Muntons MO gives widely different acid amounts? The second recipe used MO, but I'm not even sure what I used, but I put Muntons as that gave a lactic addition closer to what I used.

Good start though. Thanks for sharing.

 

[ajdelange]

Also, do you know why switching from Crisp to Muntons MO gives widely different acid amounts? The second recipe used MO, but I'm not even sure what I used, but I put Muntons as that gave a lactic addition closer to what I used.

If you titrate those malts at close to 50 °C you will find that the acid required to reach pH is

Crisp:
mEq/kg = -46.589*(pH - 5.408) + 6.3156*(pH - 5.408)^2 -2.673*(pH - 5.408)^3

Muntons:

mEq/kg = -46.094*(pH - 5.420) + 7.6213*(pH - 5.420)^2 -2.5325*(pH - 5.420)^3

IOW the malts are actually very similar. Now, however, if you cool the mash, for purposes of pH measurement you will find that the DI mash pH for the Crisp shifts from 5.408 to 5.689 whereas for the Muntons it shifts from 5.420 to 5.842. Thus, while the one isn't really more alkaline than the other at mash temperature it appears to be if you wish to set things based on room temperature mash pH measurement.

This is a new finding and one that I haven't quite decided what to do about yet. I think the answer has to be that we should actually calculate at mash temperature (which, of course, raises the question as to which mash temperature we should use) and then convert, based on what we know about how the individual malts pH's vary with temperature, to room temperature in order to get an idea as to what to expect from the pH meter reading.

 

[qmax]

I think the problem you must be trying to solve is, in effect:
I have put 126 grams of sucrose into a volumetric flask and made up to 1 L without measuring the amount of water I added. How much water did I add?
Is this what you had in mind?

Almost. My question is though: what's gravity of the solution?

However, as soon as you ask the question 'How strong is a sugar solution with x grams of sugar per liter you have to solve the 4th order equation.

Yes... However I managed to get by with a 3rd order equation. I created a 2nd degree SG(P) trendline for the range 0-30°P which should cover even the highest gravity barley wines. Its maximum error is at the real SG=1.091982; so instead of that my trendline yields 1.091938. This is the largest error throughout the range. I doubt brewers would wish for more accuracy.

I'm only vaguely familiar with the Newton's method. I think I won't need to resort to it.

It is this volume expansion that makes things difficult. It's really much easier to think in terms of mass. I even put load cells under my kettle.

Right. Well, I think a load cell would be overkill for most homebrewers.
Do you know if grain absorption can be converted to the mass basis?

Here are my thoughts on it: during the mash the grains absorb a constant volume of wort with the same gravity as the surrounding solution. When I batch sparge the absorbed wort inside the grains equalizes SG with the sparge water. Bottom line, this seems way too complicated to calculate.

Or is there some other way to predict what mass of wort with how much extract I would get pre-boil?

 

[qmax]

I just started playing with it and it looks pretty good.

I just spotted a pretty big flaw in my spreadsheet. All mineral additions were assumed anhydrous !!! If you used any, this must have skewed the results.

I'll upload a new version with this fixed very soon

A couple problems is I didn't see all the malts on there

This is because there is not a lot of available data on many malts. Other spreadsheets use color approximations, but it's a wild guess.

 

[qmax]

Now, however, if you cool the mash, for purposes of pH measurement you will find that the DI mash pH for the Crisp shifts from 5.408 to 5.689 whereas for the Muntons it shifts from 5.420 to 5.842. Thus, while the one isn't really more alkaline than the other at mash temperature it appears to be if you wish to set things based on room temperature mash pH measurement.

This is a problem! Let's go back to the basics. Do we adjust mash pH to get higher conversion/efficiency, do we do it for clarity, or are we mainly concerned with flavor?

I think clarity and conversion can be achieved within a very broad range.
If we are looking for better flavor, is it about the yeast performance? Then we can adjust the pH post-boil. Or is it about the extraction of something other than sugar from the grain?

With grains having different pH shifts, we would either miss the mash pH or the post-boil pH.

 

[ajdelange]

Almost. My question is though: what's gravity of the solution?

Once you know the amount of water (mass), given that you know the amount of sugar (mass) then °P is trivially computed from

°P = S/(S+W)

S = mass of sugar
W = mass of water

But from the previous post, the equation we need to solve is:
(((135.997*SG - 630.272)*SG + 1111.14)*SG - 616.868)*SG-100*Mass/(0.998203*Vol) = 0
and we need to solve it for SG, the gravity. Thus solution of this equation gives the gravity directly which, of course, can immediately be converted to °P with the ASBC polynomial.

Yes... However I managed to get by with a 3rd order equation. I created a 2nd degree SG(P) trendline for the range 0-30°P which should cover even the highest gravity barley wines. Its maximum error is at the real SG=1.091982; so instead of that my trendline yields 1.091938. This is the largest error throughout the range. I doubt brewers would wish for more accuracy.

That should work.

Keep in mind that most of this work was done for a program called ProMash. I was always on the author's case about things needing to 'close' to near machine precision and so pushed him into using roots using bisection, accounting for the temperature dependence of the specific heat of water etc.

I'm only vaguely familiar with the Newton's method. I think I won't need to resort to it.

I'm sure it was taught to you somewhere along the line as it is a very simple method of finding the roots of well behaved equations like these. The idea is you have a guess as to what the solution of

(((135.997*SG - 630.272)*SG + 1111.14)*SG - 616.868)*SG-100*Mass/(0.998203*Vol) = 0

might be, say 1.05. You plug 1.05 into the equation and instead of 0 you get 0.38, not zero. So you assume that near 1.05 the equation is linear and compute the slope at 1.05. Your error, &#8710;y = (dy/dSG)&#8710;SG = 0.38 and solve for &#8710;SG = 0.38/(dy/dSG). (dy/dSG) is obtained by differentiating the equation you are solving:

(dy/dSG) = ((3*135.997*SG-2*630.272)*SG+1111.14)*SG-616.868

You then 'correct' your original guess by adding &#8710;SG to it and repeating until the calculated &#8710;SG isn't changing significantly or (((135.997*SG - 630.272)*SG + 1111.14)*SG - 616.868)*SG-100*Mass/(0.998203*Vol) is close enough to 0.

Right. Well, I think a load cell would be overkill for most homebrewers.

Of course it would be. Just one of many goofy ideas I've tried out over the years.

Do you know if grain absorption can be converted to the mass basis?

Why not. Grind some grain, put it in a tared bucket, weigh it, cover it with warm water, wait, then pour off the water through a strainer, cheese cloth or something similar, drain to your satisfaction and then weigh again.

Here are my thoughts on it: during the mash the grains absorb a constant volume of wort with the same gravity as the surrounding solution. When I batch sparge the absorbed wort inside the grains equalizes SG with the sparge water. Bottom line, this seems way too complicated to calculate.

Agreed

Or is there some other way to predict what mass of wort with how much extract I would get pre-boil?

Yes. Take the FGHWE number from the maltster or a number for a similar grain and adjust it by your 'brew house efficiency' i.e. what you have learned by calculating extract based on worts you have prepared. This efficiency will include the effects of extract hung up in the grain during sparge. Most programs that do these sort of calculations have a place to enter this efficiency figure. For a given set of equipment and practices finding it has to be an iterative procedure.

 

[ajdelange]

This is a problem!

Yes it is and as I said I haven't figured out what to do about it yet.

Let's go back to the basics. Do we adjust mash pH to get higher conversion/efficiency, do we do it for clarity, or are we mainly concerned with flavor?

As far as I am concerned flavor.

I think clarity and conversion can be achieved within a very broad range.
If we are looking for better flavor, is it about the yeast performance?

Yes, I think good extraction can occur over a range of pH, so it's about flavor IMO.

Then we can adjust the pH post-boil. Or is it about the extraction of something other than sugar from the grain?

Other stuff. If you need to tweak pH to optimize yeast performance you can, of course, do that too.

With grains having different pH shifts, we would either miss the mash pH or the post-boil pH.

The problem in this case is that if we use Muntons MO based on a target room temperature pH we will add more acid to the mash than we would if we used Crisp targeting that same room temperature pH. At mash temperature, though, the Muntons mash would be at lower pH than the Crisp mash. Theoretically that's not good. Now the really conscientious brewer will have noticed that a mash using Muntons should be adjusted to a higher room temperature pH than one using Crisp. When we say mash pH should be 5.3 - 5.6 we are allowing ourselves quite a lot of wiggle room for effects like this.

 

[mtnagel]

I just spotted a pretty big flaw in my spreadsheet. All mineral additions were assumed anhydrous !!! If you used any, this must have skewed the results.

I'll upload a new version with this fixed very soon

Yes, I did. Thanks.

This is because there is not a lot of available data on many malts. Other spreadsheets use color approximations, but it's a wild guess.

Well without the data, using malts that aren't in your sheet would just be a wild guess too, right?