Trouble with water spreadsheets

[IcicleToes]

Whatup, HBT soldiers! I'm having some difficulty using the available water spreadsheets, and I'm at the point where I need some help. I've read Brungard's Water Knowledge, as well as most of Palmer's Water book, so I have an idea of mash ph, buffering, alkalinity, and the various salts and acids available for adjusting water. So far I have tried BrunWater and EZWater. My goal here is to build a profile from RO water sourced at one of those grocery store vending machines. Anyways, I have entered a sample grain bill into the spreadsheets and plugged in 0s for the starting values of my water, since it will be RO. The problem is that the spreadsheets never actually calculate any quantities for salts to make adjustments. It just says 0 for gypsum, epsom salts, etc. Is there a magic "calculate" button I am missing here? I'm doing BIAB no-sparge, or sometimes a 1 gallon sparge for a 6 gallon batch. I don't have any ph strips or a ph meter, but I am hoping the starting Ph of the RO water is somewhat predictable. Certainly I will be investing in a Ph meter at some point, but right now I just want to brew and hopefully be in the ballpark of acceptable mash ph levels.

I would be stoked if someone could share the adjustments they make to their 100% RO brewing water for any given style. There's a lot of info out there, but nobody seems to be putting out "water recipes".

Thanks, Homebrewtalkers!

 

[Homercidal]

Well, it sounds like you want the spreadsheets to magically calculate the perfect water for your beer style. It doesn't work that way.

You have to enter an amount, in grams, per gallon of water, of each salt. The spreadsheet will then give feedback on the ppm of that mineral, and how it affects pH.

I know it doesn't sound as handy as picking a style an it magically appearing, but it gives flexibility, which you wouldn't get with an automatic spreadsheet.

 

[IcicleToes]

I see. Thanks for clarifying!

 

[Derp]

Sometimes I use Beersmith's water calculator to come up with the proper salt additions to match my intended profile and then plug those numbers into BrunWater and tweak things a bit.

 

[macaronijones]

Have you tried the basic recommendations in the "Water Primer" sticky in the Brew Science forum? Gives very simple instructions for building up from RO water to get in the ballpark of a couple broad beer categories.

 

[IcicleToes]

Thank you for the recommendations! I appreciate the help. Can't wait to experiment.

 

[ajdelange]

Well, it sounds like you want the spreadsheets to magically calculate the perfect water for your beer style. It doesn't work that way.

Yes, actually it does in any Excel spreadsheet (except ones like Bru'n Water that lock it out) as this very powerful feature ships as part of Excel. I think EZ water is Excel based and not locked so you ought to be able to use it with that program.

Give me an example profile and I'll give you the salt additions you need to hit it.

 

[Homercidal]

Yes, actually it does in any Excel spreadsheet (except ones like Bru'n Water that lock it out) as this very powerful feature ships as part of Excel. I think EZ water is Excel based and not locked so you ought to be able to use it with that program.

Give me an example profile and I'll give you the salt additions you need to hit it.

My Bru'nwater is unlocked, so it too *can* have the ability to do that, but out of the box it doesn't. I actually like tweaking the numbers to meet my needs for that particular brew. I think it helped me understand the process somewhat. (Not the chemistry, that's way beyond me.)

 

[ajdelange]

Solver in no way relieves you of the need to understand the process. It just saves you a lot of labor in determining, for example, whether you are going in the right direction with your adjustments and if you are takes you automatically to the end of the path. If you aren't it takes you to best place it can get you (if you have constructed your error metric properly) and you realize, if that isn't a very good place, that you had better try another strategy. If you know linear programming at all you know I am talking about the nearest local (but possibly suboptimal) solution and the global (best) solution.

 

[jddevinn]

I have modified my Bru'n Water with a simple VB solver that will run through and "guess" salt combinations to get me as close as possible to the target.

 

[IcicleToes]

Nice, AJ! Thank you. How about this water profile?

Calcium 22
Magnesium 1.8
Sodium 18
Chloride 42
Sulfate 12
Bicarbonate ?
Alkalinity 101
Total Hardness 211

 

[Sandstorm]

Off topic....

Jddevin do the cells that turn red really matter? I know that they usually turn red when their are excess amounts of ions. I am finding in the new bruNwater the cells turn red way more often.

 

[ajdelange]

How about this water profile?

Calcium 22
Magnesium 1.8
Sodium 18
Chloride 42
Sulfate 12
Bicarbonate ?
Alkalinity 101
Total Hardness 211

For starters, you cannot ask me to synthesize a profile without telling me what pH you want. I am assuming 7 here. Second, total hardness is determined by the sum of the calcium and magnesium additions. For the numbers you have specified the total hardness is 62.3. So here's the synthesis from RO water:

Salt/Acid/Base mg/gal Synth
CaCl2.2H2O 100.42
NaCl 0.00
MgCl2.6H2O 39.46
CaSO4.2H20 9.28
MgSO4.7H20 21.26
H2O (DI) Liters 0.00
CaCO3 80.06
NaHCO3 0.00
CO2 374.24
HCl 99.53
Ca(OH)2 40.02
Na2CO3.H2O 0.00
Sodium Lactate 0.00
Potassium Lactate 0.00
Lactic 0.00
Sulfuric 32.62

This is probably not what you were hoping for as it gets sulfate and chloride from sulfuric and hydrochloric acids and bicarbonate from limestone, lime and carbon dioxide but it is an exact synthesis. The problem stems from your demand for alkalinity. Were you to brew with this water the first thing you would want to do is get rid of that alkalinity so let us rather than simulate a water you would have to treat to get rid of alkalinity simulate a water that you would not have to treat. We do this by setting the alkalinity to 0 at mash pH which I will assume is 5.4 and trying to match the other ion concentrations as best we can.

As it turns out we can do very well. The following synthesis matches everything you asked for exactly but we must tolerate a wee amount of lactic acid in order to get to mash pH and a little sodium (4.33 mg/L) so that we can use sodium chloride as a source of chloride which is necessary to hit chloride, sulfate, calcium and magnesium exactly.

Salt/Acid/Base mg/gal Synth
CaCl2.2H2O 235.99
NaCl 41.70
MgCl2.6H2O 56.95
CaSO4.2H20 81.37
MgSO4.7H20 0.05
H2O (DI) Liters 0.00
CaCO3 0.00
NaHCO3 0.00
CO2 0.00
HCl 0.00
Ca(OH)2 0.00
Na2CO3.H2O 0.00
Sodium Lactate 0.00
Potassium Lactate 0.00
Lactic 1.50
Sulfuric 0.00
88% Lactic ml/Gal 0.0014

I went through all this to illustrate that there is more to matching profiles than meets the eye. It is not generally a profitable exercise especially if you try to match the profiles in the spreadsheets/calculators as they more or less ignore the considerations discussed here. I think you will do better if you consult the BJCP guidelines and/or the AHA monographs on the individual styles to get a general idea as to what sort of water they were brewed with but never try to match alkalinity. Alkalinity at mash pH should be 0!

 

[IcicleToes]

That is superb. Thanks, Mr. Delange! That water is from the Denniston Water Project, by the way, in California. Cool to know I can recreate that profile from RO. Much appreciated.

 

[jddevinn]

Off topic....

Jddevin do the cells that turn red really matter? I know that they usually turn red when their are excess amounts of ions. I am finding in the new bruNwater the cells turn red way more often.

The note in the cells states
The ADDITION cells will turn RED if too much of a mineral is added and the Actual Water Adjustment of an ion that the mineral contains differs from the Target Water Adjustment by more than 10 ppm. MORE THAN ONE MINERAL CELL MAY TURN RED WHEN AN ION BECOMES EXCESSIVE.

Reduce the mineral addition amount for any mineral that shows a RED cell. It is OK to ignore the warning if the brewer thinks the level will not be excessive.

Looking at the math for the Conditional Formatting the cells turn red IF the Overall Finished Water Profile (line 14) - the Target Finished Water Profile (line 11) is greater than 10. Definitely not my strong suite but I've been ignoring as I THINK it may be referencing the wrong lines and is intended to subtract line 12 Actual Water Adjustment from 11 Target Water Adjustment???? I've been ignoring red cells. Probably a question for Brungard if he is around.

 

[mabrungard]

The note in the cells states
The ADDITION cells will turn RED if too much of a mineral is added and the Actual Water Adjustment of an ion that the mineral contains differs from the Target Water Adjustment by more than 10 ppm. MORE THAN ONE MINERAL CELL MAY TURN RED WHEN AN ION BECOMES EXCESSIVE.

Reduce the mineral addition amount for any mineral that shows a RED cell. It is OK to ignore the warning if the brewer thinks the level will not be excessive.

Looking at the math for the Conditional Formatting the cells turn red IF the Overall Finished Water Profile (line 14) - the Target Finished Water Profile (line 11) is greater than 10. Definitely not my strong suite and I haven't dove into this much but I've been ignoring as I THINK it may be referencing the wrong lines and is intended to subtract line 12 Actual Water Adjustment from 11 Target Water Adjustment???? I've been ignoring red cells. Probably a question for Brungard if he is around.

Yep! The wrong cells were compared. That has been corrected and is in the next update.

 

[jddevinn]

Yep! The wrong cells were compared. That has been corrected and is in the next update.

Thanks! Good to know that this was not one of the times that I'm completely off base on my understanding!!!

Have you thought about including a button linked to a macro to find the combination of salt additions to get the result closest to the target?

 

[Sandstorm]

When can we expect the next version?

 

[Homercidal]

When can we expect the next version?

August 8th I believe.

Strike that. That date is for Beersmith. I had the release date in my head and accidentally posted it here instead of Martin's update, which I have no idea when it's coming out.

 

[Sandstorm]

Nice beer-smith update!!! Martin any idea on when the next version of bru'n water is going to be available?