EZ Water Adjustment spreadsheet

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

Help Support Homebrew Talk:

Bobby_M

Vendor and Brewer
HBT Sponsor
Joined
Aug 3, 2006
Messages
25,194
Reaction score
4,134
Location
Whitehouse Station
You can easily get both the Cl and SO4 up with Calcuim Chloride and Gypsum (CaSO4). Of course, it also boosts Calcium. A touch of MgSO4 wouldn't hurt either.
 

GilaMinumBeer

Half-fast Prattlarian
Lifetime Supporter
Joined
Jan 23, 2008
Messages
65,437
Reaction score
10,792
My chloride to sulfate is 2.2, leaning to the malty/sweet. BUT, there are low levels of both. This could explain a lot about my beer since I moved here. Its not bland, but its lacking something. Is there a recommended Chloride level so the flavor is good?

TH- thanks so much for your work on this spreadsheet!
IIRC from the Brew Strong podcast, John Palmer recommends something like 50-150 ppm as a general rule of thumb.

Look at teh results section. The numbers below the tabulated results are the "preffered range" and TH has even conditionally formatted them to change color if they are below or exceed the range.
 

jmo88

Well-Known Member
Joined
Sep 17, 2008
Messages
1,374
Reaction score
30
Location
Seattle
Am I missing something? I was hoping to put in my city or style as a goal for my water and have the sheet automatically plug in the numbers to adjust my water. I am finding that I am randomly pluggin in numbers to match the style and it is hit-or-miss. Mostly miss. Any tips would be appreciated.
 

Edcculus

Well-Known Member
Joined
Jun 6, 2007
Messages
4,539
Reaction score
57
Location
Morganton, NC
Am I missing something? I was hoping to put in my city or style as a goal for my water and have the sheet automatically plug in the numbers to adjust my water. I am finding that I am randomly pluggin in numbers to match the style and it is hit-or-miss. Mostly miss. Any tips would be appreciated.
This spreadsheet only helps you build your water. Brewater 3.0 will do what you are looking for.
 
OP
-TH-

-TH-

Well-Known Member
Joined
Oct 22, 2008
Messages
913
Reaction score
107
Location
Zeeland, Michigan
Am I missing something? I was hoping to put in my city or style as a goal for my water and have the sheet automatically plug in the numbers to adjust my water. I am finding that I am randomly pluggin in numbers to match the style and it is hit-or-miss. Mostly miss. Any tips would be appreciated.
Yeah I've been meaning to put some basic instructions on the page but haven't got around to doing it yet. In the meantime...

After you enter your starting water profile and volumes, take a look at your Residual Alkalinity and determine which way you need to adjust it based on the SRM of your recipe. To increase RA, you'll need to increase carbonates, so add either CaCO3 or NaHCO3. To decrease RA, you'll need to increase either Ca or Mg, so add either CaSO4, CaCl2, or MgSo4. The salts you choose to adjust RA will be determined by what you want your Cl to SO4 ratio to be (bitter, balance, malty, etc.) and the desired individual mineral levels (either based on recommended ranges or target city profiles). It is a bit hit and miss, but it doesn't take long to see how each salt affects things differently. Personally, I like the control you have with doing it this way.

Hope that helps!
 

jmo88

Well-Known Member
Joined
Sep 17, 2008
Messages
1,374
Reaction score
30
Location
Seattle
Thanks for the replies. Part of the problem is that I live in Seattle. Which might as well be Pilsen. After I get it close my sodium is through the roof. It's more of a lack of understanding of the salts on my part I guess. I'll take your suggestions and try them out. It is more approachable than palmer's. Nice work.
 

Bobby_M

Vendor and Brewer
HBT Sponsor
Joined
Aug 3, 2006
Messages
25,194
Reaction score
4,134
Location
Whitehouse Station
I don't know if it would help anyone but I thought it was convenient to replace the word "grams" with "pH/RA up" or pH/RA down for each of the salt additions. I know the additions are all in grams but it's a learning curve before you remember the RA effect each has. I know brewater3.0 does the automatic thing but frankly, I want to learn how to build it. Once I can consistently do it in a few trials, I may reconsider.
 

LeeF

Well-Known Member
Joined
Dec 15, 2008
Messages
132
Reaction score
0
Location
Alabama
TH,

I like how simple your spreadsheet is. Very user friendly. Cheers.

Your spreadsheet automatically makes the same salt additions to the mash as to the boil kettle based on the ratio of mash to sparge water. But what if you need to increase the RA for a darker beer and need to adjust the Cl:SO4 ratio? The RA is more important in the mash? Now if you need to adjust your Cl:SO4 ratio by adding either gypsum or CaCl2 you are decreasing the RA and fighting the additions you are making to increase the RA. Would it be better to add the Chalk to the mash and the gypsum or CaCl2 to the boil kettle?

Lee
 

Bobby_M

Vendor and Brewer
HBT Sponsor
Joined
Aug 3, 2006
Messages
25,194
Reaction score
4,134
Location
Whitehouse Station
Not exactly, at least in my mind. All of the salts except for NaCl have a mash pH effect. The only salt that I don't duplicate proportionally in the boil kettle is baking soda. While it does have an overall flavor impact via the Na, it's usually not something I'm trying to boost. Basically I just ignore the BK addition for that.
 

jmo88

Well-Known Member
Joined
Sep 17, 2008
Messages
1,374
Reaction score
30
Location
Seattle
Quick question after playing with the numbers and attempting to adjust Seattle's water, which, again, is like Pilsen. It is impossible to bring RA high enough for dark beers and have calcium or sodium in the recommended guidelines. Chalk raises calcium and Baking soda raises sodium. So my question is, should I just get it close enough and adjust with 5.2? I assume RA is related to ideal PH of the mash, correct? THEN, I can focus my salts on the balance of malt and bitter.

If you couldn't make dark beers with your water, would you get it close and adjust with 5.2 and simply play with Chloride to sulfite ratio? (the quick question).
 

jmo88

Well-Known Member
Joined
Sep 17, 2008
Messages
1,374
Reaction score
30
Location
Seattle
Post your water stats.
Seattle, WA

Calcium(Ca): 17.0 ppm
Magnesium(Mg): 1.0 ppm
Sodium(Na): 4.0 ppm
Sulfate(SO4): 2.0 ppm
Bicarbonate(HCO3): 18.0 ppm
PH: 7.8 PH

Notes
Seattle. Relatively soft water with low mineral content.
 

JLem

Well-Known Member
Joined
Jan 27, 2009
Messages
3,644
Reaction score
185
Location
Attleboro
Seattle, WA

Calcium(Ca): 17.0 ppm
Magnesium(Mg): 1.0 ppm
Sodium(Na): 4.0 ppm
Sulfate(SO4): 2.0 ppm
Bicarbonate(HCO3): 18.0 ppm
PH: 7.8 PH

Notes
Seattle. Relatively soft water with low mineral content.

Well, if you add 1 gram of chalk and 1.5 grams of baking soda per gallon of mash water, you can get your RA up to 292 (best for 29-34 SRM beer colors) and still keep your Na and Ca in the recommended ranges. Not sure you need to worry about getting the RA value any higher than that.

What is your Chloride value?
 

jmo88

Well-Known Member
Joined
Sep 17, 2008
Messages
1,374
Reaction score
30
Location
Seattle
Chloride is 4ppm. So the chloride to sulfate ratio will then dictate the perception of malt or hops. It's starting to clear up for me. So as long as my RA is in range for my SRM I should hit close to 5.2, correct? I understand it as this:

1. Have all ions in recommended ranges if possible
2. Create appropriate RA for SRM to achieve close to 5.2 mash pH
3. Alter the ratio of Chloride to Sulfate to bring out the style and flavor profile.

right???
 

Bobby_M

Vendor and Brewer
HBT Sponsor
Joined
Aug 3, 2006
Messages
25,194
Reaction score
4,134
Location
Whitehouse Station
I'd rather er on the side of a lower RA because I think missing the pH on the high side is worse than on the low side. Try the following... per gallon of water. Of course, you can reduce the overall sodium by skipping the baking soda addition to the boil kettle.

Starting Water:
Ca: 17 ppm
Mg: 1 ppm
Na: 4 ppm
Cl: 4 ppm
SO4: 2 ppm
HCO3: 18 ppm

Mash Vol: 1 gal
Dilution Rate: 0%

Adjustments:
CaCO3: 0.75 grams
CaSO4: 0 grams
CaCl2: 0.25 grams
MgSO4: 0.5 grams
NaHCO3: 1 grams
NaCl: 0 grams
HCL Acid: 0 ml
Lactic Acid: 0 ml

Results:
Ca: 114 ppm
Mg: 13 ppm
Na: 76 ppm
Cl: 36 ppm
SO4: 54 ppm
CaCO3: 269 ppm

RA: 180 (20 to 25 SRM)
Cl to SO4: 0.67 (Bitter)
 

jmo88

Well-Known Member
Joined
Sep 17, 2008
Messages
1,374
Reaction score
30
Location
Seattle
Thanks. With every post this is getting clearer. For the kettle additions, I want the water profile to be adjusted in the same way except for Calcium, right?

1. Do everything you listed above for the mash.
2. Add all salts in sane amounts/gl for additional water to kettle except calcium. Make up for the calcium with baking soda.

Is this what you mean?
 

Bobby_M

Vendor and Brewer
HBT Sponsor
Joined
Aug 3, 2006
Messages
25,194
Reaction score
4,134
Location
Whitehouse Station
No, I'm saying you'd do the same "per gallon" additions to the boil kettle except do not add baking soda. There's no reason I can think of to use baking soda in the boil if your sodium is already high enough and the HCO3 is for mash pH.

Apparently 1gram of NaHCO3 adds 72ppm of sodium in one gallon. Let's say you mash with 4gallons and sparge another 4 gallons for a preboil of 8 gallons. If you add 4 grams of NaHCO3 to the mash, you have 72ppm sodium. If you leave it out of the boil addition, the sodium effectively halves to 36ppm.

I can't think of a way that TH could facilitate that kind of flexibility without also making the spreadsheet obnoxiously complicated.
 

jmo88

Well-Known Member
Joined
Sep 17, 2008
Messages
1,374
Reaction score
30
Location
Seattle
Understood. I am thinking the Calcium addition should also be left out of the boil since it can only be dissolved in the mash. It would be a useless addition and precipitate out anyway.
 

Bobby_M

Vendor and Brewer
HBT Sponsor
Joined
Aug 3, 2006
Messages
25,194
Reaction score
4,134
Location
Whitehouse Station
It will dissolve in the wort too. From what I understand, it won't dissolve in basic water usually with a pH of 8 or higher. The mash is acidic enough and so is wort.
 

jmo88

Well-Known Member
Joined
Sep 17, 2008
Messages
1,374
Reaction score
30
Location
Seattle
TH,

I can only use your online version of the spreadsheet. I can't select the Alkalinity button on the excel sheet, but I can on the webpage. I am using excel for mac version 12.0. Is this because of my version? Just wondering if it's a simple fix.
 
OP
-TH-

-TH-

Well-Known Member
Joined
Oct 22, 2008
Messages
913
Reaction score
107
Location
Zeeland, Michigan
No, I'm saying you'd do the same "per gallon" additions to the boil kettle except do not add baking soda. There's no reason I can think of to use baking soda in the boil if your sodium is already high enough and the HCO3 is for mash pH.

Apparently 1gram of NaHCO3 adds 72ppm of sodium in one gallon. Let's say you mash with 4gallons and sparge another 4 gallons for a preboil of 8 gallons. If you add 4 grams of NaHCO3 to the mash, you have 72ppm sodium. If you leave it out of the boil addition, the sodium effectively halves to 36ppm.

I can't think of a way that TH could facilitate that kind of flexibility without also making the spreadsheet obnoxiously complicated.
Ok I worked on this a while and I think I found a good way to allow for some control of the sparge water adjustments (including the ability to skip some) while keeping the spreadsheet somewhat simple to use. While it may be approaching "obnoxiously complicated", it shouldn't appear that way to the user. Now me on the other hand....

See post #1 or my signature for the latest version 1.5.
Note: The online version does not have this.
 

Bobby_M

Vendor and Brewer
HBT Sponsor
Joined
Aug 3, 2006
Messages
25,194
Reaction score
4,134
Location
Whitehouse Station
TH, you are my hero at the moment. The spreadsheet is becoming just a touch more complex but I really think it's more useful.

There are two things that I've tweaked on my local copy and you may choose to implement or not. First. On the additions box, I put an indicator as to whether it makes RA go up or down. That helps me choose additions. I'm sure I'll eventually learn and not need it, but for now, it's nice.

The other thing is providing more complete output in the raw format page. Here's what I did:

Starting Water, in ppm:
Ca: 30
Mg: 10
Na: 28
Cl: 53
SO4: 15
CaCO3: 59

Mash/Sparge Vol in gallons 5 * 3
Dilution Rate: 0% *

Adjustments in grams/ml mash and boil kettle:
CaCO3: 1 * 0.6
CaSO4: 2 * 1.2
CaCl2: 3 * 1.8
MgSO4: 4 * 2.4
NaHCO3: 5 * 0
NaCl: 6 * 3.6
HCL Acid in ml: 0 * 0
Lactic Acid in ml: 0 * 0

Mash Water/Total water in ppm:
Ca: 118 * 118
Mg: 30 * 30
Na: 225 * 198
Cl: 321 * 321
SO4: 156 * 156
CaCO3: 282 * 211

RA for Mash Only: 180 (20 to 25 SRM)
Cl to SO4 for Total Water 2.06 (Very Malty)


Enough of the formatting remains in the paste that you can provide both the mash/BK additions and mash profile/total profile in the same rows respectively without garbling the data. Also note the RA is really only pertinent to the mash and Cl/SO4 ratio only pertinent to the total water.

The way I use this now is formulate my desired profile, make trial and error adjustments and then copy/paste the raw data into my NOTES window in Beertoolspro. Unfortunately, BTP doesn't really do water very well and it doesn't export its own water additions on the brewsheet print out but it DOES print out the notes... which now contains data derived for this spreadsheet.

Seriously, thanks for putting the work into this. It's what I'm recommending to all my brewclub members (if they want my help with it anyway).
 

GilaMinumBeer

Half-fast Prattlarian
Lifetime Supporter
Joined
Jan 23, 2008
Messages
65,437
Reaction score
10,792
I am just sad I haven't had the time to brew with this tool yet.

I have enjoyed it's progression and commend you for the time it took/takes.

Excellent work from a guy that, before this, was clueless and dis-interested in making water adjustments.

I am still clueless. But at least now I am interested. :p
 
OP
-TH-

-TH-

Well-Known Member
Joined
Oct 22, 2008
Messages
913
Reaction score
107
Location
Zeeland, Michigan
There are two things that I've tweaked on my local copy and you may choose to implement or not. First. On the additions box, I put an indicator as to whether it makes RA go up or down. That helps me choose additions. I'm sure I'll eventually learn and not need it, but for now, it's nice.

The other thing is providing more complete output in the raw format page.
Good suggestions. I made both changes and re-uploaded. Give it a look to see if that's what you had in mind.

BTW Bobby I'm glad you like it and I thank you for your support. Its the least I can do considering how much I learned from your website, videos, etc. In my opinion you've put together some of the best stuff out there!
 

Bobby_M

Vendor and Brewer
HBT Sponsor
Joined
Aug 3, 2006
Messages
25,194
Reaction score
4,134
Location
Whitehouse Station
It's done as far as I'm concerned. I'll be doing a short tutorial showing how I use it sometime this week. I really wish I had a decent little preamp because my PC mic input sucks bad. Thanks.
 

JerD

Well-Known Member
Joined
Jun 29, 2008
Messages
168
Reaction score
0
Location
NW Indiana
I'm loving the heck out of this tool. One more question though. Is the dilution rate figured in for the boil additions, or just the mash water? If I figure for 40%dilution, I just take six gallons of source water and 4 gallons of distilled water and blend it all together to make the dilution process simple.
 

Bobby_M

Vendor and Brewer
HBT Sponsor
Joined
Aug 3, 2006
Messages
25,194
Reaction score
4,134
Location
Whitehouse Station
It assumes dilution of the total water so if you have 4 gallons mash and 6 gallons sparge with a 50% dilution, it assumes 2 gallons mash and 3 gallons sparge is distilled.
 

Joetuo

Well-Known Member
Joined
Sep 28, 2006
Messages
65
Reaction score
0
Location
Roselle, IL
After reading the thread and watching bobby's video's (thanks a lot) I just want to clear up one thing....

If all my numbers are in the proper range (all green) then I don't have to worry about the Ph of my mash?
 

Bobby_M

Vendor and Brewer
HBT Sponsor
Joined
Aug 3, 2006
Messages
25,194
Reaction score
4,134
Location
Whitehouse Station
No, you still do. The proper range thing is only in regards to each individual ion and says nothing about mash pH because it doesn't know the SRM of the beer you're trying to brew. If you watch the 3rd video, I talk about dialing in your residual alkalinity so that the SRM range on the bottom of the sheet matches what you're trying to brew.
 

Joetuo

Well-Known Member
Joined
Sep 28, 2006
Messages
65
Reaction score
0
Location
Roselle, IL
Ok so basically i just need to get all the numbers green and make sure the SRM is in range of what I am brewing then I am golden.
 

weetodd

Well-Known Member
Joined
Oct 30, 2006
Messages
197
Reaction score
1
Location
Seattle. WA
Really great spreadsheet, thanks for creating it and for also making it available for download. Also, thanks Bobby for the great videos.

One question on the initial numbers for mash water volume and sparge water volume. For example purposes let's say I mash with 5 gallons, sparge with 3 for 8 total preboil and expect a post boil volume of 6. The mash water one is easy--just the volume of water that is used in the initial mash (5). But, for the sparge water? In Bobby's videos, he uses the full amount of the sparge water (the additional 3 in my example) to get additions for the full pre-boil volume--so we're adding enough minerals to get the right ppm for 8 gallons. After thinking about this, doesn't the boil remove water but leave the minerals behind? (I'm not a scientist so not sure if the minerals would evaporate with the water vapor) So should the mineral additions calculate off of how much minerals we want with the finished beer (6 gallons vs. preboil of 8)

Thanks
 
Top