EZ Water Adjustment spreadsheet

Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum

Help Support Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum:

This site may earn a commission from merchant affiliate links, including eBay, Amazon, and others.
Finally, a one screen calculator that I can actually use.

I have never been much to target a specific cities water but, I have always been interested in doing a better job with the water I get. I am thinking this might be the catalyst to that.

Thanks.
 
I think of water profiles of popular brewing cities almost like recipes. They are good to use when you're first learning, but eventually you figure out what you like and you design it on your own later. It gets harder it seems when you want to brew a style that was maybe adapted from a London style and later Americanized. I mean, I did a smoked porter so I sort of looked at English/Scottish water profile but I doubt Stone/Alaskan model to London. Who knows.
 
Any way to include a pH calculator tool similar to that on BreWater? I just dunno what the math is.

I assume that when the sparge water value is non-zero, then the boil additions are calculated to keep the water balanced throughout the brew session? That is, if the spreadsheet calculates a value, then this is what is needed to be added to the kettle?

Sorry if this is covered in the Palmer SS. I am just not familiar with it.
 
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!

Wow! By my latest report the total alk of my tap water is 37 and my pH is a whopping 10.

Plugging those numbers into the coalogy calculator returns a HUGE difference between the simple and complex calcs. By the simple method(x1.22), the return is 45.1. By the complex method, the return is 23.

It would seem, if the calculator is correct, that pH does make a big difference.

PS: Norton 360 reports that "Nine Ball" is piggy backing that link.
 
I think of water profiles of popular brewing cities almost like recipes. They are good to use when you're first learning, but eventually you figure out what you like and you design it on your own later. It gets harder it seems when you want to brew a style that was maybe adapted from a London style and later Americanized. I mean, I did a smoked porter so I sort of looked at English/Scottish water profile but I doubt Stone/Alaskan model to London. Who knows.

That is exactly how it should work. There is enough information out there on the interweb to put together a target profile based on intended flavor rather then on location.
 
That is exactly how it should work. There is enough information out there on the interweb to put together a target profile based on intended flavor rather then on location.

I tend to just pay attention to my residual alkalinity, sulfate/chloride ratio and minimum amounts for yeast health of ions. I also watch out that I don't have a crazy amount of sodium or other minerals.
 
Any way to include a pH calculator tool similar to that on BreWater? I just dunno what the math is.

I assume that when the sparge water value is non-zero, then the boil additions are calculated to keep the water balanced throughout the brew session? That is, if the spreadsheet calculates a value, then this is what is needed to be added to the kettle?

Sorry if this is covered in the Palmer SS. I am just not familiar with it.

If you are talking about mash pH, I kind of intentionally left that off because I chose to use the Residual Alkalinity value to determine and show the right range for the color in order to bring the mash pH into the proper range (5.2 to 5.6 @ mash temp). I could have used/shown the "100% base malt mash pH" instead/also but I think for most people that concept is too confusing.

The pH calculator in BreWater appears to be only for figuring acid adjustments? Mine figures those into the RA number.

Your assumption about the boil additions is exactly right.
 
Wow! By my latest report the total alk of my tap water is 37 and my pH is a whopping 10.

Plugging those numbers into the coalogy calculator returns a HUGE difference between the simple and complex calcs. By the simple method(x1.22), the return is 45.1. By the complex method, the return is 23.

It would seem, if the calculator is correct, that pH does make a big difference.

PS: Norton 360 reports that "Nine Ball" is piggy backing that link.

Hmmm. That pH is insane. I thought I heard palmer say on brewstrong that nobody's pH is above 9 and if it was then...something something. Sure wish now that I remembered what that something was. Anyways I think I'll put that calculation in the spreadsheet, but before I do I want to look into a couple things first. Stay tuned.
 
If you are talking about mash pH, I kind of intentionally left that off because I chose to use the Residual Alkalinity value to determine and show the right range for the color in order to bring the mash pH into the proper range (5.2 to 5.6 @ mash temp). I could have used/shown the "100% base malt mash pH" instead/also but I think for most people that concept is too confusing.

The pH calculator in BreWater appears to be only for figuring acid adjustments? Mine figures those into the RA number.

Your assumption about the boil additions is exactly right.

Nah. I was just thinking of a titration tool. Starting pH to Target pH = XmL Acid. You know, for a ball park estimate. This way I'd only need to have one page open. I tried to cut and paste from Palmers but there are links in there to a hidden "scratch" page or s'thing (I haven't taken the time to figure out the links basically).

It's cool, I'll figure s'thing out. If you add everything it'll get cluttered like Palmers and the others.

As for the HCO3 calculator, I just cut and paste the calc to a new worksheet.
 
Hmmm. That pH is insane. I thought I heard palmer say on brewstrong that nobody's pH is above 9 and if it was then...something something. Sure wish now that I remembered what that something was. Anyways I think I'll put that calculation in the spreadsheet, but before I do I want to look into a couple things first. Stay tuned.

This is the highest I have ever seen it. However, it usually hovers around 9.2 to 9.3. I am fortunate enough to get copy of the water chemistry analysis from the city lab on a monthly basis.

Curious what that "something something" is.
 
I tend to just pay attention to my residual alkalinity, sulfate/chloride ratio and minimum amounts for yeast health of ions. I also watch out that I don't have a crazy amount of sodium or other minerals.

My thoughts exactly. I'm not concerned at all with matching my water to a particular region (at least not yet). My feeling is that certain styles came out better in certain regions because their water was naturally better suited for the appropriate style, even though at the time they might not even have known why. Now that we know how to adjust water (RA, sulfate/chloride ratio, etc.) for a particular style (color, bitterness/sweetness, etc.) then matching my water to a particular region per se becomes almost moot IMHO.
 
I really like this spreadsheet so I don't mind providing more feedback, criticism and requests :mug:

I've been playing with brewater3.0, brewer's friend, palmer's and this spreadsheet. I see that you added the common profiles on another sheet. Very cool. What's missing is a way to pull one of those target profiles in. I don't know excel all that well but I'm pretty sure VLOOKUP would work for that. Just set the dropdown on the city data and pull the rest in a row near the results. I find it difficult to design my results around a target without the target on the sheet to compare values. If the data can be overwritten on the front sheet, all the better for those who just want to input their own target.

Last, it would be awesome to have a 3rd workbook that arranges the data in a copy/paste format that looks good in plain text. For example:

Original Water
Ca 15
Mg 10
etc

Additions
CaCO3 5 grams
etc

Resulting Water
Ca 22
Mg 14
etc

This sheet could just pull the cell data from the front sheet. I really think this option would put it above all the other tools because people want to talk about their water and how to fix it and doing screen prints is a PIA.

What do you think?
 
I really like this spreadsheet so I don't mind providing more feedback, criticism and requests :mug:

I've been playing with brewater3.0, brewer's friend, palmer's and this spreadsheet. I see that you added the common profiles on another sheet. Very cool. What's missing is a way to pull one of those target profiles in. I don't know excel all that well but I'm pretty sure VLOOKUP would work for that. Just set the dropdown on the city data and pull the rest in a row near the results. I find it difficult to design my results around a target without the target on the sheet to compare values. If the data can be overwritten on the front sheet, all the better for those who just want to input their own target.

Last, it would be awesome to have a 3rd workbook that arranges the data in a copy/paste format that looks good in plain text. For example:

Original Water
Ca 15
Mg 10
etc

Additions
CaCO3 5 grams
etc

Resulting Water
Ca 22
Mg 14
etc

This sheet could just pull the cell data from the front sheet. I really think this option would put it above all the other tools because people want to talk about their water and how to fix it and doing screen prints is a PIA.

What do you think?

It would be my pleasure...

EZ_water_adjustment.xls
Version 1.3

Give it a try and let me know what you think!
 
I'm a bit confused about one thing (many things actually, but more about that later). For mash additions, I add x amount of Calc. Chloride to get where I need to be. Now I need to add gypsum to get my chloride/sulfate ratio where I need it. If I put a value in the mash addition field, is the number that pops up in the boil addition field the actual amount to add to boil in lieu of a mash addition?
 
I'm a bit confused about one thing (many things actually, but more about that later). For mash additions, I add x amount of Calc. Chloride to get where I need to be. Now I need to add gypsum to get my chloride/sulfate ratio where I need it. If I put a value in the mash addition field, is the number that pops up in the boil addition field the actual amount to add to boil in lieu of a mash addition?

The mash additions are what you add to the mash, AND the boil additions you add to the boil. That way the total amount of water used is being treated (for best results). The boil addition amounts are based on sparge water because the mash water has already been accounted for. The only reason you add it to the boil and not the sparge water itself is because according to palmer the salts dissolve better that way.
 
The mash additions are what you add to the mash, AND the boil additions you add to the boil. That way the total amount of water used is being treated (for best results). The boil addition amounts are based on sparge water because the mash water has already been accounted for. The only reason you add it to the boil and not the sparge water itself is because according to palmer the salts dissolve better that way.

Maybe I'm just dumb, but I'm still lost on this. Let's say you have 11.5 gallons of water to use. Some in your Mash, the remainder for your sparge. I was under the assumption that I treated all 11.5 gallons with the "mash addition" and then treated my runoff with the "boil addition". Is that not correct?
 
Maybe I'm just dumb, but I'm still lost on this. Let's say you have 11.5 gallons of water to use. Some in your Mash, the remainder for your sparge. I was under the assumption that I treated all 11.5 gallons with the "mash addition" and then treated my runoff with the "boil addition". Is that not correct?

You need to make additions to your water 2 times. Once to account for mash water, and once to account for sparge water. Calculators and spreadsheets usually use the mash volume for the calculations because thats what affects mash pH due to residual alkalinity. The reason you add again for sparge water is to maintain the flavor profile (cloride to sulf ratio, calcium levels, etc). The sparge water adjustment is done in the boil because the salts will dissolve better and not be left behind in the mash. Now I suppose a calculator could use total volume of water to do its calcs, but then you'd still have to figure the breakdown of salts to be added to the mash and to the boil. Even if you could treat all your water at once before hand Palmer says the salts won't dissolve well in plain water therefore you have to add them to the mash and the boil.

Make sense?
 
Maybe I'm just dumb, but I'm still lost on this. Let's say you have 11.5 gallons of water to use. Some in your Mash, the remainder for your sparge. I was under the assumption that I treated all 11.5 gallons with the "mash addition" and then treated my runoff with the "boil addition". Is that not correct?

You make the two salt additions. One to the Mash Tun and the other to the Boil Kettle, not the HLT. I put my first addition salts on top of the grain right after I crush. Dump it in the Mash Tun together. Second addition I add to the Boil Kettle at the beginning of the sparge collection.
 
I think so. Let me state another way to make sure I'm getting it. First, I don't treat the water beforehand, but rather treat it once its already in the MLT. At that point I add the mash addition. Do my normal thing, drain, sparge, drain, etc. Once I have collect all my wort I then add the boil addition.


A little slow I guess....max seems to confirm above. Thanks.
 
I think so. Let me state another way to make sure I'm getting it. First, I don't treat the water beforehand, but rather treat it once its already in the MLT. At that point I add the mash addition. Do my normal thing, drain, sparge, drain, etc. Once I have collect all my wort I then add the boil addition.


A little slow I guess....max seems to confirm above. Thanks.

Think of it as hop additions, do a mash hop addition, then a 60 minute addition.:D
 
I was under the assumption that Chalk is the only thing that HAS to be added to the mash tun where everything else would dissolve enough in the strike water in the HLT.

I had thought that too at one point, but when I did a brew several months ago I added gypsum to the HLT and I was washing a lot of it out of the HLT when I was done brewing, so I know most of it didn't dissolve, maybe some did though.
 
I was under the assumption that Chalk is the only thing that HAS to be added to the mash tun where everything else would dissolve enough in the strike water in the HLT.

I understood that most salts need to be added a couple minutes after doughing in so the PH goes down and they can be dissolved quicker.
 
I am still trying to figure out how to find my target water profiles by style. My next two brews are a blonde and a belgian blonde. What cities would I select, or in Brewater 3.0, what target should I load?
 
I think it's more art than science in choosing a profile. I would just be concerned with getting Ca/Mg in range per Palmer's recommendation for enzyme/yeast health and getting the Cl/S04 to the balanced side. 1:1 or 1:2. After that, make sure the RA matches up with 3-6 SRM or so.
 
Is there any benefit to adjusting your water, say to "balanced", when extract brewing? Or does the extract water profile override this? I've looked at other threads, Palmer's book, CP's book, but I have to think that the water is probably one of the most important ingredients in beer! I'm not sure I completely buy into "if it tastes good enough to drink, it's good enough to brew with. Just looking to control all that is controllable in brewing.
 
I think it's best to assume the current makeup of extract is balanced and if your water is not, and you'd like it to be balanced, you should modify your water also. However, I'd rather LOWER the ion concentration of the water in the process if you're already working with hard water. Do you have a water report?
 
I'm working on obtaining the report now. I do know the pH is 7.5 and chlorine is 3.2 mg/l. That's all I have right now.
 
Wow, this stuff is mind numbing! I've been following this thread, listening to Brew Strong and reading as much as I can. I got my water report today and feel like I know absolutely nothing! This spreadsheet is awesome. Tried Brewater, and didn't really like it. I don't like how it makes you calculate additions based on a target profile. From what I have picked up, its better to emulate ratios than the exact mineral content of a classic brewing city.

I thought Bored's post a few pages back was one of the best explanations of chloride to sulfate ratios I've heard yet.

Here is my profile:
Starting Water:
Ca: 33 ppm
Mg: 9 ppm
Na: 15 ppm
Cl: 22 ppm
SO4: 10 ppm
CaCO3: 92 ppm

From the spreadsheet, it looks like my yeast health ions are a little low. I'll probably need to bump those up no matter what recipe I'm planning. My RA is 63. Not terrible. If I plan on brewing a lighter beer, is it better to get that down with distilled water, or is it even a huge deal?

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!
 
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?

IIRC from the Brew Strong podcast, John Palmer recommends something like 50-150 ppm as a general rule of thumb.
 
Back
Top