A thought experiment to test the general validity of available mash pH software

[Big Monk]

Why limit yourself to the linear form? The cubic form does not preclude the use of the linear malt model in cases where you only have sufficient data to model a malt with two terms (i.e. models derived from colors or Kai's measuremnt). Plus you get the advantages of the more robust treatment of alkalinity.

But I really wanted to talk about the Solver. I think it's great. I love it. I always say that if you think you are using Excel but aren't using the Solver then you aren't really using Excel. BUT - when I told John Palmer that he most certainly should include it in his book (I came up with the charge method while helping him with the mash pH estimation part of it) he said he didn't want to get into something radically new and when I first presented the charge method at an MBAA conference and asked how many people in a room used Excel over half (15? guys) said they did but only 3 said they used the Solver (and one was Karl Siebert - college professors don't count). IOW people don't like the Solver or don't know about it or are afraid of it. And you have to admit it's clumsy to use. You have to set up the problem each time you want to solve it. For guys like us in the thick of the problem that's OK and its a wonderful tool that can help us develop programs for the Suds-Water-Works-Good types but I don't think that you are going to get those guys to use Solver. They want to put in malt type, bicarbonate and acid amounts and have the pHz estimate pop up automatically. Now perhaps you can set up a macro to invoke Solver (never thought of that before but why not?) but there is another approach. The spreadsheet below, based on the Voltmeter spreadsheet we've been fiddling with here does go directly to pHz estimate without using Solver.
View attachment 579048
View attachment 579049
Column B is used to find the pH that would be realized when 2 mL of 88% lactic acid is added to 10 gal of DI water and used to mash 12.5 lbs of Weyermanns Pils. The answer, using the Solver, is pHz = 5.515 (to 3 decimal places). Look at row 89. The answer is copied in B89. In column C we suppose we don't know about the Solver and guess that pHz = 5.3. Looking at C79 we see that the total Q change is 60.1 and thus realize that 5.3 isn't a very good guess. So in Column D we add 0.0000001 to the pH and compute the new charge change sum. It isn't very different from the charge obtained with 5.3 exactly but the ratio (∑∆Q(5.3000001) - ∑∆Q(5.3))/0.0000001 (computed in cell B88) tells us how much change a unit pH change causes in ∑∆Q and we know that if we have 60.1 mEq too much charge at pH 5.3 we ought to try a pH higher by that charge divided by dQ/dPH. This is in Cell C89. Compare this to B89. Pretty close but not quite on. Those who stayed awake in their high school math classe will recognize the genius of Sir Isaac at work here and so we have labeled C89 Newton 1st Step.
In column E we try again with our new estimate of 5.49550353 and find ∑∆Q = 5.01. Still not 0 but closer to 0. Again we find dQ/dpH and note that it is now 258 vs 307 mEq/pH. These numbers are, of course, the buffering of the mash at, respectively, pH 5.3 and 5.495. So much for those who think this problem is linear! Proceeding as above we get a new estimate of pHz = 5.5149. Close enough to 5.5151 to satisfy most but as we are quark chasers we take one more step to get ∑∆Q down to 0.04 and a pH estimate identical to the Solver solution down to the 7th decimal place.

Were someone to use Newton's method in a practical spreadheet he would, of course, hide all these extra columns on a back page or make them invisible on a main page. And, of course, if he wanted to be able to solve for the amount of lactic acid for a given pH he'd have to figure out some logic for that.

Pardon me for being dense AJ, but in the grand scheme of things, how does using the Newton method help us? How does it relate to trying to model the co-efficiencts used in getting Q malt values? Most importantly, how do we apply this analysis.

When we go to code this thing, we will need to adapt a grain bill section to the calculations in the troubleshooter. I’m not the sharpest tool in the shed but I am good at applying concepts in Excel.

 

[Silver_Is_Money]

A general observation on my part: Since this thread initially identified the major faults of many (to most, and perhaps all, sans for A.J.'s solver/tester, which likely would not qualify as a tool in general use by the public) existing mash pH prediction software packages, and squashed them (for some of us at least, due to the validity of my thought experiment with respect the varying of DI water volume, grist weight, and mineralization levels) it seems to have greatly migrated from swatting such softwares flawed atom bombs to swatting its much smaller flawed gnats.

 

[ajdelange]

Pardon me for being dense AJ, but in the grand scheme of things, how does using the Newton method help us?

Given good malt data (which I'll get back to) estimates of mash pH, solidly based on the science we have (the alkalinity of water itself is not ignored, bicarbonate is not used as a proxy for alkalinity, the pH of source water is considered, the end point titration pH used by the lab in reporting alkalinity has been considered, the non linear problem has not been represented by a linear approximation) are obtained by computing ∑∆Q(pHz) and finding the value of pHz resulting in ∑∆Q(pHz) = 0. We can do this in several ways:
1)We can grope for an answer by entering value after value for pHz until we get one that gives us ∑∆Q(pHz) close to 0
2)We can search more intelligently (bisection technique) for that pHz value
3)We can set up the Solver to do the search automatically
4)We can use Newton's method

From the user's POV 3) and 4) are obviously preferable as they give the answer automatically. 4) may be slightly preferrable to 3) in the sense that as soon as you change any input parameter the new pHz estimate pops up (in the Newton 3rd step cell in the troubleshooter/voltmeter spreadsheet). With 3) either the Solver has to be set up manually or a macro radio button has to be pressed but the manual case has the advantage that you can ask Solver to adjust any parameter to satisfy any condition. For example one might well want to use it to determine how much lactic acid should be added to set a particular mash pH. Of course we could have several radio buttons one labeled "Calculate pH", another "Calculate acid addition" a third labeled "Calculate sauermalz" a 4th labeled "Calculate Malt 2" etc. This might be the most convenient. OTOH having the Newton's method solution always on the screen does not preclude the use of the Solver, by macro or manually, for other parameters.

How does it relate to trying to model the co-efficiencts used in getting Q malt values?

It doesn't. It only gives an alternative means for finding the solution given that you have coefficients to put into the malt models. I cooked it up only because it never occurred to me, until I was typing the post on it, that one could invoke the Solver through a macro. It may yet have some value in that it is nice to see immediately (i.e. without having even to press a radio button) what any change to an input parameter does to pHz.

Most importantly, how do we apply this analysis.

It isn't really an analysis but rather an algorithm for finding pHz.

When we go to code this thing, we will need to adapt a grain bill section to the calculations in the troubleshooter.

Yes, and that's where the challenges really lie, IMO. The model ∆Qmalt = a*(pHz - pHDI) + b*(pHz - pHDI)^2 + c*(pHz - pHDI)^3 is a good model for any malt that I can imagine we will ever encounter (and if it isn't we can always add a 4th term). I'll note that in my correspondence with Joe Walts he preferred ∆Qmalt = u + v*pHz + w*pHz^2 (I think because his curve fitting routine didn't allow offset) and all the data he gave me was based on that which is an acceptable model but not as convenient and obscures pHDI but pHDI, a and b can be calculated from u, v and w. Kai measured pHDI and the amount of acid required to get to pHref (I don't remember what pHref was). It is possible to deduce a from his measurements. Thus I'll assert that any reasonable set of malt measurements can be converted into the parameters set (pHDI, a, b, c) though you may have to use 0 for c and b as it takes at least n measurements to get n parameter values. Thus we will have to agree on a model, and I'll advocate strongly for ∆Qmalt = a*(pHz - pHDI) + b*(pHz - pHDI)^2 + c*(pHz - pHDI)^3, and then convert any available malt measurements to (pHDI, a, b, c).

I’m not the sharpest tool in the shed but I am good at applying concepts in Excel.

Don't sell yourself short! After years of trying to get people to appreciate the power of this method you guys here are the first to have shown any level of understanding of it and what it can potentially do.

 

[probablynotnick]

Exciting stuff, gentlemen!

I admit that I am really only able to grasp the general concept of what is going on here, but I am eager to see where this all leads. A new tool is in reach that will bring more accurate prediction to what has always been empirical and "gut check". I would hope that at least the bigger maltsters could be encouraged to provide more data, as you are developing a tool to better utilize their product after all!

If I was more clever, I'd contribute. Instead I'll read intently

 

[ajdelange]

No matter how good the calculator it won't solve the GIGO problem and that's where the maltsters come in. Success really depends on them. I'm too old to mount a campaign to get them involved. I did make a feeble attempt a few years ago but I was, even then, a back room boffin - not a campaigner.

 

[Big Monk]

No matter how good the calculator it won't solve the GIGO problem and that's where the maltsters come in. Success really depends on them. I'm too old to mount a campaign to get them involved. I did make a feeble attempt a few years ago but I was, even then, a back room boffin - not a campaigner.

I think Weyermann would be a good start. They already have the most comprehensive and traceable MA program out there. It may not be a stretch to reach out to them and inquire the work involve in running the pH DI, a, b, and c analysis for each lot. The worst they could say is that it’s too much work and would hog resources otherwise used in other aspects of the malt house.

 

[Big Monk]

Thus we will have to agree on a model, and I'll advocate strongly for ∆Qmalt = a*(pHz - pHDI) + b*(pHz - pHDI)^2 + c*(pHz - pHDI)^3, and then convert any available malt measurements to (pHDI, a, b, c).

Don't sell yourself short! After years of trying to get people to appreciate the power of this method you guys here are the first to have shown any level of understanding of it and what it can potentially do.

I’m with you on your choice for the model. The difficulty comes in trying to get b and c values (even pH DI and a valued for that matter, even though I think we have a prelim data set that would be enough to improve on current models) and implementing it in a way that we wouldn’t scare off 99.9% of the average homebrewers.

Right now I have an active model that uses a rough draft integration of the charge method into a brewing sheet that includes recipe input. I currently am only using a linear model with pH DI and approximations of the a co-efficient based on your measurements and those others documented in Riffe’s pH III paper. I have also integrated the macro button in for solver and finding pHz based on inputs.

 

[ajdelange]

The difficulty comes in trying to get b and c values ... and implementing it in a way that we wouldn’t scare off 99.9% of the average homebrewers.

I think I know how to do that. You have, on a back page, a table with one column containing malt names, the adjacent one pHDI's the next a's. the next b's and so on. The names are reproduced on a visible page with a "malt code number" next to each. If the brewer wants to use Weyermann's floor pils he checks in the malt code column to find the code for that malt and then enters it near where he enters the amount of malt. VLOOKUP is then used to get the parameters for that malt which are used in the Q calculation. That could, and probably should, be done off the main page too. In fact probably all the "engine" calculations should be done out of view. The casual user need know nothing more about the malt models than does the user of one of the current popular spreadsheets.

Users in the know would be given instructions on what the back pages do and what they can tell them.

 

[Big Monk]

I think I know how to do that. You have, on a back page, a table with one column containing malt names, the adjacent one pHDI's the next a's. the next b's and so on. The names are reproduced on a visible page with a "malt code number" next to each. If the brewer wants to use Weyermann's floor pils he checks in the malt code column to find the code for that malt and then enters it near where he enters the amount of malt. VLOOKUP is then used to get the parameters for that malt which are used in the Q calculation. That could, and probably should, be done off the main page too. In fact probably all the "engine" calculations should be done out of view. The casual user need know nothing more about the malt models than does the user of one of the current popular spreadsheets.

Users in the know would be given instructions on what the back pages do and what they can tell them.

That’s what I’ve always done in all my sheets for pulling values out of my grain and hop database. It’s my SOP for pulling from backpage databases.

I’m enough of an Excel wiz that I’ll make it work.

 

[ajdelange]

I'm enough of an Excel "wiz" to know what VLOOKUP is but that's about the extent of it!

 

[Silver_Is_Money]

I've graduated from VLOOKUP to Index/Match, but I still fumble with it a bit, and occasionally I have to reinvent the Index/Match wheel. I blame it on getting old.

 

[Big Monk]

I've graduated from VLOOKUP to Index/Match, but I still fumble with it a bit, and occasionally I have o reinvent the Index/Match wheel. I blame it on getting old.

Index/Match is my preferred (if not THE preferred) lookup function. The reason being that it handles the tabular databases I use in all my brewing sheets, is a bit more dynamic than V and H lookup and, from what I’ve read, is faster as well.

 

[Big Monk]

EDIT: Double post.

 

[Silver_Is_Money]

I'd like to revisit one last time the mineralization issue.

A.J., if a certain minimum quantity of added Calcium ions are required to react with scenario #1's 12.5 lbs. of grist and its phosphates in order to precipitate them out as calcium phosphates (if a certain ppm or mEq/L value is introduced into the mash water, leading to a quantified mEq value) in a reaction which liberates H+ ions and thereby to some degree acidifies the mash water, then would not fully twice that minimum quantity of Calcium ions be required to be present for scenario #2, whereby the only change in the mash parameters has been the doubling of the grist weight to 25 lbs., in order to precipitate out as calcium phosphates the doubled quantity of phosphates present within what is now 25 lbs. of grist?

And would not this De-Facto invalidate multiple decades of overly simplistic thinking in regard to a mere 40-50 ppm of calcium in the mash water being a "one ppms value of calcium fits all mash scenarios" mode of thought, and replace it with a requisite minimum ratio of calcium ion mEq's to grist weight (a minimum mEq_Ca++/grist_weight ratio)?

 

[Big Monk]

I'd like to revisit one last time the mineralization issue.

A.J., if a certain minimum quantity of added Calcium ions are required to react with scenario #1's 12.5 lbs. of grist and its phosphates in order to precipitate them out as calcium phosphates (if a certain ppm or mEq/L value is introduced into the mash water, leading to a quantified mEq value) in a reaction which liberates H+ ions and thereby to some degree acidifies the mash water, then would not fully twice that minimum quantity of Calcium ions be required to be present for scenario #2, whereby the only change in the mash parameters has been the doubling of the grist weight to 25 lbs., in order to precipitate out as calcium phosphates the doubled quantity of phosphates present within what is now 25 lbs. of grist?

And would not this De-Facto invalidate multiple decades of overly simplistic thinking in regard to a mere 40-50 ppm of calcium in the mash water being a "one ppms value of calcium fits all mash scenarios" mode of thought, and replace it with a requisite minimum ratio of calcium ion mEq's to grist weight (a minimum mEq_Ca++/grist_weight ratio)?

The problem with your thought process is Calcium isn’t required at all and for some time now people, especially at our site where we informally advocate a simplification of brewing water composition, have been using only enough calcium to hit between 30-40 ppm, mostly out of a desire to get a certain amount of sulfate and chloride without adding magnesium and sodium.

Given the alternatives, people would rather add calcium salts to get sulfate and chloride rather than other options.

I find your line of thought interesting but I can’t see how it matters in a practical sense other than advocating that the 40-50 ppm mark is a landmark on the horizon. However, I think you’ll find that many people advocate that calcium isn’t really even technically required in the mash, and most only add it for the fringe benefits.

 

[Silver_Is_Money]

The problem with your thought process is Calcium isn’t required at all and for some time now people, especially at our site where we informally advocate a simplification of brewing water composition, have been using only enough calcium to hit between 30-40 ppm, mostly out of a desire to get a certain amount of sulfate and chloride without adding magnesium and sodium.

Given the alternatives, people would rather add calcium salts to get sulfate and chloride rather than other options.

I find your line of thought interesting but I can’t see how it matters in a practical sense other than advocating that the 40-50 ppm mark is a landmark on the horizon. However, I think you’ll find that many people advocate that calcium isn’t really even technically required in the mash, and most only add it for the fringe benefits.

If in fact the only reason to add minerals is flavor, you are likely completely correct here. My own doubt is what drove me to re-visit this matter and make my last post. But are you saying that there is no net benefit in precipitating out any level of present malt phosphates, and therefore the only benefit is flavoring?

 

[Big Monk]

If in fact the only reason to add minerals is flavor, you are likely completely correct here. My own doubt is what drove me to re-visit this matter and make my last post. But are you saying that there is no net benefit in precipitating out any level of present malt phosphates, and therefore the only benefit is flavoring?

No that’s not what I’m saying.

In general there are a number of fringe benefits to having calcium present including adding the flavor ions attached to the common calcium salts (CaCl, Gypsum), slight pH drop, the oft quoted beer stone removal properties and aiding in yeast flocculation.

What I am saying is, of all those fringe benefits, the most important benefit, IMHO, is flavor. All the others things on that list of benefits can be accomplished by something else if required.

 

[Silver_Is_Money]

Perhaps the case is that very little calcium is required to be present to precipitate out any phosphates which would have a negative impact on flavor, and the rest is desired to be present only due to its necessity as the means to introduce Cl- and SO4-- flavor ions.

 

[ajdelange]

A.J., if a certain minimum quantity of added Calcium ions are required to react with scenario #1's 12.5 lbs. of grist and its phosphates in order to precipitate them out as calcium phosphates (if a certain ppm or mEq/L value is introduced into the mash water, leading to a quantified mEq value) in a reaction which liberates H+ ions and thereby to some degree acidifies the mash water, then would not fully twice that minimum quantity of Calcium ions be required to be present for scenario #2, whereby the only change in the mash parameters has been the doubling of the grist weight to 25 lbs., in order to precipitate out as calcium phosphates the doubled quantity of phosphates present within what is now 25 lbs. of grist?

Just the opposite in fact. Assuming 10Ca++ +6PO4--- +2H20 ---> Ca10(PO4)6(OH)2 + 2H+ to be the governing equation the concentration of calcium required for precipitation is [Ca++] = ([Ksp/[PO4]^6)^(1/10). If you double the PO4 then the calcium concentration at which precipitation starts is 2^(-6/10) = 0.659754 times as high as it is with the original phosphate content. This means not only that phosphate precipitation can occur with less calcium originaly present but that when it does occur it will occur down to a level 66% of what it was in scenario 1 (phosphate is always assumed to be in excess). This is the same concept that is behind the trick we use of adding calcium salts to brewing water before boiling to get more alkalinity out.

And would not this De-Facto invalidate multiple decades of overly simplistic thinking in regard to a mere 40-50 ppm of calcium in the mash water being a "one ppms value of calcium fits all mash scenarios" mode of thought, and replace it with a requisite minimum ratio of calcium ion mEq's to grist weight (a minimum mEq_Ca++/grist_weight ratio)?

The 50 ppm calcium is a rule of thumb recognized as such by brewers who should understand that is should be interpreted as "in general you should have 50 ppm calcium even though you know that some very fine beers are made with much less than that."

 

[Silver_Is_Money]

Is calcium also beneficial in haze reduction?

 

[Big Monk]

Is calcium also beneficial in haze reduction?

Supposedly, but so is hot and cold break removal. [emoji6]

 

[ajdelange]

One of the benefits of calcium is capture of oxalate. You want that to happen in your ferementer - not your kidneys.

 

[mabrungard]

Malt provides all the calcium that yeast need for their metabolism...just like malt provides all the magnesium the yeast need. However, there are benefits in having either calcium and magnesium in the brewing liquor. I agree with AJ that dropping out oxalate is a very good reason. Others are improved heat resistance for mash enzymes and increased yeast flocculation. Including some calcium in the MASH is a good thing. Adding all your calcium salts to the mashing water means that you can temporarily boost the mash's calcium content to help drop the oxalate content in the wort. The subsequent dilution with low TDS sparging water does enable lager brewers to achieve lower calcium levels in the kettle.

This particular discussion is humorous to me since any discussion of brewing with low calcium content seems to be sacrilege to English brewers. I've encountered brewers on British homebrewing forums that claimed I was a total fool for suggesting that modest calcium levels might produce better beer for them. Having seen water reports for some areas in England, I can understand why they might be used to 'minerally' flavor in beer.

PS: the mash thickness/pH issue with Bru'n Water has been corrected. Thanks for the prodding.

 

[SteveH aka shetc]

PS: the mash thickness/pH issue with Bru'n Water has been corrected. Thanks for the prodding.

Will there be a new pro version as well?

 

[Silver_Is_Money]

PS: the mash thickness/pH issue with Bru'n Water has been corrected. Thanks for the prodding.

Excellent news! Will the emailing of the paid version commence soon?

 

[ajdelange]

At this point it has been verified that one can invoke Solver through a macro and thus I think the fundamental problem is solved.
This doesn't mean, of course, that there isn't still lots of work to do. Developers will need to figure out how to hide all the highly technical stuff from the users, for example.

I spent the day fiddling with Excel and discovered a lot of the neat stuff you can do with it. In working with the spreadsheet I post here I was able to get rid of 3 columns and all those r's and f's by writing functions that hide all that stuff from the user and other functions that solve for the lactic acid, phosphoric acid and the estimated mash pH, in all its non linear glory, by using Newton's method. Much faster than the Solver too.

 

[Silver_Is_Money]

I spent the day fiddling with Excel and discovered a lot of the neat stuff you can do with it. In working with the spreadsheet I post here I was able to get rid of 3 columns and all those r's and f's by writing functions that hide all that stuff from the user and other functions that solve for the lactic acid, phosphoric acid and the estimated mash pH, in all its non linear glory, by using Newton's method. Much faster than the Solver too.

But does it still use 'Solver'?

 

[mabrungard]

AJ is programming VBA Functions. It does not use solver. The only question I have is if the inclusion of VBA code into Excel makes it less compatible with a wide array of computing platforms.

 

[sixhotdogneck]

Newton-Raphson is an algorithm that can be used in place of solver.

The book "Numerical Recipes in C" contains implementations useful in the context given in this thread. (or Fortran)

Spreadsheets proper (Excel, OpenOffice, LibreOffice) are software, but the individual spreadsheets within them are *not* software, they're simply spreadsheets (rows and columns of numbers/formula/built in functions run by the underlying spreadsheet software) most of which have absolutely awful user interfaces (due to being a spreadsheet).

It would be best if a proper software library was coded (with classes, types, structures, functions etc...) to represent the chemical reactions taking place (proton surfeit/defecit) and the water calculations (dilution, combination, etc).

This would allow implementation of genetic algorithms (which would produce lists of ideal results that one could choose from) from various user inputs that simply cannot be implemented in simplistic spreadsheets. Solver type algorithms are useful for min/max/optimal value type solutions but definitely do not compare to genetic algorithms.

It seems Mr. Delange has a spreadsheet on his website and several posts with these algorithms on this forum and his website which could be used to implement such software.

 

[sixhotdogneck]

Such a software library would allow for very elegant solutions.

For example, a class "Water" could be developed with a "+" operator which would allow one to combine waters:

Water w1 = new Water(8.3pH, 40ppm Ca, 0ppm Mg, 5ppm Na, 120ppm Cl, 47ppm SO4, 45ppm HCO3);
Water w2 = new Water(7.6pH, 32ppm Ca, 2ppm Mg, 11ppm Na, 95ppm Cl, 37ppm SO4, 0ppm HCO3);
Water w3 = w1 + w2;

A mash class would allow for very elegant mash pH predictions and programming:

Mash m = new Mash();
m.Waters.Add(w3);
m.Grains.Add(new Grain(1.8L, 5.67 DI pH, 3.45 a, 5 b, 9 c));
Print(m.MashPH);

Many things could be done with this, for example one could make a Mash Interpreter in which these statements could be input directly and output immediately as text. Or make a friendly user interface to allow data input and output in an elegant fashion.

One could also write fancy genetic algorithms to "find" the ideal waters and/or salt additions for certain grain bills... many possibilities.

I suppose one could even write an algorithm to "generate recipes" based on various parameters.

Of course these things require everyone to put aside their pride ("my" spreadsheet, "my" algorithm) and place effort and energy into the construction of the library.