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).