• Please visit and share your knowledge at our sister communities:
  • If you have not, please join our official Homebrewing Facebook Group!

    Homebrewing Facebook Group

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

Homebrew Talk

Help Support Homebrew Talk:

This site may earn a commission from merchant affiliate links, including eBay, Amazon, and others.
I tried to run it in LibreOffice Calc 6.0, and it fails with a statement that says something (from memory) loosely akin to: This spreadsheet contains links to other files, but they are not available. Not having Excel, I may be out of luck here.

Try the link now and see what happens. I don't see any external links but I've had old deleted dropdowns keep references to other sheets that are a bitch to find so i wouldn't put it past Excel to be hiding some junk.

Anyway, here is a screenshot:

Capture.JPG


I just got super dangerous as well because I found out you can set a VBA button up to execute solver commands...
 
However, if one was willing to integrate the use of the solver into their spreadsheet (which i'm considering), then you could use a linear form of AJ's equation:

Q (mEq) = ∑ Bg/kg * (pHz - pH DI) (2)
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.
Untitled20.jpeg

Untitled21.jpeg

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.
 
The very same.

I don't go to many brewing conferences but I will say that I have never been at one (including ones in Canada and Belgium) at which he wasn't present.
 
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.

I think I am following but you do leave me in the dust sometimes!

If you get a chance AJ, take a look at the file in #238. When I linear, that's what I mean. I used the following:

∆Q = a * (pHz - pH DI)
∆Q = ∑ Bg/kg * (pHz - ∑ pH DI/kg) and used the solver to zero out Q with pHz

∑ Bg/kg = Weighted Sum of Buffering capacity for each malt (is this a suitable stand in for co-efficient a

∑ pH DI/kg = Weighted Sum of base malt pH DI values

I guess my big question would be if the Newton method you describe above could get me approximations for b and c, which is how I read your post but I admittedly am a little lost on the implementation.
 
Last edited:
At this point it has been verified that one can invoke Solver through a macro and thus I think the fundamental problem is solved. Given this I can't emphasize too strongly that linearized approaches should be avoided. There is only one reason to linearize a non linear problem and that is to make it solveable when non linear solutions are not available. That is no longer the case here. The non linear solution is available and should be used thus avoiding the problems (inaccuracies) that linearization imposes.

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.
 
At this point it has been verified that one can invoke Solver through a macro and thus I think the fundamental problem is solved. Given this I can't emphasize too strongly that linearized approaches should be avoided. There is only one reason to linearize a non linear problem and that is to make it solveable when non linear solutions are not available. That is no longer the case here. The non linear solution is available and should be used thus avoiding the problems (inaccuracies) that linearization imposes.

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.

So as of right now I have my solver function tied to a macro with a button:

Capture.JPG


Obviously the next step is digesting your last post and setting up background data sheet. Stay tuned...
 
I'm not following. Are you talking about a log function somewhere in the calculation? Equation 2 above is just for the malt portion and derives directly from AJ's troubleshooter. It doesn't take into account any of the acid inputs or mineral inputs. Equation 1 is a modified version of Riffe's Grist pH calculation that substitutes a generic buffering capacity value for a weighted average of all the individual malt's buffering capacities and a weighted average of all the base malt pH DI values.

I'm not sure where log fits into any of it.

Precisely my point. pH is log base 10 scaled system. I guess my point is to question the validity of any attempt to either derive acidity from, or to compute resulting pH's for a blended grist, when using equations which do not utilize log base 10, and to me it doesn't mater in the least who's name is behind the equation(s).
 
Last edited:
Precisely my point. pH is log base 10 scaled system. I guess my point is to question the validity of any attempt to either derive acidity from, or to compute resulting pH's for a blended grist, when using equations which do not utilize log base 10, and to me it doesn't mater in the least who's name is behind to the equation.

At this point, I've moved beyond inferring acidity from other variables. I am using empirically derived buffering values for the malts, pH DI values straight from the maltster averaged over 3 years worth of lots, and plugging it all into the the sheet and using solver just like the troubleshooter AJ put together.

I've moved past the Equation 1 from post #226. It's not even on my radar anymore.

I'm firmly committed to:

Q Malt (mEq) = a * (pHz - pH DI) plus Q terms for Lactic, Sauermalz, and Minerals as laid out in AJ's troubleshooter.
 
Update: I changed your grist to 90% Pilsner and 10% CaraMunich II, and the mash pH as seen in the lower right corner remained at 5.724, so I guess it isn't working in LibreOffice. Libre did not bark at me when I told it to turn on macros though.
 
Update: I changed your grist to 90% Pilsner and 10% CaraMunich II, and the mash pH as seen in the lower right corner remained at 5.724, so I guess it isn't working in LibreOffice. Libre did not bark at me when I told it to turn on macros though.

Did you use the Calculate Mash pH macro button?
 
I clicked on it, but nothing happened. It is merely a white box in LibreOffice Calc. No text within the box.

What is the name assigned to this particular macro?

Edit, found it among the macros, and ran it, and it kicked out with the following error:

"Basic Runtime error '35' SolverOK"
 
Last edited:
You may be able to tie a change in those input value fields to trigger re-execution of the solver as well. It's a brute force approach though.

You put your solve code in a sub, and then each value field change method would call the sub.
 
You may be able to tie a change in those input value fields to trigger re-execution of the solver as well. It's a brute force approach though.

You put your solve code in a sub, and then each value field change method would call the sub.

I'm really only concerned with execution of the Mash pH calc so right now it's working fine.
 
I clicked on it, but nothing happened. It is merely a white box in LibreOffice Calc. No text within the box.

What is the name assigned to this particular macro?

Edit, found it among the macros, and ran it, and it kicked out with the following error:

"Basic Runtime error '35' SolverOK"

Does Libre have solver?
 
I know that in Excel you have to reference solver to the VBA code. Either way, you can still manually run the solver if the Macro doesnt work.

I think Libre is choking on line 9 of the macro. Gotta run for now though (me that is, not the macro).... Duty call from my wife.
 
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.

AJ,

Can you provide the code for A79:A90 and B79:B90?
 
Take the dog for a walk and I'm dozens pf posts behind!

I think I am following but you do leave me in the dust sometimes!

I'm being a bit more casual about detailed explanations as you can get them from playing with the Voltmeter spreadsheet which contains all the formulas. As I said earlier, understand what Henderson - Hasselbalch says and observe how it is implemented in the spreadsheet and understand that the changes in charges on the things that give up protons must equal the change in charges on the things that absorb them and you understand brewing water chemistry.

This does NOT mean that you should hesitate to ask questions if you have them.


I guess my big question would be if the Newton method you describe above could get me approximations for b and c, which is how I read your post but I admittedly am a little lost on the implementation.

Newton's method (which you must have seen before) does nothing but what the Solver does and that is to find the value of pHz which causes ∑∆Q(pHz) to be 0. Look it up on Wikipedia and look especially at the animation.




If you get a chance AJ, take a look at the file in #238. When I linear, that's what I mean. I used the following:

∆Q = a * (pHz - pH DI)
∆Q = ∑ Bg/kg * (pHz - ∑ pH DI/kg) and used the solver to zero out Q with pHz

∑ Bg/kg = Weighted Sum of Buffering capacity for each malt (is this a suitable stand in for co-efficient a

∑ pH DI/kg = Weighted Sum of base malt pH DI values
If you are going to take the trouble to compute Q's and use the Solver there is, given the demonstrated simplicity of doing so, no reason that I can see not to use the robust formulas for ∆Q. For malt this is

∆Q = ∑_i ai * mi* (pHz - pH DI) +∑_i bi * mi *(pHz - pH DI)^2 + ∑_i ci * mi*(pHz - pH DI)^3

= ∑_i ai * mi* pHz - ∑_i ai * mi*pHDI + ∑_i bi * mi *pHz^2 - 2*∑_i bi * mi *pHzI*pHDI + ∑_i bi * mi *pHDI^2 + ...

= ∑_i mi*(pHz*∑_i ai * mi/∑_i mi) - ∑_i mi*(∑_i ai * mi*pHDI/∑_i mi) +∑_i mi*( pHz^2*∑_i bi * mi/∑_i mi) + ...

In the last line each term in parentheses is the weighted average of something but I don't really see the point of calculating things that way. Also note that the weights are the products of the masses and the first buffering coefficient.


∑ Bg/kg = Weighted Sum of Buffering capacity for each malt (is this a suitable stand in for co-efficient a

In cases where you have data derived from only two titration points (Kai's measurements) or where you are trying to model based on color and haven't the temerity to estimate the titration curves curvature then if you can show that ∆Q = ∑ Bg/kg * (pHz - ∑ pH DI/kg) = ∑_i ai * mi* pHz - ∑_i ai * mi*pHDI yes. Otherwise, no. In the case where you have higher order data such as Joe Walts or mine, no.
 
AJ,

Can you provide the code for A79:A90 and B79:B90?

Surely. Col. A
Grand Total

Carbonic Acid mmol/L
mg/L carbonic acid
New Ct
New Alk
New alk ppm
pACo2


pHzs

Col B.
=B77+B78

0
=B81*62
=B61+B81+B9/84.01
=B83*(B32-B39) +(B56-B57)
=50*B84
=(B83*B36/1000)/(10^-1.41)

=B81*62
=B14
Solver Answer
Col. C
=C77+C78

0
=C81*62
=C61+C81+C9/84.01
=C83*(C32-C39) +(C56-C57)
=50*C84
=(C83*C36/1000)/(10^-1.41)

=(D79-C79)/(D14-C14)
=C14-C79/C88
Newton 1st Step
Col. D
=D77+D78

0
=D81*62
=D61+D81+D9/84.01
=D83*(D32-D39) +(D56-D57)
=50*D84
=(D83*D36/1000)/(10^-1.41)

dQ/pH
 
I changed some stuff around and had to fix an error in how I was handling malt. The sheet now calculates individual Q malt (mEq/l) values for each malt and then sums them before calculating Q malt (mEq) values. I used a mix of reported pH DI values for base malts (my 3 year averages from Weyermann) and measured values from Riffe's Mash pH III paper. For buffering values, I used a mix of the reported values from Riffe's Mash pH III paper.

http://www.lowoxygenbrewing.com/wp-content/uploads/2018/0Brewing-Sheet-v1.1.xlsm

Before the fixes I was getting no change between pHz and pH DI. Obviously something was wrong and I fixed it up.

It should be noted that with the exception of the NY 2-Row and Special B, all the malts in this sheet are Weyermann brand.
 
Last edited:
Surely. Col. A
Grand Total

Carbonic Acid mmol/L
mg/L carbonic acid
New Ct
New Alk
New alk ppm
pACo2


pHzs

Col B.
=B77+B78

0
=B81*62
=B61+B81+B9/84.01
=B83*(B32-B39) +(B56-B57)
=50*B84
=(B83*B36/1000)/(10^-1.41)

=B81*62
=B14
Solver Answer
Col. C
=C77+C78

0
=C81*62
=C61+C81+C9/84.01
=C83*(C32-C39) +(C56-C57)
=50*C84
=(C83*C36/1000)/(10^-1.41)

=(D79-C79)/(D14-C14)
=C14-C79/C88
Newton 1st Step
Col. D
=D77+D78

0
=D81*62
=D61+D81+D9/84.01
=D83*(D32-D39) +(D56-D57)
=50*D84
=(D83*D36/1000)/(10^-1.41)

dQ/pH

Not to be a pain in the butt, but can you report that with row 78 too? I don't seem to have the delta carbonic row in my sheet
 
∆Q Carbonic' =B22*B81*B39 =C22*C81*C39 =D22*D81*D39 =E22*E81*E39 =F22*F81*F39 =G22*G81*G39 =H22*H81*H39

Thanks AJ. I’m working up a formatted version of a master copy of the troubleshooter.

One other question: Is the water portion of the troubleshooter required if we are assuming distilled water? What does it bring to the table so to speak?
 
Last edited:
Thanks AJ. I’m working up a formatted version of a master copy of the troubleshooter.

One other question: Is the water portion of the troubleshooter required if we are assuming distilled water? What does it bring to the table so to speak?
What it brings to the table is the charge change on water molecule themselves. With DI water, as we have seen, the charge differences are small as the alkalinity of pure water to mash pH is only 1 or 2 ppm as CaCO3. Nut that is 2 - 4% of the alkalinity of water with measured alkalinity of 50 ppm.

Notr that the latest version allows for not only distilled/RO water bu water with arbitrary alkalinity and hardness.

My attitude here is the same as with the malt: it is so easy to do it right there is really no reason to do it any other way.
 

Latest posts

Back
Top