Silver_Is_Money
Larry Sayre, Developer of 'Mash Made Easy'
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.
Last edited:
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.
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.
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?
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.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?
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."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)?
Is calcium also beneficial in haze reduction?
PS: the mash thickness/pH issue with Bru'n Water has been corrected. Thanks for the prodding.
PS: the mash thickness/pH issue with Bru'n Water has been corrected. Thanks for the prodding.
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.
And your contribution to this project would be...?....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...?
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%.But does it still use 'Solver'?
That is correct.AJ is programming VBA Functions.
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).It does not use solver.
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.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.
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.Newton-Raphson is an algorithm that can be used in place of solver.
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 isThe book "Numerical Recipes in C" contains implementations useful in the context given in this thread. (or Fortran)
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).
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.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.
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.He discussed on topic. That is enough.
Maybe for you it is but I'm interested enough to find out more.He discussed on topic. That is enough.
Maybe for you it is but I'm interested enough to find out more.
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
@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.That is correct.
I have experience implementing just what you describe. As long as the spreadsheet doesn't use VBA or macros.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 ?
When I try to compile your functions I get an 'Sub or Function Not Defined' error on the CalcQ(pH0) call.
Enter your email address to join: