Water: A New Brewing Water 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.
McKnuckle said:
@RPIScotty, modifying the boil time or boil-off rate changes the estimated mash pH... that's very odd. And I can see it's doing that because adjusting those boil values changes the mineral gram values.

The g/gal mineral additions should all be relative to the strike water; they shouldn't have anything to do with the boil volumes.

Am I misunderstanding something?
Click to expand...

I have corrected this. The issue was that i was misapplying the concept of removing your boil off value from volumes to account for mineral concentration in the kettle. IT is a sound theory but applying it this in dynamic calculations meant it was interacting in real time with some of the portions of the spreadsheet. I put a note in the boil off cell letting people know to removing boil off from strike and sparge water inputs for correction instead.

McKnuckle said:
It's because of the way you're calculating the raw gram weights in B31:F31 based on the grams/gallon values in B30:F30. I believe you should be using gallons = strike + sparge. But instead you are subtracting the boil-off amount from that, or gallons = strike + sparge - boil-off. This is the case for all of the mash mineral cells.

For mash acid additions, you are using only gallons = strike. So there is some inconsistency in these formulae that you might want to take a look at.
Click to expand...

I corrected all the values in B31:F31 to reflect the corresponding volumes. Acid in mash, kettle and KO are all good.
 
The minerals and pH are working well now, thanks!

Another tweak: The Shrinkage Loss and SRM are tied to the Batch Volume rather than the Post-Boil Volume. You can see how it doesn't make sense if you enter a value for Batch Volume that is out of whack with the other volumes. As a result, Fermenter and Packaged volumes don't correctly follow the mash/boil continuum of volume losses.
 
McKnuckle said:
The minerals and pH are working well now, thanks!

Another tweak: The Shrinkage Loss and SRM are tied to the Batch Volume rather than the Post-Boil Volume. You can see how it doesn't make sense if you enter a value for Batch Volume that is out of whack with the other volumes. As a result, Fermenter and Packaged volumes don't correctly follow the mash/boil continuum of volume losses.
Click to expand...


This is an artifact from trying to prevent circular reference errors in excel.

1.) shrinkage is tied to the user input for batch volume

2.) SRM is tied to the fermentor volume

3.) packaged volume = fermentor volume - fermentor losses
 
At this point, I'm content to modify these things on my local copy. :) If you change the fundamental science stuff, I'll definitely want to amend it... but I like the ability to customize the process-oriented elements so they make sense to me. SRM doesn't change even if you lose all but one drop of the post-boil volume, so that's definitely a flawed calculation.

Maybe it's in the documentation, but are you assuming that salts are added to the strike volume only, or to the strike and sparge volumes together (which are then separated)? The formulae seem inconsistent in that department, or maybe I just haven't figured it out yet.

Thanks again.
 
McKnuckle said:
At this point, I'm content to modify these things on my local copy. :) If you change the fundamental science stuff, I'll definitely want to amend it... but I like the ability to customize the process-oriented elements so they make sense to me. SRM doesn't change even if you lose all but one drop of the post-boil volume, so that's definitely a flawed calculation.

Maybe it's in the documentation, but are you assuming that salts are added to the strike volume only, or to the strike and sparge volumes together (which are then separated)? The formulae seem inconsistent in that department, or maybe I just haven't figured it out yet.

Thanks again.
Click to expand...


I'm ok with that as long as when you distribute the sheet you preface the exchange with the fact that your copy is altered. I always envisioned it as open source anyway. That's the beauty of excel.

As far as SRM, I have always used the fermentor volume for calculations with great success, but again, to each their own, and I encourage you change what you need.

The reason I calculate the additions the way I do is this:

1.) If you No-Sparge, sparge Volume will be zero and the additions calculate based on strike volume alone.

2.) This way, both camps are covered with no tricky excel logic.

Hope you enjoy!
 
RPIScotty said:
1.) If you No-Sparge, sparge Volume will be zero and the additions calculate based on strike volume alone.
Click to expand...

So right now, the spreadsheet assumes that:

1) Salt weights are calculated based on the total water (strike+sparge); and
2) Mash salts are added only to the strike volume.

This means the program always assumes that you sparge with your unadulterated source water. It won't (currently) calculate a situation where one adds salts to the total water (strike+sparge), but separates that water into strike and sparge volumes. In that instance, all of the water will have the same ppm concentrations.

Right now, if I calculate a no-sparge, the ppm values are whatever they are... but if I re-do the water and split it into 50/50 strike/sparge volumes, the ppm values show up as doubled in the mash. Of course, when one adds salts to the TOTAL volume and splits up the water, that doesn't happen. The ppm values stay constant.

Again, I'm not sure if that's in the docs, but best to point it out, so people know how to work with the program.
 
McKnuckle said:
So right now, the spreadsheet assumes that:

1) Salt weights are calculated based on the total water (strike+sparge); and
2) Mash salts are added only to the strike volume.

This means the program always assumes that you sparge with your unadulterated source water. It won't (currently) calculate a situation where one adds salts to the total water (strike+sparge), but separates that water into strike and sparge volumes. In that instance, all of the water will have the same ppm concentrations.

Right now, if I calculate a no-sparge, the ppm values are whatever they are... but if I re-do the water and split it into 50/50 strike/sparge volumes, the ppm values show up as doubled in the mash. Of course, when one adds salts to the TOTAL volume and splits up the water, that doesn't happen. The ppm values stay constant.

Again, I'm not sure if that's in the docs, but best to point it out, so people know how to work with the program.
Click to expand...

I made the fix and will be updating the manual as well. I seem to have been updating the spreadsheet at a much faster pace then the manual!

Seems to work now when splitting the strike and sparge as well as when combined.
 
RPIScotty said:
Pre-Boil? I highly doubt that. Did you mean post-Boil?
Click to expand...

Fair enough, just checked the formulas again. It's actually the post boil volume of unfermented wort and but does not account for several significant things.

Luckily the difference is small for most brews, exceptions are obvious though such as long extended boils, significant caramelization like some scottish ale recipes, high hopping rates, some biere de garde recipes use extended boils as well.

Fine grain crushes should increase color contributions some, although the difference is likely negligible. Variable boil time is not accounted for, malliard reactions will darken the wort. High hopping rates will change the color.

I'm not aware of anyone currently researching new color estimation formulas, as it's not really important.
 
FYI: The latest fix you implemented has it working properly in Linux using LibreOffice. I'm now seeing predicted mash pH values. Thanks!!!

Another issue? I believe that what I'm seeing when I add minerals to the mash is that the spreadsheet seems to be assuming that they are also being added to the sparge water. Is my assessment here correct?

I had mash set for 4.3125 gallons and sparge for 4.333 gallons, and when I added 1.043478 grams/gallon of CaCl2 dihydrate (I assume you are defaulting to the dihydrate) in order to add 4.5 grams to my mash water, the spreadsheet said I had added 9.02 grams. But at the same time, only 4.5 grams is being picked up with regard to the ppm's of Ca++ and Cl- ions in the finished beer. A minor glitch, but something isn't quite adding up here.
 
Silver_Is_Money said:
FYI: The latest fix you implemented has it working properly in Linux using LibreOffice. I'm now seeing predicted mash pH values. Thanks!!!

Another issue? I believe that what I'm seeing when I add minerals to the mash is that the spreadsheet seems to be assuming that they are also being added to the sparge water. Is my assessment here correct?

I had mash set for 4.3125 gallons and sparge for 4.333 gallons, and when I added 1.043478 grams/gallon of CaCl2 dihydrate (I assume you are defaulting to the dihydrate) in order to add 4.5 grams to my mash water, the spreadsheet said I had added 9.02 grams. But at the same time, only 4.5 grams is being picked up with regard to the ppm's of Ca++ and Cl- ions in the finished beer. A minor glitch, but something isn't quite adding up here.
Click to expand...


I'll take a look in the morning. I am admittedly a No-Sparge Brewer so I am trying to adapt the sparge settings for you guys.

I'll take a look at my literature and get this squared away in the morning. I want to get it right so people can feel comfortable brewing with it. For me, I always get great results but that is with my standard no-sparge and only using the strike volume as an input to water calcs

I'll get you guys squared away. Thanks for the patience.
 
I worked on the mineral additions a little more this morning:

http://www.********************/uncategorized/water-a-new-water-chemistry-spreadsheet/

That is v.BETA.1.4 and an Updated Users Manual
 
For mash and sparge both set to 5 gallons, it still doubles the grams added, but does not double the overall ions contribution that occurs when the mash and sparge waters are merged, For that it cuts them in half. The ions contribution is the part that is correct. The grams indicated is incorrect.

What is needed is a separate entry line for minerals to mash and to sparge waters.
 
Silver_Is_Money said:
For mash and sparge both set to 5 gallons, it still doubles the grams added, but does not double the overall ions contribution that occurs when the mash and sparge waters are merged, For that it cuts them in half. The ions contribution is the part that is correct. The grams indicated is incorrect.

What is needed is a separate entry line for minerals to mash and to sparge waters.
Click to expand...


The sheet doesn't allow for sparge mineral additions.

The spreadsheet assumes that you treat the mash volume (strike water) with total mineral additions and that sparge water merely dilutes them.

EDIT: it is essentially hard wired to behave line BW with the "add sparge minerals to mash" button on "Yes"
The second assumption is that you sparge with ion free/Low alkalinity water.
 
When I add 4.5 grams of CaCl2-2H2O the program indicates that I have added 9 grams (in the box immediately below where the 0.9 grams are entered for 5 gallons, whereby 5 * 0.9 = 4.5 grams). But it correctly puts only 4.5 grams worth of mineral ions into the system.

The error is on row 31, where the mash water and sparge water are summed and then this sum is multiplied times 0.9. Remove the sparge water and multiply the grams/gallon addition with the mash water only and the problem goes away. Can I fiddle with my copy and make this happen?
 
Silver_Is_Money said:
When I add 4.5 grams of CaCl2-2H2O the program indicates that I have added 9 grams (in the box immediately below where the 0.9 grams are entered for 5 gallons, whereby 5 * 0.9 = 4.5 grams). But it correctly puts only 4.5 grams worth of mineral ions into the system.

The error is on row 31, where the mash water and sparge water are summed and then this sum is multiplied times 0.9. Remove the sparge water and multiply the grams/gallon addition with the mash water only and the problem goes away. Can I fiddle with my copy and make this happen?
Click to expand...


You can alter it however you like. What I have written is consistent with my copy of Brun Water, i.e. It behaves the same way
 
Silver_Is_Money said:
When I add 4.5 grams of CaCl2-2H2O the program indicates that I have added 9 grams (in the box immediately below where the 0.9 grams are entered for 5 gallons, whereby 5 * 0.9 = 4.5 grams). But it correctly puts only 4.5 grams worth of mineral ions into the system.

The error is on row 31, where the mash water and sparge water are summed and then this sum is multiplied times 0.9. Remove the sparge water and multiply the grams/gallon addition with the mash water only and the problem goes away. Can I fiddle with my copy and make this happen?
Click to expand...


Fixed it! I had a brainfart and implemented your recommended changes. Download a new copy!
 
Try a case study for me if you would. I brewed an oatmeal stout and estimated pH using all of the common tools. This new spreadsheet is the only outlier [Edited], and maybe you can poke around and tell me why.

4.2 lbs grain, 4 gal full volume mash with distilled water

70% Golden Promise 3L
9% Flaked Barley 2.2L
7% Flaked Oats 2.2L
7% Roasted Barley 550L
3% Carafa II 425L
4% Crystal 80 80L

Salts added to full volume mash:
1.2g gypsum (0.3g/gal)
1.4g CaCl (0.35g/gal)
0.9g baking soda (0.225g/gal)

Mash water profile (rounded) Ca 44, Mg 0, Na 16, Cl 45, SO4 44

Estimated mash ph:
Bru'n Water 5.49
Brewer's Friend 5.51
MpH 5.48
Water 5.8 <-- actually 5.60

Thoughts? I don't think you need the boil-off and other stuff, but let me know if you do. About 2.27 gal ends up in the fermenter.

[Edit: downloaded file has a recipe example, and I had not erased the DI Ph values.]
 
McKnuckle said:
Try a case study for me if you would. I brewed an oatmeal stout and estimated pH using all of the common tools. This new spreadsheet is the only outlier, and maybe you can poke around and tell me why.

4.2 lbs grain, 4 gal full volume mash with distilled water

70% Golden Promise 3L
9% Flaked Barley 2.2L
7% Flaked Oats 2.2L
7% Roasted Barley 550L
3% Carafa II 425L
4% Crystal 60 60L

Salts added to full volume mash:
1.2g gypsum (0.3g/gal)
1.4g CaCl (0.35g/gal)
0.9g baking soda (0.225g/gal)

Mash water profile (rounded) Ca 44, Mg 0, Na 16, Cl 45, SO4 44

Estimated mash ph:
Bru'n Water 5.49
Brewer's Friend 5.51
MpH 5.48
Water 5.8

Thoughts? I don't think you need the boil-off and other stuff, but let me know if you do. About 2.27 gal ends up in the fermenter.
Click to expand...


I calculated 5.61:

View attachment ImageUploadedByHome Brew1485351803.036430.jpg

Did you use a DI pH input for the golden promise? Did you change any other parameters?

Brun Water also shows 5.61
 
Cr@p, it's Crystal 80 - please adjust that. No, I did not put in any other pH inputs. I checked each spreadsheet carefully so now I have to figure out what is different.

[Correction: The DI Ph values populated in the downloaded file had not been erased. I now get 5.60 in Water. I still get a lower estimate in the other tools.]
 
The crystal 80 only reduced it by 0.01.

I did match your reading to Brewers Friend.

I'm not sure what the &#916; is.
 
I still get 5.50 in Bru'n Water, but whatever... :) I'll probably still estimate using all of these tools like a madman. Thanks for checking and sorry about the oversight. Prior versions didn't have a pre-populated recipe so I neglected to erase all of the inputs.
 
Let me know how your actuals measure up to your estimations.
 
This may be appalling to you, but I don't own a pH meter. Reading about others' experiences with them, it seems like an enormous pain and just one more gadget to obsess over.

So I've used the Brewer's Friend pH estimates for dozens of batches now, and have practiced the art of empiricism whereby I've observed what stout tastes like at 5.5 or 5.3 or whatever, and have adjusted my targets accordingly.

So the estimates may be great, good, or indifferent, but the tool has been consistent and it has served me well in being able to reliably produce beer of a desired quality.

I use 5.4 or just below for very pale beers, 5.5 or just above for stouts, and somewhere in between for everything else, roughly based on color and desired smoothness/brightness.
 
McKnuckle said:
This may be appalling to you, but I don't own a pH meter. Reading about others' experiences with them, it seems like an enormous pain and just one more gadget to obsess over.

So I've used the Brewer's Friend pH estimates for dozens of batches now, and have practiced the art of empiricism whereby I've observed what stout tastes like at 5.5 or 5.3 or whatever, and have adjusted my targets accordingly.

So the estimates may be great, good, or indifferent, but the tool has been consistent and it has served me well in being able to reliably produce beer of a desired quality.

I use 5.4 or just below for very pale beers, 5.5 or just above for stouts, and somewhere in between for everything else, roughly based on color and desired smoothness/brightness.
Click to expand...


For some reason I didn't catch this post the other day.

Believe it or not, I use the narrow range Colorphast strips. My collaborator owns the Extech pH110 which I will be purchasing once my current batch of strips run out.

In developing the sheet, he beta tested all the revisions and additions we made along the way: sauergut, the original "malt override" color cells, the new DI pH additions, etc.

We have experienced very consistent results with the sheet. We are mostly using just the bare minimum of minerals and sauergut or acid malt. 5.2-5.3 for everything.
 
I renamed 'Mash Sauergut Acid %' as 'Acidulated Malts Acidity Factor', and placed a value of 240 in this field, and then I changed ' Sauergut Amount (ml)' to read 'Acidulated Malt (Oz.)', then lastly I changed the "Target pH" on the 'pH Reduction Data' sheet to a value of 5.35, and now I can input in the new 'Acidulated Malt (Oz.)' field my batches required ounces of acidulated malt instead of ml of sauergut.
 
Last edited:
Silver_Is_Money said:
I renamed 'Mash Sauergut Acid %' as 'Acidulated Malts Acidity Factor', and placed a value of 240 in this field, and then I changed ' Sauergut Amount (ml)' to read 'Acidulated Malt (grams)', then lastly I changed the "Target pH" on the 'pH Reduction Data' sheet to a value of 5.35, and now I can input in the new 'Acidulated Malt (grams)' field my batches required grams of acidulated malt instead of ml of sauergut.
Click to expand...


I can't support this change in the slightest. Why wouldn't you just use the acid malt in the malt dropdown? Do you understand how the Sauergut calcs are structured? I can't, with confidence, say that what you changed will work at all.
 

Similar threads

FlyingWombat
Beginners Guide to Water Chemistry and Brewing Salts
Replies
3
Views
467
BrewnWKopperKat
BrewnWKopperKat
C
Artesian well suitability
Replies
3
Views
605
mabrungard
mabrungard
O
Water Chemistry for a Mexican lager
Replies
5
Views
1K
BigEd
B
S
Water profile advice
Replies
12
Views
931
stiray
S
B
Newbie already made a mistake?
3 4 5
Replies
187
Views
5K
Bagarge
B

Latest posts

Back
Top