[Kaiser]

I finally finished overhauling the water and mash pH spreadsheet that I have been maintaining. I started this work a few months ago when I realized that even the "basic" interface was not as intuitive as I thought it was. It seems nobody wants to enter salts as ppm even though that makes the addition independent of the water amount.

Here is what I changed:
* rearranged the basic interface into water, grist, salts&acids, report and necessary salt additions. This should be more intuitive.
* changed the treatment of undissolved chalk such that it only contributes half its calcium since it contributes only half its alkalinity. Chalk's solubility in mash seems to be limited and what does not dissolve and contribute to a rise in alkalinity should not contribute calcium ions either.
* salt additions can now be made in g and mg/l. You can select the unit
* the salts to be added can be reported in g and tsp. The latter is useful if your scale breaks down or you don't own one yet.
* lactic acid and phosphoric acid are supported. How out there is using hydrochloric or sulfuric acid? I could add this as well.
* water boiling has been added to the "advanced page". This was easy to add since I already supported lime treatment
* I added pH shift estimations for the major water treatment steps

This is what I kept:
* The basic and advanced pages are still there. Anything entered in the basic page will automatically carry over into the advanced page. The idea is to support a wide variety of users
* the basic formatting remained ion order to better support its use on mobile devices
* I avoided macros or the use of fancy functionality in hope that this spreadsheet can be supported by mobile devices
* the SRM based mash pH prediction is still there.
* support for SI and US units. Under the hood it uses SI units almost exclusively.

I plan to add grist based mash pH prediction. Not so much because it is much more precise than the SRM based prediction but because you don't have to enter the %roasted value, which I admit is a bit ambiguous and not that easy to grasp.

I gave it a fair amount of testing and when updating it I made sure to reuse as much of what I already had in order to reduce the number of bugs that were introduced. I was also tempted to password protect the content but then I noticed that when you open a password protected Excel file in OpenOffce you are not asked for a password when unlocking pages. So much for security . But the pages are still protected in order to avoid accidental changes to the formulas.

Kaiser_water_calculator.xls

Kai

[ndcube]

Thanks Kai.

I used to use grams as a salt input and adjusted to the desired ppm but I find that since using your spreadsheet I like starting in ppm and have grams as the output.

[andyi]

Thanks for the update Kai,

On the basic and advanced tabs in the in the "base water blending" section: cannot imput the into Ca and Mg fields, they  are formulas.

Did I miss something?

-Cheers

[tygo]

Nice update.  I like the ability to put the salts in as grams.

I noticed that the salt and acid additions get split out proportionally between the mash and sparge water.  Does that mean that the calculation of mash pH is factoring in only the strike water portion of the additions?

Also, is the calculated mash pH at mash temperature or room temperature?

[bluesman]

I now have two very detailed water calculators. One from Martin and yours. 

I will be able to compare results between them. We are very fortunate to have experts like Martin and yourself here at the AHA.

Thanks again for your great work!

[Kaiser]

Good questions. I'll have to add them to the documentation.

I used to use grams as a salt input and adjusted to the desired ppm but I find that since using your spreadsheet I like starting in ppm and have grams as the output.

Specifying salts in mg/l (ppm) is still there. Just select mg/l as the units for the salts.

On the basic and advanced tabs in the in the "base water blending" section: cannot imput the into Ca and Mg fields, they  are formulas.

No you didn't miss anything. You should be able to override the Ca, Mg and Alkalinity values. By default they are taken from the GH and KH test results in case you only have that info.

I noticed that the salt and acid additions get split out proportionally between the mash and sparge water.  Does that mean that the calculation of mash pH is factoring in only the strike water portion of the additions?

yes, mash pH only factors in the strike water minerals.

Also, is the calculated mash pH at mash temperature or room temperature?

By convention, pH values are always given as room temp sample pH values. I have to be more explicit about that.

Kai

[ndcube]

Good questions. I'll have to add them to the documentation.

I used to use grams as a salt input and adjusted to the desired ppm but I find that since using your spreadsheet I like starting in ppm and have grams as the output.

Specifying salts in mg/l (ppm) is still there. Just select mg/l as the units for the salts.

Yeah, I realized that.  Just saying how my preference changed.

[tygo]

I noticed that the salt and acid additions get split out proportionally between the mash and sparge water.  Does that mean that the calculation of mash pH is factoring in only the strike water portion of the additions?

yes, mash pH only factors in the strike water minerals.

Before I make my next nitpicking comment I'd just like to say that this is a great spreadsheet and I'd like to express my appreciation to you for taking the time to put this together and release it free to the world for the benefit of all of us homebrewers.

So on to the nitpicking  :

As a batch sparger I'd like the option to just add all of the salt and acid additions to the mash and have the mash pH calculated off of that.  I don't worry too much about the sparge water.  As a work around I'll just set the total water and strike water equal and it'll work just fine.

Although if you wanted to help me predict boil pH based on the pH of the sparge water I'm adding and give me the option to adjust that I wouldn't object. 

[Kaiser]

As a batch sparger I'd like the option to just add all of the salt and acid additions to the mash and have the mash pH calculated off of that.  I don't worry too much about the sparge water.  As a work around I'll just set the total water and strike water equal and it'll work just fine.

I have actually thought about that. Mostly since I think that all the salts can be added to the mash. Especially when using R/O water. I assume that you would still be interested in the equivalent water profile as opposed to the water profile just for the mash. What I mean with this is that the reported water profile should be shown as if the salts were added to all the water since you want to see that the ion concentrations are in the suggested range. The pH should however be calculated as if all salts are added to the strike water.

Kai

[tygo]

Yeah, that's exactly right Kai.  I am interested in the total water profile but my practice is to add all salts to the mash.

[akr71]

Here is what I changed:
...
* the salts to be added can be reported in g and tsp. The latter is useful if your scale breaks down or you don't own one yet.
...

Sweet!  I own a scale, but it only has a resolution of 1g and I question its accuracy (it works well enough for hops, but water additions are another story).  I have measuring spoons that go down to 1/32 tsp.

[Kaiser]

Yeah, that's exactly right Kai.  I am interested in the total water profile but my practice is to add all salts to the mash.

I added that in the latest version and hopefully didn't introduce new bugs.

You can now select if you want to add salts to the mash only or both mash and spare. In the report section you can then see the report for the mash or the overall water profile. Residual alkalinity is always reported for the mash only.

Kai

[tygo]

Awesome!  Thanks Kai.

[roger]

Kai, another thanks from an appreciative brewing novice. I've learned so much about water chemistry in the past few months. I now realize how much I don't know that it's scary.

In using your spreadsheet, is it appropriate to adjust the mash water as suggested, but simply treat sparge water with acid to get the pH correct? This is not in accordance with the German Purity law, but then again, I never studied law.

[Kaiser]

Kai, another thanks from an appreciative brewing novice. I've learned so much about water chemistry in the past few months. I now realize how much I don't know that it's scary.

There is a lot of detail that explains how things work. But to brew beer you only need to remember what lowers pH and what raises pH. Most of the dense chemistry is contained in spreadsheets, water recipes or guidelines for water treatment.

In using your spreadsheet, is it appropriate to adjust the mash water as suggested, but simply treat sparge water with acid to get the pH correct? This is not in accordance with the German Purity law, but then again, I never studied law.

No, it’s not in accordance with the RHG (ReinHeitsGebot, German purity law), and that’s why it hasn’t shown up in my calculator until now. It also not what you would do if you want to be authentic and recreate a particular city’s brewing water. But that is not that important anyway.

Adding all the salts to the mash is fine. What will happen is that the boil pH will be closer to the mash pH since less pH changing minerals are added through the sparge. And having a boil pH closer to the mash pH is not a bad thing.

Kai