EZ Water Calculator 2.0

EZ Water Calculator 2.0 is now available:
http://www.ezwatercalculator.com/

The spreadsheet now calculates the estimated mash pH rather than using Palmer's RA/SRM correlation. The pH is calculated using water profile data, SRM, amount of roasted malt, and mash thickness. Calculations are based on Kai's extensive mash water pH experiments, seen here:
http://braukaiser.com/documents/effect_of_water_and_grist_on_mash_pH.pdf
and here:
http://braukaiser.com/wiki/index.php/Beer_color,_alkalinity_and_mash_pH

Version 2.0 also allows for addition of acidulated malt (Sauermalz).

Less focus is placed on the chloride to sulfate ratio, although it is still there.

Regional water profiles (i.e. Burton, etc.) have been removed.

Some disclaimers have been added - including one about the addition of chalk.

Here's a screenshot:

Nice! Thanks for all the hard work and keeping the spreadsheet up with current research. Looking forward to using this!

.... Any chance of uploading an html version. My Excel 2008 for Mac doesn't like the radial buttons for alkalinity and bicarbonate. This was the case in the last version too, so I always used the html version, which worked for selecting alkalinity.

You freakin animal.

Sweet! Are crystal malts roasted? Or does that just apply to black patent and roasted barley?

I always assumed that Crystal malts were not roasted (which contribute more acidity) and that roasted malts (Roasted Barley, chocolate, black patent) contributed less acidity. Is this true?

Also, how does munich and vienna play into this spreadsheet? When using Kai's spreadsheet I considered them to be roasted specialty malts.

aarondrich said:

I always assumed that Crystal malts were not roasted (which contribute more acidity) and that roasted malts (Roasted Barley, chocolate, black patent) contributed less acidity. Is this true?

Also, how does munich and vienna play into this spreadsheet? When using Kai's spreadsheet I considered them to be roasted specialty malts.

Click to expand...

I put munich in as a base grain, I hope that's right? Always more to learn.

Ok, since we started it how about Aromatic? I'm assuming this is roasted?

The "specialty" grain heading is what throws me off, aren't all specialty grains roasted, at least to a point?

Any chance for a tsp size addition conversion ?

here's the chart from Kai's paper:


Munich and Vienna are in the base malt grouping.

I think I will change the heading "specialty" malts to be "crystal - type" malts
and maybe list examples somewhere.

adam01 said:

Any chance for a tsp size addition conversion ?

Click to expand...

4 grams is approx 1 tsp.

jmo88 said:

.... Any chance of uploading an html version. My Excel 2008 for Mac doesn't like the radial buttons for alkalinity and bicarbonate. This was the case in the last version too, so I always used the html version, which worked for selecting alkalinity.

Click to expand...

Not real soon. I'm having a hard time getting anything to successfully convert it to html or even any web-based spreadsheet format for that matter.

All that the radio buttons do is put a "1" in cell K3 for HCO3 or a "2" for CaCO3. You could try to just do that manually in your Mac version by typing 1 or 2 in cell K3 and see if that works. Maybe if I had access to a mac I could tweak it to work better but for now...

You may want to round to 2 digits for mash thickness since 1.25qts/lb is common.

If you turn off adjust for sparge water, then you're expected to put enough in the mash
to account for all the water, correct?

Is there a benefit to only putting salts into the mash vs mash + sparge ?
(Mash is easier)

Adam

Nice work TH. It's a lot busier than version 1, but a whole lot more information too. I kinda miss the SRM ranges though.....

-TH- said:

Not real soon. I'm having a hard time getting anything to successfully convert it to html or even any web-based spreadsheet format for that matter.

All that the radio buttons do is put a "1" in cell K3 for HCO3 or a "2" for CaCO3. You could try to just do that manually in your Mac version by typing 1 or 2 in cell K3 and see if that works. Maybe if I had access to a mac I could tweak it to work better but for now...

Click to expand...

Good enough for me. That worked. It's an easy enough workaround.

Great work! And thank you VERY much for the OpenOffice version!

Man, and I just finally got comfortable with version 1. There's always more to learn...

Thanks for all the hard work, -TH-. I can't imagine how complicated this must be to get into spreadsheet form for people like me.

My water report (Bear Gulch Water District, CA, http://www.calwater.com/wq/ccr/2009/bear-gulch-bg/bear-gulch-bg-2009_web.pdf) does not list bicarbonate or alkalinity. Is there a way to derive it from other values that might be found on the water report?

Reelale said:

Nice work TH. It's a lot busier than version 1, but a whole lot more information too. I kinda miss the SRM ranges though.....

Click to expand...

Why miss something that apparently is far less important than we thought?

wuertele said:

My water report (Bear Gulch Water District, CA, http://www.calwater.com/wq/ccr/2009/bear-gulch-bg/bear-gulch-bg-2009_web.pdf) does not list bicarbonate or alkalinity. Is there a way to derive it from other values that might be found on the water report?

Click to expand...

Given that bicarbonate is one of the most prevalent ions in most drinking water you can get an idea of how much is present by adding enough to bring the total electronic charge per liter to 0 based on all the other ions reported. In your case the situation is complicated by the fact that averages are presented and that the water has an inordinate amount of aluminum. Caveats given, I estimate that your alkalinity is around 189 mg/L as CaCO3.

thank you very much, i may try a bit of headscratching tinkering to see if i can convert it to metric-any suggestions appreciated, working in carpentry doesn't really require office software skills!

Thanks for the work

The water report lists 'Hardness' as 55 (average). This is probably what you are looking
for.

Thank you. Thank you. Thank you.

adam01 said:

The water report lists 'Hardness' as 55 (average). This is probably what you are looking
for.

Click to expand...

No, he said alkalinity or bicarbonate neither of which is listed. Also he specifically said Bear Gulch which has an average hardness of 170. The 55 number is for the SFPUC district.

You may be confusing "carbonate hardness" with hardness. Carbonate "hardness" is the term Europeans sometimes use to describe alkalinity. In this case it means the part of the hardness which is paired with bicarbonate i.e. the alkalinity (at pH < 8.3). Here, the carbonate hardness, usually called "temporary hardness" in the US, is about 170 with the rest of the 189 being being "permanent hardness" (paired with sulfate and chloride). If there is no chloride or sulfate (or other anion) then alkalinity and hardness are the same.

And yes, I did say 50 last night but that was because, like the big dummy I can sometimes be, I didn't notice that the "as sulfur" check box on my spreadsheet was selected. The total alkalinity for these data would be about 189. I'll go back and correct yesterday's post.

That is so Awesome! I can't wait to use it on my next batch! Thanks so much for your contributions to the community. You should get free beer everywhere you go.

Why were the regional profiles removed? Doesn't that help us target the product we are shooting for? Other than that, I love the changes (and the work-around for Excel for Mac).

thorongil said:

Why were the regional profiles removed? Doesn't that help us target the product we are shooting for.

Click to expand...

Second that.

thorongil said:

Why were the regional profiles removed? Doesn't that help us target the product we are shooting for? ...

Click to expand...

IMO the target we are shooting for is #1 getting pH right and #2 getting ions within preferred ranges (not necessarily matching a historical profile). I understand there might be a few hard core folks out there who really want to match regions - thats why I put it on v1.0 (it was by request). However, after I did that, so many people got the wrong idea thinking they HAD to match region profile. It created way more confusion that what it was worth. With v2.0 I figure if someone is bent on matching a profile, they can still do that by finding the values on their own and matching them by comparison. They should be aware however that to match the profile exactly might be difficult to get right and might require a lot of salts/acids - in which case the resulting water might not end up a perfect match due to factors outside the realm of this spreadsheet. Also, I've read (can't remember source) that the well-known breweries in these regions most likely adjust their water now anyway so attempting to match the published profiles is somewhat pointless.

Hope you can use it anyway

Cheers

That all makes good sense, and I will absolutely use the spreadsheet - it is a great tool for which I thank you. I'll just continue to compare to the regional ratios and RA to stay in the ballpark.

I also will continue to use the spreadsheet. I am one of those that tried hard to match profiles. I always wondered how accurate the profiles truly are with current water treatment and quality control. But they seemed like a good target for any particular beer type.

-TH- said:

They should be aware however that to match the profile exactly might be difficult to get right and might require a lot of salts/acids - in which case the resulting water might not end up a perfect match due to factors outside the realm of this spreadsheet.

Click to expand...

One of those factors is that many, if not most, of the commonly published profiles do not represent physically realizable water. People wind up trying to match something that nature could never make. If nature can't make it you can't make it.

-TH- said:

Also, I've read (can't remember source) that the well-known breweries in these regions most likely adjust their water now anyway so attempting to match the published profiles is somewhat pointless.

Click to expand...

As an obvious example of this - Munich Helles and Dunkles are both (or were both) made from the same water but it was obviously decarbonated before making Helles. Today smaller breweries would be supplied with mains water which is very probably softened at the water treatment plant in order to protect the city's distribution system. Larger breweries may still operate from their own wells but I think that's becoming rare. And the question as to what an individual brewery does with its well or municipal water can only be answered by contacting the brewery itself. So when trying to match Burton water, for example, be aware that modern Burton style ales seem to be brewed today with much softer, lower sulfate water than the specs for Burton which you may have found would seem to indicate. Match those specs and you will have a historically authentic beer. Go with general water management principals and you will have a perhaps less authentic but nevertheless better beer.

So i've been following these EZ water calc spreadsheet updates and threads and now I've got a question. Using this newest spreadsheet (2.0) from what I understand you target your CL:S04 ratio to enhance your flavor profile, than you verify and/or adjust so that your mash pH is within range also. The newest spreadsheet emphasis is no longer placed on the RA to SRM correlation but rather the mash pH. And now the question, does the RA matter if your ph is in range? What I mean by this is does this affect the flavor similar to the CL:S04 directly?...or only does it only affect flavor by being off and therefore the mash pH being off and thus problems with incorrect mash pH (ie: acidic mash extracting tannins)?

This came up when I was trying to build a profile for a dry stout (Jamil's recipe) loosly based on the dry stout regional profile. My "balanced" profile based on balanced mash pH and a good malty CL:SO4 ratio resulted in a negative RA. From previous reading I have heard higher RA = darker higher SRM (more acidic mash).

Here are the profile details I built if relevant (please feel free to comment on them I could use any advise):
Ca: 52
Mg: 2
Na: 39
Cl: 100
SO4: 58

Cl:sO4: 1.72
CaCO3: 23
RA: -15
Est. pH: 5.32

Can someone with more knowledge please clarify?

I'm trying this on Sunday with a Robust porter. Can't wait to see if I can nail my mash pH using the spreadsheet.

Many thanks!

Excellent work.

I am curious regarding the mash thickness disclaimer, "*** This spreadsheet becomes less accurate for mash thickness below 1.0 qt/lb or above 2.4 qt/lb."

I do full-volume, BIAB, with approximately 3.3 qt/lb when I am doing a 60-minute boil, and 3.7 qt/lb for a 90-minute boil. Is there any easy way to modify the spreadsheet based upon mash thickness?

jeffmeh said:

Excellent work.

I am curious regarding the mash thickness disclaimer, "*** This spreadsheet becomes less accurate for mash thickness below 1.0 qt/lb or above 2.4 qt/lb."

I do full-volume, BIAB, with approximately 3.3 qt/lb when I am doing a 60-minute boil, and 3.7 qt/lb for a 90-minute boil. Is there any easy way to modify the spreadsheet based upon mash thickness?

Click to expand...

It appears that -TH- based the spreadsheet on Kai Troester's experimental data which was limited to a range of mash thicknesses. So you could repeat Kai's experiments with thinner mashes.

Alternately you could measure the pH of the mash and make small adjustments as necessary.

In general with more water your mash pH will be higher.

remilard said:

It appears that -TH- based the spreadsheet on Kai Troester's experimental data which was limited to a range of mash thicknesses. So you could repeat Kai's experiments with thinner mashes.

Alternately you could measure the pH of the mash and make small adjustments as necessary.

In general with more water your mash pH will be higher.

Click to expand...

couldn't have said it better myself, thanks!

so, is chocolate malt "crystal" or "roasted"?

nice work TH

motobrewer said:

so, is chocolate malt "crystal" or "roasted"?

nice work TH

Click to expand...

I would assume it's roasted

I had the same question about chocolate malt as well as brown and amber malt. I would assume they are "roasted," but who knows?

Seems like the best thing to do is repeat Kai's experiment using those malts and decide where they belong from there. I don't really have the equipment to pull that off, though (no pH meter).

Anyway, fine work on this spreadsheet! It has already brought to light a couple minor mistakes I was making. I'm sure I can make good use of it. Thanks, TH!