Water: A New Brewing Water Spreadsheet

[ajdelange]

Yes. What you get and what others get is going to be different. Emphasize that you have driven down a major error source and let the users see how much improvement that makes for them.

 

[Big Monk]

Yes. What you get and what others get is going to be different. Emphasize that you have driven down a major error source and let the users see how much improvement that makes for them.

Will do. Thanks for all the great information AJ.

 

[RocketBrewer]

Subbed, looking forward to trying this out!

 

[McKnuckle]

@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?

 

[dryboroughbrewing]

Very cool any chance you could add british oat malt?

 

[Big Monk]

@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?

I'll take a look. That is odd.

 

[McKnuckle]

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.

 

[Big Monk]

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.

Thank you for the heads up. I'll take a look Monday morning and clear it up.

One thing to understand is that I revamped our typical calculations for this sheet and expanded them greatly to give people the required set of inputs.

If you guys stick with me on this, I'll work out the kinks for sure.

 

[Silver_Is_Money]

Still not working in Linux using LibreOffice 5.2.4 for me. If others are using this combination please let me know if it is working for you. Perhaps I'm just doing something very incorrectly.

 

[Big Monk]

Still not working in Linux using LibreOffice 5.2.4 for me. If others are using this combination please let me know if it is working for you. Perhaps I'm just doing something very incorrectly.

What issues are still occurring?

 

[Silver_Is_Money]

Errors on the kettle water line and the mash pH line.

 

[Big Monk]

Errors on the kettle water line and the mash pH line.

Send me a screen shot if you could. Are the water dropdowns working for you?

 

[Silver_Is_Money]

The water dropdowns are working. Will send screenshot in a PM.

 

[Big Monk]

The water dropdowns are working. Will send screenshot in a PM.

I'll take a look tomorrow. I think I know the issue.

 

[Big Monk]

@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?

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.

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.

I corrected all the values in B31:F31 to reflect the corresponding volumes. Acid in mash, kettle and KO are all good.

 

[Big Monk]

The water dropdowns are working. Will send screenshot in a PM.

You should be all set. I believe I removed the culprit.

 

[McKnuckle]

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.

 

[Big Monk]

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.

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

 

[McKnuckle]

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.

 

[Big Monk]

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.

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!

 

[McKnuckle]

I would never distribute, but thank you for the reminder about issuing a caveat if I did.

 

[Big Monk]

Edit: sorry. Duplicate post.

 

[pricelessbrewing]

SRM calculations are based on preboil volume.

 

[Big Monk]

SRM calculations are based on preboil volume.

Pre-Boil? I highly doubt that. Did you mean post-Boil?

 

[McKnuckle]

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

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.

 

[Big Monk]

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.

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.

 

[pricelessbrewing]

Pre-Boil? I highly doubt that. Did you mean post-Boil?

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.

 

[Silver_Is_Money]

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.

 

[Big Monk]

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.

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.

 

[aeviaanah]

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