Author Topic: Using water spreadsheets  (Read 2140 times)

Offline dean

  • Brewmaster
  • *****
  • Posts: 922
  • Me and Hayden, my newest grandson.
    • View Profile
Using water spreadsheets
« on: January 21, 2010, 06:23:09 AM »
I've been messing around with three different spreadsheets, ezcalc, Palmer's and Kai's.  They are all different and so far I think I like Kai's because it seems to be more straight forward and gives a projected pH of the mash.  Palmer's spreadsheet shows another value that I'm not sure about, it gives an analysis of the outcome (I think?) when it states "malty", "balanced", "bitter", "very bitter".  I'm not sure where this fits in unless its in regards to hop usage?   ???

Offline Kaiser

  • Global Moderator
  • Senior Brewmaster
  • *****
  • Posts: 1797
  • Imperial Brewing Geek
    • View Profile
    • braukaiser.com
Re: Using water spreadsheets
« Reply #1 on: January 21, 2010, 07:40:52 AM »
Palmer's spreadsheet shows another value that I'm not sure about, it gives an analysis of the outcome (I think?) when it states "malty", "balanced", "bitter", "very bitter".  I'm not sure where this fits in unless its in regards to hop usage?   ???

I think you are talking about the chloride to sulfate ratio. I haven’t looked much into this subject and John had more data for that. When I design water I just look at the chloride and sulfate numbers. I’ll go for higher sulfate for hoppy beers and lower for malty beers. I have yet to so dome experiments to experience the difference which is why this analysis of the water is missing from my spreadsheet.

Kai

Offline Thirsty_Monk

  • Brewmaster General
  • *******
  • Posts: 2189
  • Eau Claire WI
    • View Profile
Re: Using water spreadsheets
« Reply #2 on: January 21, 2010, 09:15:35 AM »
I already posted this in another thread but will post it here again:

There is nice video how to use ezWaterCalculator:
http://www.homebrewtalk.com/f128/water-modification-videos-ths-spreadsheet-144461/
You can get it from here.
http://www.ezwatercalculator.com/

I also tried Palmer's spreadsheet but I think that ezWaterCalculator is simpler.
Never tried Kai's spreadsheet.
Na Zdravie

On Tap At The TapRoom:
Bohemian Pilsner
Bohemian Dark Lager
Smoked Bock
MaiBock
American Brown Ale
Marzen
Root beer

Offline Kaiser

  • Global Moderator
  • Senior Brewmaster
  • *****
  • Posts: 1797
  • Imperial Brewing Geek
    • View Profile
    • braukaiser.com
Re: Using water spreadsheets
« Reply #3 on: January 21, 2010, 09:41:55 AM »
Why not talk about what brewers are looking to get from a water spreadsheet.

One challenge, which I hopefully addressed to some extend, is providing a simple interface to a potentially complex problem. That’s why I have the “basic” and “advanced” sheets in mine. They are connected such that if you start entering values into the basic sheet they are carried into the advanced sheet and you can use that if you feel you need more info. It doesn’t work the other way.

Water treatment options can be overwhelming and I don’t want to scare brewers away by having too many options. On the other hand, there are so many water treatment options and I want to be able to provide guidance and calculations for most of them. In addition to the basic addition of salts and acids I have played with dissolving chalk and, just recently, successfully applied lime treatment of brewing water. I’ll be releasing an update that includes that calculation as well.

I’m also not supporting hydrochloric acid for mash pH treatment. It is a very dangerous acid and not for casual use in brewing. If you have access to food grade HCl and not just HW store muriatic acid you are likely knowledgeable enough to do update the spreadsheet to support it. Lactic acid as acidulated malt or 88% solution is a much safer option.

Another design aspect, which I considered, is having a layout that is mobile device friendly. Many of us have handheld devices capable of running spreadshets which we are using on brewday or even for quick recipe deign. Hence the arrangement of the fields in a column on the right.

Here is the link to the current version: http://braukaiser.com/documents/Kaiser_water_calculator.xls

Just in case you don't know it already: check out OpenOffice.org which is a free office suite that also supports Excel spreadsheets.

Kai

Offline dean

  • Brewmaster
  • *****
  • Posts: 922
  • Me and Hayden, my newest grandson.
    • View Profile
Re: Using water spreadsheets
« Reply #4 on: January 21, 2010, 12:56:07 PM »
If we plug the numbers into John Palmer's spreadsheet it gives us the descriptions I mentioned in the first post, I guess my question is should we be looking for "balanced"?  I plugged in the numbers for the Burton India Pale Ale as the target water and it gave a description of "very bitter" for Burton's water, which I expect the India Pale Ale is but the spreadsheet asked nothing about the pH of Burton water... so how could it come to that conclusion?  Or is it saying with the pH of my water it would be bitter?  Or are those the mineral amounts in the particular beers mentioned and not the water for each of the named beers?

Oh yeh, I can see where water additions could get complicated.   :D 

I'll probably just try my hand at it and hopefully take good enough notes that I'll be able to duplicate a brew if I like how it turns out. 

Offline Kaiser

  • Global Moderator
  • Senior Brewmaster
  • *****
  • Posts: 1797
  • Imperial Brewing Geek
    • View Profile
    • braukaiser.com
Re: Using water spreadsheets
« Reply #5 on: January 21, 2010, 01:14:31 PM »
but the spreadsheet asked nothing about the pH of Burton water

The only time we actually would care about the pH of the brewing water is if the alkalinity is not known and we need to calculate it from the bicarbonate content. But even for that conversion do we make the assumption that all alkalinity is caused by bicarbonate. In waters with a pH higher than ~9.0 this is assumption starts to fall apart but the EPA limit for drinking water pH is 8.5.

The malty/balanced/bitter calculation is based soley on the chloride to sulfate ratio. What you are thinking of is the increased hop extraction and thus increased bitterness of beers brewed with a high mash and subsequently high wort boil pH. That is not considered in any spreadsheet I have seen. Ideally the wort pH should be reflected in the IBU estimations that we are using but none of the authors that provided an IBU estimation formula considered wort boil pH.

I hope that clears up some confusion.

Kai

Offline a10t2

  • Official Poobah of No Life.
  • *
  • Posts: 3695
  • Ask me why I don't like Chico!
    • View Profile
    • SeanTerrill.com
Re: Using water spreadsheets
« Reply #6 on: January 21, 2010, 01:47:21 PM »
What you are thinking of is the increased hop extraction and thus increased bitterness of beers brewed with a high mash and subsequently high wort boil pH. That is not considered in any spreadsheet I have seen. Ideally the wort pH should be reflected in the IBU estimations that we are using but none of the authors that provided an IBU estimation formula considered wort boil pH.

Kai: do you know, either from experimental data or theory, what the effect of pH on isomerization would actually be? It isn't something I've ever looked into, and I'm wondering if there's a substantial variation within the "normal" (5.3-5.7 or so) mash pH range. Or when you say "high pH" do you mean well outside of the normal targeted range?
Beer is like porn. You can buy it, but it's more fun to make your own.
seanterrill.com/category/brewing
twomilebrewing.com

Offline dean

  • Brewmaster
  • *****
  • Posts: 922
  • Me and Hayden, my newest grandson.
    • View Profile
Re: Using water spreadsheets
« Reply #7 on: January 21, 2010, 04:31:59 PM »
but the spreadsheet asked nothing about the pH of Burton water

The only time we actually would care about the pH of the brewing water is if the alkalinity is not known and we need to calculate it from the bicarbonate content. But even for that conversion do we make the assumption that all alkalinity is caused by bicarbonate. In waters with a pH higher than ~9.0 this is assumption starts to fall apart but the EPA limit for drinking water pH is 8.5.

The malty/balanced/bitter calculation is based soley on the chloride to sulfate ratio. What you are thinking of is the increased hop extraction and thus increased bitterness of beers brewed with a high mash and subsequently high wort boil pH. That is not considered in any spreadsheet I have seen. Ideally the wort pH should be reflected in the IBU estimations that we are using but none of the authors that provided an IBU estimation formula considered wort boil pH.

I hope that clears up some confusion.

Kai


I think I overthought it and confused myself, the ppm's for the listed beers are the mineral profiles of those beers if we wanted to adjust our profiles to emulate them ... I think.   :D

Offline dean

  • Brewmaster
  • *****
  • Posts: 922
  • Me and Hayden, my newest grandson.
    • View Profile
Re: Using water spreadsheets
« Reply #8 on: January 22, 2010, 05:10:12 PM »
Hey Kai, I don't know if its just me but I can't input a value in the field for Lactic Acid and have it do anything that I can see?  Maybe I'm doing something wrong, I'm not a computer guru or anything like that so it wouldn't be a surprise.  I have lactic acid available so I'd like to be able to use it in my calculations, I'm going to order acidified malt though because your program works with it and it would be as easy as doing a normal mash anyway but I would still like to be able to use the lactic acid, is there something I'm not doing right?  No matter what I enter it doesn't change the mash pH right now.

Offline Kaiser

  • Global Moderator
  • Senior Brewmaster
  • *****
  • Posts: 1797
  • Imperial Brewing Geek
    • View Profile
    • braukaiser.com
Re: Using water spreadsheets
« Reply #9 on: January 23, 2010, 06:07:32 AM »
I'll check that. You schould be able to change both the amount and strength of LA.

Kai

Offline dee

  • Cellarman
  • **
  • Posts: 65
    • View Profile
Re: Using water spreadsheets
« Reply #10 on: January 23, 2010, 11:04:36 AM »
I already posted this in another thread but will post it here again:

There is nice video how to use ezWaterCalculator:
http://www.homebrewtalk.com/f128/water-modification-videos-ths-spreadsheet-144461/
You can get it from here.
http://www.ezwatercalculator.com/

I also tried Palmer's spreadsheet but I think that ezWaterCalculator is simpler.
Never tried Kai's spreadsheet.
I just downloaded the ezWaterCalculator and agree that it is very easy to use and probably easier for someone new to water adjustments.  Palmer's spreadsheet is a little intimidating and it took me a while to understand what I was looking for.  I didn't understand the relationship between additional alkalinity needed, additional alkalinity contributed and resulting mash RA.

Offline Thirsty_Monk

  • Brewmaster General
  • *******
  • Posts: 2189
  • Eau Claire WI
    • View Profile
Re: Using water spreadsheets
« Reply #11 on: January 23, 2010, 12:41:32 PM »
Those video tutorals are great help too.
Na Zdravie

On Tap At The TapRoom:
Bohemian Pilsner
Bohemian Dark Lager
Smoked Bock
MaiBock
American Brown Ale
Marzen
Root beer

Offline tygo

  • I spend way too much time on the AHA forum
  • ********
  • Posts: 2622
  • Sterling, VA
    • View Profile
Re: Using water spreadsheets
« Reply #12 on: January 24, 2010, 08:47:26 AM »
I like that EZ Water Calculator spreadsheet.
Clint
Wort Hogs

Fermenting: Wit
On Tap: Lucifer's Hammer Golden Strong Ale, Dopplebock, Old Fuzzynut's Ale

Offline mrcceo

  • Cellarman
  • **
  • Posts: 56
  • Location - Philadelphia Area
    • View Profile
Re: Using water spreadsheets
« Reply #13 on: January 24, 2010, 10:00:43 AM »
Kai:  I've been working between 3 popular calculators and so far I found that yours seems to give me the control I’m looking for.      I prefer to add acid malt to the mash in order to get as close as possible to the proper PH and your calculator has a section that allows for the addition as well as giving the estimated PH change. I noticed that when I add even the smallest amount of acid malt that the RA changes dramatically.  Can you explain this?

Offline Kaiser

  • Global Moderator
  • Senior Brewmaster
  • *****
  • Posts: 1797
  • Imperial Brewing Geek
    • View Profile
    • braukaiser.com
Re: Using water spreadsheets
« Reply #14 on: January 24, 2010, 03:11:54 PM »
I'll check that. You schould be able to change both the amount and strength of LA.

There was a simple bug in the spreadsheet that caused the lactic acid amount entered on the "basic" sheet not to be carried over to the "advanced" sheet. That's why you didn't see any effect of it.

I noticed that when I add even the smallest amount of acid malt that the RA changes dramatically.  Can you explain this?

One reason is that I have you enter it as % of the water weight and the other reason is that pretty big residual alkalinity changes are necessary to move the pH by only a few 10th pH units.

Kai