EZ Water Adjustment spreadsheet

HomeBrewTalk.com - Beer, Wine, Mead, & Cider Brewing Discussion Community.

Help Support Homebrew Talk:

-TH-

Well-Known Member
Joined
Oct 22, 2008
Messages
913
Reaction score
108
Location
Zeeland, Michigan
Go here to download the spreadsheet:
www.EZWaterCalculator.com
Version 1.7 (2-26-10)

Instructions:

Step 1: Enter your starting water profile, mash & sparge volumes, and dilution rate (if any).

Step 2: Examine the results.
1) Residual Alkalinity (RA) - See if the corresponding color range (SRM) matches the beer you are brewing. If not, the RA will need to be adjusted in order for the mash to be at the proper pH.
2) Chloride to Sulfate Ratio - Determine if this needs to be adjusted in order to match the style/flavor of your beer.
3) Individual Mineral Levels - Take note of which mineral levels need to be adjusted in order to fall within the recommended ranges shown. Alternatively you can try to match these levels to a particular region or other set of guidelines.​
Step 3: Modify the results by making salt additions. The goal here is to get: 1) The RA to coincide with your recipe color, 2) The chloride to sulfate ratio to a desired flavored profile, and 3) The individual mineral levels to within recommended ranges.

Notes:

Add mash salts directly to the mash. Add sparge salts directly to the boil (not the sparge). You may choose not to add certain salts to the sparge water in order to keep some minerals lower in the total water (sodium for example). In this case, simply uncheck the appropriate box.

For an excellent video series about adjusting water using this spreadsheet (courtesy Bobby M), go here: Water Modification Videos, TH's Spreadsheet

For more information about how this works and why, I highly recommend reading John Palmer's entire chapter on water found here:
http://www.howtobrew.com/section3/chapter15.html

Source: The recommended mineral ranges, RA to SRM recommendations, and Cl to SO4 ratio recommendations are all based on John Palmer's RA spreadsheet and pH nomograph.
 

EuBrew

Well-Known Member
Joined
Apr 22, 2009
Messages
607
Reaction score
33
Location
Columbus, OH
So far this seems much easier to manage. One question, my water report does not have bicarbonate listed. It has total alkalinity (CACO3), Noncarbonate hardness (CACO3), and total hardness (CACO3). Which of these values do I plug into the bicarbonate level?
 
OP
-TH-

-TH-

Well-Known Member
Joined
Oct 22, 2008
Messages
913
Reaction score
108
Location
Zeeland, Michigan
So far this seems much easier to manage. One question, my water report does not have bicarbonate listed. It has total alkalinity (CACO3), Noncarbonate hardness (CACO3), and total hardness (CACO3). Which of these values do I plug into the bicarbonate level?
Alkalinity. I'll note that on the spreadsheet.
 

The Pol

Well-Known Member
Joined
Feb 12, 2007
Messages
11,390
Reaction score
116
I use Brewater 3.0... it is fully automatic

It only asks for your starting water profile...

Then asks for your target water profile (it has a library of just about any profile you can imagine)

Once you select your target, you let it compute your salt additions itself. No trial and error... it will give you the salt amounts in grams or in tsp.

The only thing you have to do is tell it your starting water profile, then choose one of the many target water profiles... bam, it is done. I am personally not a big fan of slide rule type calculators.

Nice work though.
 
OP
-TH-

-TH-

Well-Known Member
Joined
Oct 22, 2008
Messages
913
Reaction score
108
Location
Zeeland, Michigan
But the Bicarbonate number and the CaCO3 number are not the same. Close but not the same number. I've never even tried a conversion between them so I don't know what it is.
This is what I had noted on the spreadsheet:
Bicarbonate (HCO3) = Alkalinity
as CaCO3 x 60 / 52

It was a little unclear because of the text wrap not to mention it should have been 61 / 50.

Now it shows:
Bicarbonate (HCO3) = Alkalinity as CaCO3 x 1.22
 

Saccharomyces

Be good to your yeast...
Lifetime Supporter
Joined
Jun 17, 2008
Messages
5,438
Reaction score
145
Location
Pflugerville, Texas
But the Bicarbonate number and the CaCO3 number are not the same. Close but not the same number. I've never even tried a conversion between them so I don't know what it is.
The conversion depends on many factors, so the best bet is to get it straight from the report.

If you don't have your alkalinity, but you have total hardness, using the total hardness number is probably close enough. We are making beer, not atomic weapons. ;)
 

conpewter

Well-Known Member
Joined
Nov 2, 2007
Messages
5,077
Reaction score
58
Location
East Dundee, Illinois
I use Brewater 3.0... it is fully automatic

It only asks for your starting water profile...

Then asks for your target water profile (it has a library of just about any profile you can imagine)

Once you select your target, you let it compute your salt additions itself. No trial and error... it will give you the salt amounts in grams or in tsp.

The only thing you have to do is tell it your starting water profile, then choose one of the many target water profiles... bam, it is done. I am personally not a big fan of slide rule type calculators.

Nice work though.


I like Brewater but it gives me some strange salt additions when I let it use all the choices, once I narrow it down so it only cares about Ca/Mg/Cl/SO4 and only let it use gypsum and epsom salt it actually works. For the most part I am liking using palmer's spreadsheet and beersmith to set up my water profiles for different styles.
 

Bobby_M

Vendor and Brewer
HBT Sponsor
Joined
Aug 3, 2006
Messages
25,195
Reaction score
4,138
Location
Whitehouse Station
Since I'm just learning this stuff, I appreciate having to use trial and error because I think I'm learning more about what each salt addition actually does. It's kind of like calculating out efficiency on paper before you let software tell you.
 
Joined
Aug 11, 2008
Messages
2,266
Reaction score
25
Location
Brooklyn
Since I'm just learning this stuff, I appreciate having to use trial and error because I think I'm learning more about what each salt addition actually does. It's kind of like calculating out efficiency on paper before you let software tell you.
I feel the same way. I like to figure it out, helps you learn the WHY and HOW, instead of just following directions. Thanks very much for this -TH-, very well done.
 

ShortSnoutBrewing

Kwanesum Chinook Illahee
Joined
Oct 17, 2007
Messages
3,189
Reaction score
25
Location
Portland, OR
Thanks -TH-. A lot less "cluttered" than the Palmer version.

Question to all...On the Very Bitter - Very Malty scale...Is it wrong to say that we are aiming towards balanced with all brews? It would seem that we can have a hoppy/bitter brew that is still "balanced" and vice-versa on the malty side.
 

Schnitzengiggle

Well-Known Member
Joined
Feb 19, 2009
Messages
2,560
Reaction score
39
Location
Tucson
Thanks -TH-. A lot less "cluttered" than the Palmer version.

Question to all...On the Very Bitter - Very Malty scale...Is it wrong to say that we are aiming towards balanced with all brews? It would seem that we can have a hoppy/bitter brew that is still "balanced" and vice-versa on the malty side.
Good question, my water profile tells me that it is balanced for a 10-15 SRM style, my first AG brew this Sunday will be an Ordinary Bitter, having said that Burton on Trent water makes for a very bitter profile, so just like Kilted Brewer asks, shouldn't we aim for a balanced style?

Which is what I will be getting from my water.
 
OP
-TH-

-TH-

Well-Known Member
Joined
Oct 22, 2008
Messages
913
Reaction score
108
Location
Zeeland, Michigan
This is from the instruction page of Palmer's worksheet. It should clear things up:

Here is where the chloride to sulfate ratio is useful to help choose which salts to use in adjusting the RA. If you are intending to brew a hoppy beer, use sulfate salts to move the balance to Bitter or Very Bitter. If you are intending to brew a malt dominated beer, then use chloride salts to move the balance to Malty or Very Malty. Alternatively, you can use a combination of chloride and sulfate salts to keep the character Balanced.
 

HenryHill

Well-Known Member
Joined
Mar 9, 2007
Messages
3,039
Reaction score
13
Location
Perry, MI
There doesn't seem to be an increase in Cl- when I add some HCl to lower the alkalinity, for a low SRM beer. Doesn't this contribute Cl-to the adjusted water?

By Palmer's Sheet, I am balanced for Cl/SO4 when adding HCl, and Cl- changes. I see no change in ppm of Cl in EZ, and the ratio does not change, leaving my result unbalanced.
 

lamarguy

Well-Known Member
Joined
Aug 21, 2008
Messages
1,652
Reaction score
33
Location
Austin, TX
This is from the instruction page of Palmer's worksheet. It should clear things up:

Here is where the chloride to sulfate ratio is useful to help choose which salts to use in adjusting the RA. If you are intending to brew a hoppy beer, use sulfate salts to move the balance to Bitter or Very Bitter. If you are intending to brew a malt dominated beer, then use chloride salts to move the balance to Malty or Very Malty. Alternatively, you can use a combination of chloride and sulfate salts to keep the character Balanced.
Yup, exactly - target the style of the beer. You can certainly make all of the water profiles "balanced", but that doesn't help accentuate the target style in any way.

Just be sure to stay within the recommended range for SO4. Too much and you're IPA may be darn near undrinkable...
 
OP
-TH-

-TH-

Well-Known Member
Joined
Oct 22, 2008
Messages
913
Reaction score
108
Location
Zeeland, Michigan
There doesn't seem to be an increase in Cl- when I add some HCl to lower the alkalinity, for a low SRM beer. Doesn't this contribute Cl-to the adjusted water?

By Palmer's Sheet, I am balanced for Cl/SO4 when adding HCl, and Cl- changes. I see no change in ppm of Cl in EZ, and the ratio does not change, leaving my result unbalanced.
His only adjusts if there is a number greater than zero in the "est. Acid Only Mash Addition (ml)". For that number to be greater than zero, you have to enter a "target RA" that is lower than your starting RA (according to his cell formula). If not, that number stays 0 and the spreadsheet won't adjust CL for the HCL addition. I think he was just looking at the wrong cell by mistake and should have been checking if the "HCL Mash water addition" cell was above zero to determine if CL should be adjusted (if you look at his formulas you might see what I'm trying to say here).

Anyways I'm thinking the CL should be adjusted for HCL no matter what so I fixed mine to do that. It has now been re-uploaded (v1.1).
 

Snafu

Well-Known Member
Joined
Apr 9, 2009
Messages
906
Reaction score
22
Location
Richmond, VA
TH with this new ver 1.1 something has changed. All the other sheets (incl. ver 1.0)and software say my beers should be very bitter without any additions. When I plug in my #'s on your sheet now they tell me I'l balenced. Can you verify?

Ca - 19.2
Mg - 16.3
Na - 23.2
SO4 - 40.6
Cl - 12.4
HCO3 - 52.1

Thanks!
 

Snafu

Well-Known Member
Joined
Apr 9, 2009
Messages
906
Reaction score
22
Location
Richmond, VA
Thanks -TH-. A lot less "cluttered" than the Palmer version.

Question to all...On the Very Bitter - Very Malty scale...Is it wrong to say that we are aiming towards balanced with all brews? It would seem that we can have a hoppy/bitter brew that is still "balanced" and vice-versa on the malty side.
I'm very new to this so maybe I'm looking at this wrong. but as I understand my own water profile (without additions) I would be brewing very bitter beer. I use the spreadsheets to move that to a range of bitter/malty that fits into the BJCP style guide. For example if I was brewing a American wheat which is described as "slightly malty" I would adjust my water profile that is very bitter to a malty one by additions of Calcium Chloride and some Gypsum. (amounts will vary). But if I wanted to brew a east coast pale ale with is very bitter I may only has to adjust with a little gypsum and not change the bitterness of my water.

But, as I said before, I'm very new at water profiles and this is only my understanding of it as of today. might change tomorrow! :D
 
OP
-TH-

-TH-

Well-Known Member
Joined
Oct 22, 2008
Messages
913
Reaction score
108
Location
Zeeland, Michigan
TH with this new ver 1.1 something has changed. All the other sheets (incl. ver 1.0)and software say my beers should be very bitter without any additions. When I plug in my #'s on your sheet now they tell me I'l balenced. Can you verify?

Ca - 19.2
Mg - 16.3
Na - 23.2
SO4 - 40.6
Cl - 12.4
HCO3 - 52.1

Thanks!
SORRY! I left a "2" in the HCL ml addition. Make that Zero and you should be back to the way you were.
 

boredatwork

Well-Known Member
Joined
Dec 9, 2008
Messages
286
Reaction score
7
Location
Westford, MA
This doesn't have much to do with this specific spreadsheet, but I have three comments about the flavor profile based on the sulfate:chloride ratio. This is all based on my experiments.

First, even though most people call it "malty vs. bitter" I think it is more accurate to think about it as "malty/sweet vs. bitter". Part of this is related to my second point, but I prefer to think of the chloride (and even Palmer uses this wording) as emphasizing flavor - not just malty characteristics. Malty is one attribute of flavor, but so is sweetness. And sweetness and malty are not the same thing, although malty is often used to mistakenly describe sweetness (as detailed in Brewing Classic Styles).

Second, when you change the ratio you have two things working together - and it is important to think about this when you are adjusting your water. The sulfate emphasizes bitter and the chloride emphasizes flavor. If you go high on sulfate and low on chloride, not only are you bringing out the bitterness but you are also lessening the taste of the malty/sweet flavor. Likewise, if you go high on the chloride and low on sulfate you are emphasizing malty/sweet flavor and lessening the taste of bitterness.

But here is the curve ball - remember that in addition to the ratio of the two you are also controlling the individual dosage of sulfate and chloride. So regardless of the ratio the levels of each mineral will still have an effect. For example, with a high dosage of both sulfate and chloride, even if there is a 1:1 ratio, will be different than a low dosage of both with a 1:1 ratio. The same concept applies even when the ratio is not 1:1. If you have a 2:1 ratio with low dosage, it will be different than a 2:1 ratio with a high dosage, and vice-versa.

Lastly, because of the second point, you need to take think of your water as a part of your recipe - and think about the contribution it will have on the intended flavor. This is really important.

For example, I made a beer than should have been high in flavor (malty/sweet) and low in bitterness. However, my sulfate:chloride was about 3:1 - which is exactly the opposite of what you would want. But in addition to the backwards ratio, the levels of chloride were very low. Not only did the beer come out very bitter, but it also had very little flavor. This was due to the double whammy of low chloride in the water, but also the low chloride ratio.

Now, on a different beer that should have also been malty/sweet and low bitterness, I went to a 1:2 ratio of sulfate:chloride, but also increased the levels of chloride. The effect was dramatic. There was lots of flavor and very low bitterness.

So, it is clear that the water profile effects flavor, but here is the reason to think about it in the context of your recipe. In the second example where the water was targeted to provide a malty/sweet flavor and low bitterness, I also independently adjusted the IBU through the amount of hops to the lower end of the style range because that I like more malty/sweet and less bitter. However, I did this without consideration of the water adjustments I had made - not thinking of the water as a part of the recipe. After tasting the beer it was very very sweet and very low in bitterness. I highly suspect that I had made a double adjustment for malty/sweet - one coming from the water and one from the IBU adjustment. Personally I loved it, but it made me realize that I need to think of the water profile as a contributor to flavor just as much as the hop additions or the grain bill.

Hopefully that was clear.
 

Snafu

Well-Known Member
Joined
Apr 9, 2009
Messages
906
Reaction score
22
Location
Richmond, VA
that was awsome bored! Makes me feel i'm on the right tract, but still have alot to learn. I think what would help some of the folks here as well as myself would be some kind of power point or video where someone would actually use a typical water profile and show exactly how (how much) additions you would make for a few certain types of beers. I seem to recall there are limits to how much you should add to a 5 gal batch (1 tsp?) It was in one of the many books I have.

I'm excited about this stuff, I've been brewing for years and although I have a pretty good feel for my equipment and the process. This is the final frontier that will make a hugh impact on the finished product. I just went to AG brewing this summer and although I've been reducing the hop bitterness for my own personal taste it was still bitter (because of water). I actually didn't sleep all that well because I can't wait to try it out this weekend!
 

jdieter

Well-Known Member
Joined
Feb 10, 2009
Messages
74
Reaction score
5
Nice work TH, is there a reason you didn't include canning salt in the adjustments cells. There are times I prefer it over baking soda so I don't increase the bicarbonate but increase the chloride to sulfate ratio.
 

MarsColonist

Well-Known Member
Joined
Jul 22, 2008
Messages
384
Reaction score
6
Location
Live Music Capital of the World
This is what I had noted on the spreadsheet:
Bicarbonate (HCO3) = Alkalinity
as CaCO3 x 60 / 52

It was a little unclear because of the text wrap not to mention it should have been 61 / 50.

Now it shows:
Bicarbonate (HCO3) = Alkalinity as CaCO3 x 1.22
The 1.22 factor only works if your original pH is 8.4 or less. Here in Austin, our municipal water is ~9.4, and the most recent water report shows "Total alkalinity (as CaCO3) = 72"... by the 1.22 that would make the HCO3 = 87.8, but due to the high pH, a different formula has to be used.

From: Alkalinity (As of CaCO3 to As of HCO3-): Practical guide on unit conversion | Coal Geology & Mining

http://coalgeology.com/wp-content/u...-convertion-spreadsheet-wwwcoalgeologycom.xls

From above spreadsheet: HCO3 = (Alkalinity as CaCO3 * 61) / (( 1 + ((2x10^-10.3)/(10^(-pH)))*50)

My calc comes out as 54, which is significantly off from the aforementioned 88...

Apparently, this is because "At higher pH carbonic acid becomes more stable than the bicarbonate ion."
 
OP
-TH-

-TH-

Well-Known Member
Joined
Oct 22, 2008
Messages
913
Reaction score
108
Location
Zeeland, Michigan
The 1.22 factor only works if your original pH is 8.4 or less. Here in Austin, our municipal water is ~9.4, and the most recent water report shows "Total alkalinity (as CaCO3) = 72"... by the 1.22 that would make the HCO3 = 87.8, but due to the high pH, a different formula has to be used.

From: Alkalinity (As of CaCO3 to As of HCO3-): Practical guide on unit conversion | Coal Geology & Mining

http://coalgeology.com/wp-content/u...-convertion-spreadsheet-wwwcoalgeologycom.xls

From above spreadsheet: HCO3 = (Alkalinity as CaCO3 * 61) / (( 1 + ((2x10^-10.3)/(10^(-pH)))*50)

My calc comes out as 54, which is significantly off from the aforementioned 88...

Apparently, this is because "At higher pH carbonic acid becomes more stable than the bicarbonate ion."
Thanks for pointing that out. I've looked into this a bit now and I think I'll leave it alone for a few reasons: 1. Palmer's spreadsheet doesn't account for it.:D 2. Even up to a pH of 9 its still in the ball park (within 10%). 3. I'm not sure if it even matters since the mash will bring the pH down anyways.

Ironically my spreadsheet (and Palmer's) convert back to alkalinity as CaCO3 to calculate Residual Alkalinity (using the 1.22) so if you put in your number (times 1.22) it would later divide by 1.22 and you'd be fine. THe spreadsheet would only be off (possibly) for those who enter HCO3 directly and have high water pH.

Also, just FYI when I put your numbers into the calculator on the second link you gave, I got 70.2 for the calculated HCO3 (not 54).

Also, just FYI my virus software picks up a trojan threat in the first link you posted.

Cheers!
 

SpanishCastleAle

Well-Known Member
Joined
Jan 5, 2009
Messages
4,339
Reaction score
42
Location
Central Florida
Thanks for pointing that out. I've looked into this a bit now and I think I'll leave it alone for a few reasons: 1. Palmer's spreadsheet doesn't account for it.
Palmers spreadsheet let's you choose which unit...it's not obvious that it has this feature but it is there.
 
OP
-TH-

-TH-

Well-Known Member
Joined
Oct 22, 2008
Messages
913
Reaction score
108
Location
Zeeland, Michigan
Palmers spreadsheet let's you choose which unit...it's not obvious that it has this feature but it is there.
Yes but if you look at his formulas he converts using a straight conversion (50/61). He does not factor in water pH like maxhavoc pointed out.

I updated mine so that you can enter either number to make it easier.


Why don't you or Palmer include for Sodium Chloride (NaCl)?
I'm not sure, maybe because he cautions against having Na AND Cl both high (in other words he recommends keeping one or the other low). Anyways I updated mine now to include it.

EDIT: I just read Palmer again and he says keep Na paired with Sulfates low (not Cl) so scratch what I just said above.
 

MarsColonist

Well-Known Member
Joined
Jul 22, 2008
Messages
384
Reaction score
6
Location
Live Music Capital of the World
Also, just FYI when I put your numbers into the calculator on the second link you gave, I got 70.2 for the calculated HCO3 (not 54).

Also, just FYI my virus software picks up a trojan threat in the first link you posted.
I use AVG, and I didnt get any trojan notice... hmmm.
Also, Im now not sure how I got 54. His spreadsheet does indeed show 70.2.
Thanks for the heads up!:rockin: Now I have to see where my cali common stands for the salts I added
 
Top