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

[Big Monk]

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 ***** to find so i wouldn't put it past Excel to be hiding some junk.

Anyway, here is a screenshot:

I just got super dangerous as well because I found out you can set a VBA button up to execute solver commands...

 

[ajdelange]

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.

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.

 

[Silver_Is_Money]

Try the link now and see what happens.

Works now! Thanks!!!

 

[Big Monk]

Karl Siebert

From Cornell?

 

[ajdelange]

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.

 

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

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.

 

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

 

[Big Monk]

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:

Obviously the next step is digesting your last post and setting up background data sheet. Stay tuned...

 

[Silver_Is_Money]

RPIScotty, is the above available for download yet?

 

[Big Monk]

RPIScotty, is the above available for download yet?

It has macros, so i'm not sure you can use it, but give it a try:

See Below for Link

We are in the land of charge now and it's glorious. Solver is the coolest thing I have ever used in Excel.

 

[Silver_Is_Money]

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

 

[Silver_Is_Money]

It has macros, so i'm not sure you can use it, but give it a try:

http://www.********************/wp-content/uploads/2018/07/Brewing-Sheet-v1.1.xlsm

We are in the land of charge now and it's glorious. Solver is the coolest thing I have ever used in Excel.

LibreOffice Calc can generally handle Excel macros at some levels, so I'll download it and try to see if it works.

 

[Big Monk]

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.

 

[Silver_Is_Money]

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.

 

[Big Monk]

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?

 

[Silver_Is_Money]

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"

 

[schematix]

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.

 

[Big Monk]

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.

 

[Big Monk]

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?

 

[Silver_Is_Money]

Does Libre have solver?

Yes!

 

[Big Monk]

Yes!

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.

 

[Silver_Is_Money]

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.

 

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

AJ,

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

 

[ajdelange]

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.

 

[ajdelange]

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

 

[Big Monk]

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.********************/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.

 

[Big Monk]

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

 

[ajdelange]

∆Q Carbonic' =B22*B81*B39 =C22*C81*C39 =D22*D81*D39 =E22*E81*E39 =F22*F81*F39 =G22*G81*G39 =H22*H81*H39

 

[Big Monk]

∆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?

 

[ajdelange]

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.

 

[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!