EZ Water Calculator 3.0

[-TH-]

I have a question about the EZ calculator. I don't have Excel, so I've been using openoffice. It's worked just fine, but I had to restore my computer on Sunday and am re-downloading everything.

When I try to use the spreadsheet now, I get a green box around the pull down grain selections and I can't pick a grain. The "anchor" symbol shows, and it will not let me select a grain. I'm sure it's my error, but can someone explain how to work around this/fix it? I'd really appreciate it!

I think maybe your openoffice is in "design mode". To toggle it on/off, find this button on one of the toolbars:

and make it not depressed (I couldn't find a way to do it through the pulldown menus).

Hope that helps!!!

 

[Aschecte]

Wow this spreadsheet is really opening doors for me to step up my brewing. Thank you so much for developing such as easy to use format. One quick question for everyone I am very new to water chemistry and have a rudimentary understanding of the compounds and ions we are dealing with but is there any other sources for learning why a particular salt is favored over another and also reading this thread why RA is insignificant to a styles srm as for example Palmer has stated.

Sent from my iPhone using HB Talk

 

[ajdelange]

.... why a particular salt is favored over another and..

A salt is a delivery system for ions. Thus if one wants chloride ions he must use a chloride. There are several choices. Magnesium, potassium, sodium, calcium or ferric chloride would all deliver chloride ion. But each chloride salt also contains a cation. Iron would not be a good choice for this cation because it makes things taste metallic. Any of the others I listed may or may not be suitable depending on what you are trying to do. If you are trying to increase magnesium and chloride then magnesium chloride would be a good choice but it's not found at LHBS's. If you are trying to increase calcium (which you often are) then calcium chloride is a good choice (readily available and commonly added to brewing water). So you really need to understand what the various ions do. There are lots of books, articles, websites etc that have this information.

....reading this thread why RA is insignificant to a styles srm as for example Palmer has stated.

Palmer never said that! The problem is that people don't seem to recognize that he never said that. What he did say with respect to his SRM/RA nomograph and spreadsheet is that the relationship is "a handwave at best". The reason it's a handwave at best is that in general darker beers were brewed originally with waters with higher RA. There is a correlation. But a statistician would point out that the correlation is a very weak one (Pearson's r is small). What this means in simple terms is that a stout with SRM 60 can be brewed with water with an RA of 20 or an RA of 200 or anything in between. Assuming the average is 110 you should be able to see the folly in saying that to brew a 60 SRM beer you must have an RA of 110.

 

[atreid]

Ok I just started looking into this great Spreadsheet and I entered all my tap water data, and grain bill for my next brew, an English IPA.

I added 8g of Gypsum, 1g of CaCl2, 2g of Epsom, all that to get a decent water profile, with 300ppm Sulfate and 0.16 Cl/SO4 ratio to enhance bitterness. My question is not actually about that.

Problem was my pH was 5.72... not very good... As I can buy 88% lactic acid at my LHBS (never tried that before), I added 4mL in the spreadsheet and I now have 5.46, pretty good enough...

My question is: I now have an Effective Alkalinity of -163ppm and a Residual Alkalinity of -276ppm.

What is the meaning of those negative values? I can't seem to find a clear answer anywhere...

Thank you!

EZ Sheet included right there:

 

[ajdelange]

Alkalinity is defined as the amount of acid you must add to a sample of water to bring its pH to a titration endpoint, often, but by no means always, 4.3. Thus if you have a negative alkalinity that means that the sample is originally at a pH < the end point pH. In spreadsheet calculation a negative RA means that if you added the acid to your water the pH would go below 4.3 (or whatever the end point pH is) but that you are not doing that - you are adding it to water plus grain and the acid is pulling the grain pH down at the same time it is neutralizing alkalinity in the water. All this is empirical and a bit iffy but the pH prediction numbers work out pretty well most of the time though they tend to estimate pH's that are too low.

Residual alkalinity is defined by RA = alkalinity - (calcium_hardness + magnesium hardness/2)/3.5. Thus any water sample with 0 alkalinity and some hardness will have a negative RA. Any sample in which (calcium_hardness + magnesium hardness/2)/3.5. > alkalinity will have a negative RA. The traditional Burton water has a negative RA (lots of hardness, not much carbonate). No natural water has a negative alkalinity.

In the spreadsheets the assumption is made that each unit of reduction of RA results in a corresponding reduction in pH. At 0 RA the pH of a base malt grist will be the DI water pH of the particular malt. If the RA < 0 the pH will be lower than the DI water mash pH by 0.00168 pH per ppm as CaCO3 of RA. If RA > 0 it is assumed the the pH will be higher by a like amount . Again, this is a little iffy but seems to work reasonably well for planning purposes.

If you want to know what the pH of your mash is you will have to obtain and learn how to use a decent pH meter. There is just too much variability to allow consistent accurate predictions with an empirical model.

 

[atreid]

Wow GREAT reply...! :rockin:

No money for the pH meter yet, but I used pH probes a lot in the last years, fairly simple and reliable if you just take the time to calibrate them...

You say the predictions tend to estimate pH's that are too low... Does it mean I should aim for lower, like 5.3 to get 5.4? I'm guessing it's not as simple as this or the worksheet would just do it already... ;-)

What I was wondering is if a negative residual alkalinity meant there was no more buffering power but it doesn't sound like it from your explanation... No buffering would mean water very sensible to pH change. You never want to be in that steep titration slope happening when neutralization occurs...

(Have a hard time switching to English technical writing on a subject I'm not a master of...) ;-)

 

[motobrewer]

Wow GREAT reply...! :rockin:

yeah, uh, you'll find that with AJ.

why he hangs with us mortals, the world may never know...

 

[ajdelange]

No money for the pH meter yet, but I used pH probes a lot in the last years, fairly simple and reliable if you just take the time to calibrate them...

You'll also be familiar with their quirks then and that's a good thing. I'm always advising people to get them but I know it takes a bit of practice with them to understand when they are drifting. Fortunately, with the newer technology that is less likely to happen.

You say the predictions tend to estimate pH's that are too low... Does it mean I should aim for lower, like 5.3 to get 5.4? I'm guessing it's not as simple as this or the worksheet would just do it already... ;-)

Actually the EZ spreadsheet has been adjusted and is now much more "accurate" than it was. That means it matches (obviously) the author's brewing experience but, since the adjustment, it also matches mine better than it used to. I wouldn't apply a bias because I don't really know if one is there. In order to determine that it is dozens of brewers would have to make hundreds of measurements and AFAIK that hasn't been done. When I say the spreadsheets tend to estimate low I am referring mostly to the fact that they assume that dark/roast malts supply more acid than they sometimes do. Today, for example, I brewed a stout which will doubtless measure at least 60 SRM using 10% roast barley. I did it with water with alkalinity of about 50 and the mash pH came in at 5.62. I can't run the latest version of the EZ calculator as it is not compatible with Office 2011's version of Excel but I am sure it would tell me that the pH of this mash would be appreciably lower that 5.63 and indeed it usually is. With this recipe I usually get 5.55 but this particular batch or roast barley was apparently less acidic than most and/or this batch of Maris Otter has a lower DI water mash pH than most.

What I was wondering is if a negative residual alkalinity meant there was no more buffering power but it doesn't sound like it from your explanation...

No, the buffering depends on the pH and the acid being used.

No buffering would mean water very sensible to pH change. You never want to be in that steep titration slope happening when neutralization occurs...

Thiking in terms of a titration curve alkalinity is the equivalent amount of acid or base required to go from one point on the curve to another. If you pass close to the pK (or pK's) of the acids involved the buffering capacity is high. If the starting and end points don't span one or more pK's the buffering capacity will be low. It doesn't matter that the alkalinity, as defined for our purposes, is negative.

(Have a hard time switching to English technical writing on a subject I'm not a master of...) ;-)

I can assure you it is better than my technical (and other) French.

 

[-TH-]

I can't run the latest version of the EZ calculator as it is not compatible with Office 2011's version of Excel

It won't open? or is there another issue? I thought 2011 was supposed to open older files. I don't have 2011 yet so I can't try it myself.

 

[rockytoptim]

I have Office 2010 and it opens fine with that.

 

[ajdelange]

It won't open? or is there another issue? I thought 2011 was supposed to open older files. I don't have 2011 yet so I can't try it myself.

It opens and the boxes etc appear but no numbers or formulas. And EZ isn't the only spreadsheet from previous versions that it won't handle. Did I mention that this is the Mac version?

 

[mistercameron]

I'm just seeing this for the first time. Great idea, and even better idea making an open office version.

Have you considered doing a web version?

 

[-TH-]

I'm just seeing this for the first time. Great idea, and even better idea making an open office version.

Have you considered doing a web version?

I have looked into it some, but I haven't found an easy way to create one.

 

[mistercameron]

I have looked into it some, but I haven't found an easy way to create one.

I don't know if there's an easy way to export from excel to a web page, so I guess it would have to come down to having the time and skills to do it.

 

[GilaMinumBeer]

I don't know if there's an easy way to export from excel to a web page, so I guess it would have to come down to having the time and skills to do it.

Excel 2010 can Save As a web page.

 

[mistercameron]

Excel 2010 can Save As a web page.

Will it do all the equations and calculations?

 

[GilaMinumBeer]

Will it do all the equations and calculations?

I just tried a Save As to web page and then opened it up in IE.

The answer is, no? As far as I can see it only preserves the graphical content. Maybe the formulae are still there and some minor tweaking needed to allow field edits but I don;t know anything about that.

 

[ResumeMan]

couldn't you just upload it as a Google doc?

 

[jtejedor]

I was just about to say the same thing. On second look though the drop down to select the grain type is not working in google docs.

 

[mabrungard]

I've found Google Docs to work very poorly with spreadsheets. It screws the crap out of Bru'n Water if you try it there. I imagine it does the same to EZ. If you need a freeware spreadsheet program, the LibreOffice program works very well in my opinion. I think its better than OpenOffice. I'm really surprised that the new version of Excel is not being very backward compatible.

 

[GilaMinumBeer]

I've found Google Docs to work very poorly with spreadsheets. It screws the crap out of Bru'n Water if you try it there. I imagine it does the same to EZ. If you need a freeware spreadsheet program, the LibreOffice program works very well in my opinion. I think its better than OpenOffice. I'm really surprised that the new version of Excel is not being very backward compatible.

Excel 2010 has been flawless in backwards compatibilty for me and I use a LOT of elaborate spreadsheets.

But, the originator has to save the 2010 workbook as a 97-2000 workbook for it to be seamless or a lot gets mucked up.

 

[mistercameron]

I just tried a Save As to web page and then opened it up in IE.

The answer is, no? As far as I can see it only preserves the graphical content. Maybe the formulae are still there and some minor tweaking needed to allow field edits but I don;t know anything about that.

I know it's doable in HTML/JS, I guess there's no automated way to export the working spreadsheet, which means any change there means manually updating all the affected code in JS.

I still think the Open Office version is great, as I refuse to buy any more MS products.

 

[Yooper]

Maybe this was answered earlier (and if it was I apologize) but does the "new" calculator have phosphoric acid as an addition, rather than lactic acid? I'd like to choose between them for acidification.

 

[AScott]

Maybe this was answered earlier (and if it was I apologize) but does the "new" calculator have phosphoric acid as an addition, rather than lactic acid? I'd like to choose between them for acidification.

Looks like only Lactic Acid and Acidulated Malt for acid choices in v. 3.0.1.

 

[wildwest450]

What grain type would you put carapils under in the spreadsheet?


_

 

[bsdx]

I believe its a light crystal malt made from pilsner

 

[mcnewcp]

Definitely the simplest water chem calculator I've used to date.

 

[Surly]

What grain type would you put carapils under in the spreadsheet?


_

So my question: Is there a source that indicates what type/style of grain for inputting into the spreadsheet?

If not, how accurate must we be in setting the grain specs?

 

[hafmpty]

OK. I'm going to sound like a total idiot here, but what do you all use to calculate your sparge water volume. I'm really struggling with this part of the calculator. I fly sparge so I don't ever calculate my sparge water. I just fill my keggle with 10 gallons of water and run off until I reach my volume or until the gravity drops to where I risk extracting tannins. So how should I do this?

Thanks!

 

[ajdelange]

I do it exactly the way you do. With experience I have some expectation as to how much water will actually be used but I keep the HLT full throughout the brew day and then simply throw out any that is left over at the end of the day.

I treat all water the same. I start with an HLT full of water and as I top up add 1 cc of concentrated salt solution for each inch of water I add. The strength of the concentrated solution is calculated such that each cc contains the salt that would be required for 1" of water in the HLT.

If you wish to treat the sparge water separately then just compute the salt additions you need for 10 gal and add that much to 10 gal of water. Then throw away any water you don't use. The salts are cheap.

 

[hafmpty]

If you wish to treat the sparge water separately then just compute the salt additions you need for 10 gal and add that much to 10 gal of water. Then throw away any water you don't use. The salts are cheap.

That would be the logical thing to do. But here's where I get mixed up:

Some of the salts will just end up sitting at the bottom of the HLT because they won't dissolve because of the pH. I'm fine with making up 10 gallons of sparge water. That would be a heck of a lot easier. But how do I adjust the water in the HLT so the salts will dissolve and actually end up in my boil?

 

[RoadKing]

I believe sparge additions go in the boiler, not actually in the sparge water (HLT).

 

[ajdelange]

That would be the logical thing to do. But here's where I get mixed up:

Some of the salts will just end up sitting at the bottom of the HLT because they won't dissolve because of the pH. I'm fine with making up 10 gallons of sparge water. That would be a heck of a lot easier. But how do I adjust the water in the HLT so the salts will dissolve and actually end up in my boil?

You don't. If they won't dissolve in the HLT they won't dissolve in the kettle. There are a couple of things which could cause what you are seeing. One is that you are trying to dissolve chalk which is insoluble in water. But you should never put chalk into sparge water (or any water in 99% of cases). Chalk would be the only case where pH would have anything to do with its dissolution. You could add chalk and, for example, enough hydrochloric acid to lower the pH enough to dissolve it which would result in calcium chloride in the beer (and some calcium bicarbonate) but it's easier to just add some calcium chloride and you wouldn't have the residual calcium bicarbonate.

The second is that you are trying to dissolve gypsum. Gypsum is lots more soluble that chalk but not terribly soluble (2 g/L) and is less soluble in hot water than in cold. So dissolve gypsum in cold water and then heat it. Even in cold water it can take a lot of stirring to get it to dissolve and I do mean a lot. If you dissolve it cold and it precipitates when the water is heated you are trying to dissolve too much but I can't fathom why anyone would want to put that much gypsum into sparge water. Actually, I really don't understand why people want to treat sparge water differently from the mash water other than to acidify it. If you want to add salts to the kettle (and again I don't know why you would though I think some add extra calcium to try to precipitate more phosphate and lower pH further) add them to the kettle

 

[kal]

OK. I'm going to sound like a total idiot here, but what do you all use to calculate your sparge water volume. I'm really struggling with this part of the calculator. I fly sparge so I don't ever calculate my sparge water. I just fill my keggle with 10 gallons of water and run off until I reach my volume or until the gravity drops to where I risk extracting tannins. So how should I do this?

Good point as fly spargers (like myself) do not need to calculate sparge volumes. You just go until you reach your boil kettle volume or gravity drops like you mentioned.

I only calc sparge volumes for EZ water. Here's how I do it using a recent batch as an example:

Amount of grain: 18.5 lbs
Mash thickness: 1.25 qt/lb

So, strike water = (18.5 x 1.25)/4 = 5.8 gal
I have an extra 0.5 gal in the hoses/HERMS coil so I raise the strike water to 6.3 gal

Loss to grain absorption = 18.5 x 12% = 2.2 gal

So sparge water = initial boil kettle volume I want + loss to grain absorption - strike water = 13.9 + 2.2 - 6.3 = 9.8 gal.


Kal

 

[DarkBrood]

That would be the logical thing to do. But here's where I get mixed up:

Some of the salts will just end up sitting at the bottom of the HLT because they won't dissolve because of the pH. I'm fine with making up 10 gallons of sparge water. That would be a heck of a lot easier. But how do I adjust the water in the HLT so the salts will dissolve and actually end up in my boil?

If you want your salts to properly dissolve into an aqueous solution (i.e. water), you'll need to pre-adjust a small amount of water to the conditions that each salt requires and then add that to your brewing liquor. 0.5-1 cup of water should be a large enough volume to adjust temperature and/or pH as needed. With a little fore-thought, any intended acidification from lactic acids can be used in this pre-treatment. To get the best dissolve, prepare them ahead of time (not days before or anything....just be sure to give the salts enough time and agitation for proper ion separation, etc.). A little math and you should be able to dump them into your liquor as it is heating up and end up with a properly-adjusted water with the right pH and without excessive precipitation.

 

[hafmpty]

sparge water = initial boil kettle volume I want + loss to grain absorption - strike water = 13.9 + 2.2 - 6.3 = 9.8 gal.

OK so I'm not totally out in left field. That's basically what I've been doing. Thanks.

A little math and you should be able to dump them into your liquor as it is heating up and end up with a properly-adjusted water with the right pH and without excessive precipitation.

Thanks for the idea DarkBrood. That is not something I've ever done. Have you ever done that? If so, would you be willing to share your process for a recent recipe so I can see the numbers. If not, would you do a "hypothetical"?

 

[kal]

OK so I'm not totally out in left field. That's basically what I've been doing. Thanks.

Or we may both be playing way out in left field by ourselves ....

It seems to make sense however.

Kal

 

[DarkBrood]

Thanks for the idea DarkBrood. That is not something I've ever done. Have you ever done that? If so, would you be willing to share your process for a recent recipe so I can see the numbers. If not, would you do a "hypothetical"?

I have done a cold dissolve with gypsum before - certainly does increase solubility. I've been looking into small-batch solutions for my brewing salts for a little bit now. I have a set of mason jars labelled for each salt and am currently trying to pin down the specifics for each addition and balance the adjustments against each other. It is important to remember that everything you are using for the pre-adjustments will end up in your liquor, so be sure to sum up any acids, etc. that you use. Also, if you're hoping to add them in-process (rather than pre-mash), you're going to be adding colder water (which will affect your temps) and the solutions will be added at differing pH's. Just like determining your initial amounts of the salts to add, there are a lot of interdependencies between time, temp, pH, solution density, and other factors that require careful balancing for your own process. Eventually, I'll probably put out a small dissertation on it, but if I try to get the specifics written out right now, we'd probably all end up confused....

A good place to start is looking into exactly what conditions are required for each salt to dissolve completely into solution (meaning proper ion separation, not just "you can't see it any more") and go from there.

 

[ajdelange]

A good place to start is looking into exactly what conditions are required for each salt to dissolve completely into solution (meaning proper ion separation, not just "you can't see it any more") and go from there.

That shouldn't take much effort:

Calcium Carbonate: Insoluble. Shouldn't be used
Sodium Carbonate: Infinitely soluble*. Shouldn't be used
Calcium Chloride: Infinitely soluble
Calcium Sulfate: Soluble to the extent of 2 g/L at 20 °C. More soluble in cold water
Magnesium Sulfate: Infinitely soluble
Sodium Chloride: Infinitely soluble
Potassium Chloride: Infiinitely soluble
Calcium Hydroxide: Soluble to the extent of 1.73 g/L at 20 °C. More soluble in cold water than hot. Use vary sparingly and only if required.

*Infinitely soluble in this context means that as much as you would ever want to dissolve in a brewing situation readily dissolves.

Calcium carbonate should not be used because it does not dissolve unless acid is added and it raises alkalinity
Sodium carbonate should not be used because it raises alkalinity and because it will cause stripping of calcium when a solution containing it which also contains a calcium salt is heated (precipitation of calcium carbonate).

 

[mabrungard]

AJ,

For Ca(OH)2 and CaSO4, those additions listed above both produce ionic concentrations that will be higher than a brewing situation should ever want. So for almost all those minerals listed, they are 'infinitely' soluble for brewing purposes. Chalk is the exception.

You're kind of hard on chalk although its poor reputation is deserved. But, it still can have a place in brewing with the proper understanding of its idiosyncrasies. As you and Kai Troester have pointed out, about half the predicted alkalinity from chalk is contributed to water if the chalk is not 'dissolved' into the water with an acid. When 'naturally' dissolved by carbonic acid, all the predicted alkalinity is contributed to the water. Either bubbling air or CO2 through the water produces carbonic acid for that dissolution.

Your mantra of not adding alkalinity to brewing water unless actually needed is sound. But there are situations where alkalinity is needed and chalk is a viable, yet poor, avenue for adding it.