EZ Water Adjustment spreadsheet

[jmo88]

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).

 

[Bobby_M]

Post your water stats.

 

[jmo88]

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]

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]

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]

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]

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]

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]

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]

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]

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.

 

[-TH-]

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]

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).

 

[Bobby_M]

Oh, do you mind if I do a video tutorial on how to use this thing?

 

[-TH-]

Oh, do you mind if I do a video tutorial on how to use this thing?

That would be awesome!

I will look into the other things you mentioned as well.

 

[ShortSnoutBrewing]

Oh, do you mind if I do a video tutorial on how to use this thing?

Please do Bobby!! :rockin:

 

[GilaMinumBeer]

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.

 

[-TH-]

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]

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]

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]

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.

 

[JerD]

Awesome
Thanks alot

 

[Joetuo]

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]

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]

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.

 

[wildwest450]

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.

With an srm of 6 or less.

 

[Joetuo]

ROFL I see what you did there.

 

[weetodd]

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

 

[Brew-boy]

Greta tool TH and very nice video Bobby, Thanks for all your help guys.

 

[gruversm]

It works, you just need a couple of old VB dlls...

Google and download VBRUN300.dll and cmdialog.vbx to the same directory as BREWATER.exe.

Norton Internet Security is flagging the executable as a possible Virus.