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

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

 

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

 

[ScrewyBrewer]

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

And your contribution to this project would be...?

 

[passedpawn]

And your contribution to this project would be...?

He discussed on topic. That is enough.

 

[ajdelange]

But does it still use 'Solver'?

It does not use the Solver to determine what the mash pH estimate is or how much lactic acid is needed to hit pH 5.4 with a particular grain bill and water parameters. But it still can be used if, for example you want to hit a particular pH in a grain bill in which crystal malt is 10 percent of the total and sauermalz no more than 3%.

 

[ajdelange]

AJ is programming VBA Functions.

That is correct.

It does not use solver.

It does not use Solver to solve the unconstrained problems of finding mash pH and the amounts of acid or base to add to realize a given pH. As such it will blithely estimate a mash pH of -7 if the math drives it that way. If you try to add acid to a recipe that is already acidic it won't let you and will admonish you to add base instead. Nut please remember that I am, at this point, only playing around with new toys. The Solver can and should be used to Solve, for example, constrained problems (e.g. want no more than x% sauermalz).

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.

Another question with VBA is security. At one time Microsoft removed it from Excel because of security concerns. One can still save spreadsheets in formats that will not run macros.

 

[ajdelange]

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

It can be used in place of the Solver for certain types of problems like the one of finding mash pH. Yes, Q is a non linear function of pH but it is a monotonic one so that there is only a single root. This is ideal for Newton. Other classes of problems it cannot do and so is not a substitute for Solver. Solver can handle constrained problems and multidimensional ones. Newton can handle multidimensional ones too (e.g. finding the set of salt additions that best match and ion profile) but I can't see doing those in Excel (other than with Solver) because it cannot, AFAIK, invert a non square matrix.

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

Numerical Recipes in FORTRAN is probably the most useful book I ever owned but one hardly needs anything in it for this application. Sure it's got Newton Raphson and Root Bisection but these are trivial algorithms compared to much of what's in that book. For example the code for finding mash pH is

Function FindpHz(pH0 As Double) As Double
'Find pH that 0's pHz starting from an initial guess at pH0

Dim dQdpH As Double
Dim QpH0 As Double
Dim Corr As Double
Corr = 0.1

Do Until Abs(Corr) < 0.0001
QpH0 = CalcQ(pH0)
dQdpH = (CalcQ(pH0 + 1e-07) - QpH0) / 1e-07
Corr = -QpH0 / dQdpH
pH0 = pH0 + Corr
Loop

FindpHz = pH0

End Function

That's it!

Of course we could take the SVD code from Recipes and translate it into VBA thus allowing us to invert matrices...

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

The chemistry here is really simple. Proton deficits and surfeits for any acid, weak or strong, mono or polyprotic, can be had from

Function QAcid(pH As Double, pK1 As Double, Optional pK2 As Double = 60, Optional pK3 As Double = 60, Optional pK4 As Double = 60) As Double
Dim r1 As Double
Dim r2 As Double
Dim r3 As Double
Dim r4 As Double
Dim f0 As Double
Dim f1 As Double
Dim f2 As Double
Dim f3 As Double
Dim f4 As Double

r1 = 10 ^ (pH - pK1)
r2 = 10 ^ (pH - pK2)
r3 = 10 ^ (pH - pK3)
r4 = 10 ^ (pH - pK4)

f0 = 1 / (1 + r1 + r1 * r2 + r1 * r2 * r3 + r1 * r2 * r3 * r4)
f1 = f0 * r1
f2 = f1 * r2
f3 = f2 * r3
f4 = f3 * r4

QAcid = -(f1 + 2 * f2 + 3 * f3 + 4 * f4)
End Function

Another, I think you'll agree, pretty simple bit of code. The challenge and where the heavy lifting has to be done is in organizing this handful of simple functions into classes (or whatever the jargon of OOP is) and merged with objects that provide a simple, understandable and friendly user interface.

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.

It's not on my website. There is one there that is so primitive I ginned it up while waiting for my turn to speak at an MBAA meeting. But you can use it to get answers using the Solver. This stuff I'm posting about today was cooked up today so it isn't exactly time tested.

 

[ajdelange]

He discussed on topic. That is enough.

As he 'liked' the post he quoted I don't think he was being snarky here. Just asking what part the poster might be willing to take in this endeavor.

 

[sixhotdogneck]

I guess I could contribute to a spec and of course coding but off the top of my head:

Base Classes/Interfaces: Salt, Acid, Grain, Water, Mash

Salt:
Properties: Molecular weight, Salt Name, Proton Contribution
Functions:
Operators:

Acid:
Properties: pH, pK1, pK2, pK3, pK4, Acid Name, normality
Functions: DensityPH - determine acid density at specified pH probably from a formula derived from online data (20C or 25C)
Operators:

Grain:
Properties: pHDI, a, b, c, Grain Type
Functions:
Operators:

Water:
Properties: pH, Ca PPM, Mg PPM, Na PPM, Cl PPM, SO4 PPM, HCO3 PPM
Functions:
Acidify - would determine how much (weight/volume) of a specific acid would be needed to bring this waters pH to a target pH and modify the water as such (Adding the acid flavor ions)
AddSalt - would add a specified amount of specific salt to this volume of water
Operators:
'+' would combine two volumes of water to produce a new water
'+' would add a specified amount of salt to this water

Mash:
Properties: List of Temperature Steps, List of Strike Waters, List of Sparge Waters, List of Grains, Target pH
Functions:
AddWater - would add a water
AddGrain - would add a grain
MashPH - would calculate the pH of the mash given the list of strike waters, list of grains and output a list of acids and a list of salts to add
Operators:

Derived Classes:

Salts: Calcium, Magnesium, Sodium, Chloride, Sulfate, Bicarbonate - add property "weight"
Acid: Lactic, Hydrochloric, Sulfuric, Phosphoric, Citric, Tartaric, Malic - add property "volume"
Grain: add property "weight", add property "name"
Water: add property "volume"
Mash: add property ""

Error checking would be slim and fall on the consumer of the library - IOW user would be allowed to create unrealistic mash pH's and waters

Usage would be similar to that in my post above. Usability is always determined by using the library.

This is all just back of the napkin but perhaps a step in the right direction.

 

[ScrewyBrewer]

He discussed on topic. That is enough.

Maybe for you it is but I'm interested enough to find out more.

 

[ScrewyBrewer]

@sixhotdogneck @ajdelange I have advanced skills in Excel VBA and Visual Basic 6.0. Porting Excel VBA code to a distibuted .Dll file containing the different classes is another possible approach. The downside to the approach is limiting its use to Microsoft products, if the user's don't already to subscribe to them.

 

[shetc]

I'll set up the sprint planning

 

[passedpawn]

Maybe for you it is but I'm interested enough to find out more.

Sorry, I complete misinterpreted the meaning of your post above. I'll butt out now...

 

[Silver_Is_Money]

There may be operating system hope for those of us who are completely Microsoft intolerant. A team in Russia (presumably where Microsoft can't touch them) is presently working on an operating system called 'ReactOS', which intends to be an NT level compliant and also fully clean code 'clone' of the Microsoft operating system. It will be free. It is currently in the Alpha stage, and both "full install" and "run me from a CD or DVD" versions are available now. If it runs Excel (and if it doesn't at present, it will somewhere down the road a bit), the only remaining issue would be purchasing Excel. Having to purchase Excel while being Microsoft intolerant remains a problem though.

 

[schematix]

I don’t mind the windows and excel combination, but then again I have 3 windows laptops that all have excel so I might be biased.

Maybe this could be something developed platform agnostic.... say html5 and java script ?

 

[Big Monk]

I don’t mind the windows and excel combination, but then again I have 3 windows laptops that all have excel so I might be biased.

Maybe this could be something developed platform agnostic.... say html5 and java script ?

The question is: on who's time and dime? Excel is convenient for me because:

a.) I have it
b.) I can work quickly in it so it doesn't take me a ton of time

You start talking about TRUE coding, doing web-based stuff, etc. and:

a.) My eyes glaze over
b.) It would take up too much of my time, personally

 

[schematix]

The question is: on who's time and dime? Excel is convenient for me because:

a.) I have it
b.) I can work quickly in it so it doesn't take me a ton of time

You start talking about TRUE coding, doing web-based stuff, etc. and:

a.) My eyes glaze over
b.) It would take up too much of my time, personally

A lot of people can write code. Not as many understand this specific chemistry. I'm sure more than one programmer has the aptitude to understand the math and chemistry presented and run with it.

If AJ were hit by a bus we'd all be screwed though.

 

[ScrewyBrewer]

That is correct.

@ajdelange VBA code compiled into a Visual Studio 6.0 .Dll file can be called from worksheet cells saved as an .xlsx format. No macro or VBA code required by the worksheet.

When I try to compile your functions I get an 'Sub or Function Not Defined' error on the CalcQ(pH0) call.

Function FindpHz(pH0 As Double) As Double
'Find pH that 0's pHz starting from an initial guess at pH0
Dim dQdpH As Double
Dim QpH0 As Double
Dim Corr As Double
Corr = 0.1
Do Until Abs(Corr) < 0.0001
QpH0 = CalcQ(pH0)
dQdpH = (CalcQ(pH0 + 0.0000001) - QpH0) / 0.0000001
Corr = -QpH0 / dQdpH
pH0 = pH0 + Corr
Loop
FindpHz = pH0
End Function

 

[ScrewyBrewer]

I don’t mind the windows and excel combination, but then again I have 3 windows laptops that all have excel so I might be biased.

Maybe this could be something developed platform agnostic.... say html5 and java script ?

I have experience implementing just what you describe. As long as the spreadsheet doesn't use VBA or macros.

Click here for an example.

 

[ajdelange]

When I try to compile your functions I get an 'Sub or Function Not Defined' error on the CalcQ(pH0) call.

Do you have the source for that? I don't recall sending it to you. In any case here it is:

Function CalcQ(pH As Double) As Double

Dim Gal As Double
Dim Alkppm As Double
Dim pHs As Double
Dim pHe As Double
Dim Liters As Double
Dim alkmEq As Double
Dim Row As Double

Gal = Range("B2").Value
Alkppm = Range("B3").Value
pHs = Range("B4").Value
pHe = Range("B5").Value
Liters = 3.785 * Gal
alkmEq = Alkppm / 50

CalcQ = Liters * (Qw1L(pH) - Qw1L(pHs) + Ct(alkmEq, pHs, pHe) * (QAcid(pH, 6.38, 10.38) - QAcid(pHs, 6.38, 10.38)))
CalcQ = CalcQ - Liters * (Range("B6").Value / 50 / Range("B11").Value + Range("B7").Value / 100 / Range("B11").Value)

For Malt = 0 To 4
If Cells(6 * Malt + 22, 2).Value = " " Then
Row = 0
Else
Row = Cells(Malt * 6 + 22, 2).Value + 3
CalcQ = CalcQ + Cells(Malt * 6 + 24, 2).Value * dQm1kg(pH, Cells(Row, 9).Value, Cells(Row, 10).Value, Cells(Row, 11).Value, Cells(Row, 12).Value)
End If
Next Malt

CalcQ = CalcQ - Range("B55").Value * LacNorm(pH, Range("B56").Value)
CalcQ = CalcQ - Range("B60").Value * PhosNorm(pH, Range("B61").Value)
CalcQ = CalcQ + Range("B65").Value
CalcQ = CalcQ + (1000 * Range("B67").Value / 84) * QBicarb(pH)
CalcQ = CalcQ + Range("B74").Value
End Function

This calls several other functions which I don't think I've sent to anyone but Scotty. Also it references particular cells in the particular spreadsheet I've put together.

So I think the point is that anyone who wants to experiment with this should get a copy of it from me. I think it's far enough along that I'd be willing to send it to anyone who wants to try it. It's heavily commented and so I think people ought to be able to figure out how to use it. So anyone who wants it PM me with an e-mail address and I'll send it to you.

The only caveat is that Users MUST understand the basic principle on which it works. That is, in a nutshell. that mash components (water, bicarbonate ions, acid molecules, the individual malts) all give up or grab protons in accordance with their properties and the pH of the environment in which they are placed. The pH a mash will come to is the pH that causes all the protons donated to be exactly equal to the number of protons grabbed. To get a mash component to a particular pH protons must either be supplied to it (in which case we say there is a proton deficit with respect to that pH) or absorbed from it (in which case we speak of a proton surfeit which we more conveniently express as a negative deficit). The spreadsheet simply calculates and lists the deficit for each component and sums the deficits. The mash pH is the pH that makes the sum 0. The spreadsheet finds that pH by an iterative technique (Newton's method).

 

[ScrewyBrewer]

Thank you AJ, private message sent with my email address. When adding the code from your current post the compiler is now balking about the (Qw1L(pH) function not found. Once I see the code in your spreadsheet everything will fall into place.

 

[ajdelange]

I'm really surprised at where this thing has wound up in a little over a week. It's incredibly powerful in what it can do. For example, change the alkalinity of the source water or the end point titration pH or the amount of a malt etc. and the estimated mash pH updates automatically. You can select up to 8 malts and use ones in a 'database' (on a separate sheet) or enter pHDI and buffering numbers for 'custom' malts. If you don't have the latter just use -40 and you'll get a pretty good estimate. Malt selection is by pull-down menu. It will calculate the amount of lactic acid, phosphoric acid, sodium bicarbonate or a malt (usually sauermalz) it takes to hit a desired mash pH at the push of a button (and warn you if you are trying to acidify an already to acidic mash). It uses none of the approximations the first generation spreadsheets use. For example it does not estimate mash pH based on the assumption that proton deficit is a liner function of pH but instead uses iterative techniques hidden in macros. One of the 'bells and whistles' it features is a plot of proton deficit vs pH. You can see in the picture how non linear the curve is. It takes into account the fact that the strength of phosphoric and lactic acid and the proton absorbing capacity of sodium bicarbonate depend on pH.

I think this is ready for the general user to play with. It's heavily commented so you should be able to figure out how to use it pretty quickly. Are there pronblems with it? There probably are. It's only a few hours old at this posting. But if anyone wants to give it a shot just drop me a PM with and e-mail address.