Water: A New Brewing Water Spreadsheet

[Big Monk]

RPIscotty, Thankyou for taking the time to put this together!

Well get there. I need to do some more work to get it 100% for everyone.

 

[Big Monk]

I worked on the mineral additions a little more this morning:

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

That is v.BETA.1.4 and an Updated Users Manual

 

[Silver_Is_Money]

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.

 

[Big Monk]

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.

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.

 

[Silver_Is_Money]

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?

 

[Big Monk]

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?

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

 

[Big Monk]

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?

Fixed it! I had a brainfart and implemented your recommended changes. Download a new copy!

 

[Silver_Is_Money]

It's working now!

 

[McKnuckle]

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.]

 

[Big Monk]

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.

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

 

[McKnuckle]

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.]

 

[Big Monk]

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.

 

[McKnuckle]

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.

 

[Big Monk]

Let me know how your actuals measure up to your estimations.

 

[McKnuckle]

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.

 

[Big Monk]

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.

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.

 

[enkamania]

Where do you find the DI ph? I did really quick search and couldn't find anything.

 

[Silver_Is_Money]

Where do you find the DI ph? I did really quick search and couldn't find anything.

I emailed Briess and asked them for a list of their DI pH's for their malts, and they sent me one.

 

[Silver_Is_Money]

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.

 

[Big Monk]

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.

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.

 

[Silver_Is_Money]

I can't support this change in the slightest. Why wouldn't you just use the acid malt in the malt dropdown?

Purely personal preference.

PS: The 240 value I initially went with can be adjusted to match your particular acidulated malts actual acid strength (concentration). This value may at first quick assessment range between approximately 200 and 300 as my first guesstimate. I haven't fiddled with it enough to verify it, but 200 may correlate to 2% lactic acid by weight in the acidulated malt, and 300 may correlate to 3% lactic acid in the acidulated malt. This gives another dimension to the end user which is not available by simply selecting German Saurmaltz (acidulated malt) from the dropdown.

PPS: I only made this change (and others, all for my personal use) after reading your comments to another forum member regarding your confirmation that you consider this spreadsheet being open source. If this has changed, please let me know.

 

[Big Monk]

Purely personal preference.

PS: The 240 value I initially went with can be adjusted to match your particular acidulated malts actual acid strength (concentration). This value may at first quick assessment range between approximately 200 and 300 as my first guesstimate. I haven't fiddled with it enough to verify it, but 200 may correlate to 2% lactic acid by weight in the acidulated malt, and 300 may correlate to 3% lactic acid in the acidulated malt. This gives another dimension to the end user which is not available by simply selecting German Saurmaltz (acidulated malt) from the dropdown.

PPS: I only made this change (and others, all for my personal use) after reading your comments to another forum member regarding your confirmation that you consider this spreadsheet being open source. If this has changed, please let me know.

Just be careful. I can add an acid malt strength input if you'd like.

The equation for sauergut is based on 60 ml/kg of malt. Your value of 240 is going to throw the equation out of whack, and inputting grams instead of ml there isn't going to jive either. You can alter the sheet to your hearts desire as long as you know how the calcs work!

Let me know about the strength setting and I'll implement Monday.

 

[Silver_Is_Money]

Oops, I was clearly experiencing brain fade earlier, and I intended ounces wherever I previously stated grams for acidulated malt. I have edited my earlier post accordingly.

My guide in initially choosing 240 (this still needs tweaking) has been from admittedly quite limited experience which indicates that for batches of the size that I typically brew, 1.25 ounces of acidulated malt added to the grist generally gives me about the same downward pH shift as does the addition of 1 ml of 88% lactic acid.

I'm knee deep in personalized modifications at this juncture, so I will leave it to others on the forum to decide if you should implement a strength setting box for acidulated malt in future releases. Since I never intend to use saurgut, my personal modification should suffice for me.

 

[divrack]

ive seen a number of studies that found little or no noticeable impact of even HSA, im not sure we need worry too much about DO in water?

besides everything i make tends to be drunk before it would be a problem. maybe ill think about employing some of these methods for barley wines and the like.

 

[Big Monk]

Purely personal preference.

PS: The 240 value I initially went with can be adjusted to match your particular acidulated malts actual acid strength (concentration). This value may at first quick assessment range between approximately 200 and 300 as my first guesstimate. I haven't fiddled with it enough to verify it, but 200 may correlate to 2% lactic acid by weight in the acidulated malt, and 300 may correlate to 3% lactic acid in the acidulated malt. This gives another dimension to the end user which is not available by simply selecting German Saurmaltz (acidulated malt) from the dropdown.

PPS: I only made this change (and others, all for my personal use) after reading your comments to another forum member regarding your confirmation that you consider this spreadsheet being open source. If this has changed, please let me know.

No that's fine. I just to make sure that you know the equations for that section are based on 60 ml of Sauergut per 1 kg of malt to give the correct pH drop.

I don't believe what you have implemented will work.

 

[Silver_Is_Money]

As long as it shows that for every 1.25 ounces of acid malt that I add to the grist I'm getting a predicted pH shift that is a close match to the alternative of adding 1 ml of lactic acid to the mash (or to the strike water), I'm happy with my modification. In simulations so far it seems to be behaving just as I want it to. I.E., it seems to be working.

 

[Big Monk]

As long as it shows that for every 1.25 ounces of acid malt that I add to the grist I'm getting a predicted pH shift that is a close match to the alternative of adding 1 ml of lactic acid to the mash (or to the strike water), I'm happy with my modification. In simulations so far it seems to be behaving just as I want it to. I.E., it seems to be working.

Awesome! That's what it's all about!

 

[Silver_Is_Money]

It is a very nice spreadsheet, but its underlying complexity often plays fits with LibreOffice in Linux, and I constantly find myself blowing it up just by entering batch data. The multitude of Index/Matches do not always find a match, and as soon as one does not, it blows up and it's back-up copies to the rescue. No idea if it is so frail under Excel.

 

[Big Monk]

It is a very nice spreadsheet, but its underlying complexity often plays fits with LibreOffice in Linux, and I constantly find myself blowing it up just by entering batch data. The multitude of Index/Matches do not always find a match, and as soon as one does not, it blows up and it's back-up copies to the rescue. No idea if it is so frail under Excel.

It's for sure an open office thing. It is very robust in excel.

 

[Silver_Is_Money]

I sure wish I had Excel!