I have been looking at what is available out there and decided to do a little work in Excel and create my own, borrowing some ideas from Palmer's tool and the Water Witch. What I wanted to do is have a place to enter your water chemistry and the chemistry of the water you would like to copy. Based on those two sets of entries, it would then calculate what mash water additions are needed to get as close as possible to the target mash water. In addition, it also figures out how much you need to dilute your water with distilled/RO water if you are too high in some minerals. You can lock additions at a certain level if you wish (zero if you prefer not to use a certain type of additive or a nice round number if you so choose). Another output is a graph that shows the best SRM of beer for your water, the target water, and the water as adjusted.
It runs using Solver to minimize an error function (target minus adjusted water, by ppm of each critical ion) and an Excel macro, so if you download this tool, you will need to enable the macros when asked (no malicious code in there - I can provide the code to anyone who wants to see it and verify). If some of you that use Excel and also have experience in modifying your mash water chemistry can check this out and give me some feedback on how to improve it, I would really appreciate it. If you would like to know more details on how it works behind the scenes or why I chose to do it this way, just let me know.
That looks absolutely great! I'll play with it a little bit and give you some feedback.
Thanks!
__________________ On Tap: Lake Walk Pale Ale -- Eternity (Raspberry Stout) -- Nutrocker -- Donnybrook Dark Primary: Lake Walk Pale Ale Secondary: Summit IPA Up Next: Smoked Porter -- Pub Ale -- Watermelon Wheat Planning: Gone But Not Forgotten:
It will not open in Google Docs, and when I attempt to open it in Excel I get "run-time error 9/subscript out of range", and Excel wants to go into debug mode.
__________________
Cheers,
John
"There Are No Stupid Questions, But There Are a LOT of Inquisitive Idiots!"
Tried to run this today. After enabling the macros, here's what I got:
Quote:
Run-time error '1004'
Unable to set the Installed property of the AddIn class
debug mode takes me to a window with this:
Quote:
Private Sub Workbook_Open()
AddIns("Solver Add-In").Installed = True
On Error Resume Next
Application.VBE.ActiveVBProject.References.AddFrom File (Application.LibraryPath & "solver.xla")
End Sub
Any idea how to fix? I don't use excel often, so I'm kind of lost here.
Sure would be a great tool to have.
__________________ Fermenting:
rye farmhouse ale
Oktoberfest
flanders red Kegged:
Oktoberfest
It will not open in Google Docs, and when I attempt to open it in Excel I get "run-time error 9/subscript out of range", and Excel wants to go into debug mode.
It did the same thing for me. But then I realized that I didn't have the Solver Add-in installed. You will have to go back to your Microsoft Office installation and enable the add-in.
It did the same thing for me. But then I realized that I didn't have the Solver Add-in installed. You will have to go back to your Microsoft Office installation and enable the add-in.
That's likely what's wrong with mine as well.
I guess it pays to read the OP carefully:
Quote:
It runs using Solver to minimize an error function (target minus adjusted water, by ppm of each critical ion) and an Excel macro, so if you download this tool, you will need to enable the macros when asked
<goes rummaging into the closet to find the Office CD>
__________________ Fermenting:
rye farmhouse ale
Oktoberfest
flanders red Kegged:
Oktoberfest
I wanted to take a look at this utility, not out of necessity, but curiosity. I already found one of the most useful tools for adjusting water chemistry that is available in a freeware program. It is called Brewater. Check out the link and scroll down a little and download Brewater 3.0. It is quite an amazing little Windows Utility. It has drag sliders that let you make adjustments to ion concentrations without having to type in the numbers. I have used it with much success so far.
I might as well link to the amazing, and simple Mash Water Calculator that I use for batch sparging. It makes it simple to find your strike water volumes, etc. And it's also free! Scroll down to near the bottom and download Mashwater 3.3.
__________________
Cheers,
John
"There Are No Stupid Questions, But There Are a LOT of Inquisitive Idiots!"
Those of you with the runtime errors, let me know what versions of Excel and Windows you are running. The Excel file should have a macro that runs at opening and loads the Solver AddIn for use in macros and Excel. It is possible that you need the MS Office disk for this. For those of you that manually want to correct this until I can find an automated fix, here are the steps:
-Open the file and get the error, then select debug. This should open bring up a MS Visual Basic window.
-Click the menu Tools > References... and select SOLVER from the list. If it is not on the list, it may need to be found using the Browse... button (the file type drop down box should be changed to .xla). If it is not on your computer, it is probably because you didn't have it selected as part of the install package when you loaded MS Office. You can load it off of your disk, but I will also add a link to the add-in here (not sure if this will work with all Excel vesions, but here it is any way - save it and then follow this step from the beginning, browsing to the place you just saved it).
-Should work now, so exit out of Excel and try again. If it still does not work, in Excel (don't click debug - this fix is not in the Visual Basic window), select the menu Tools > Add-Ins... and select Solver. If it is not on you list, you will have to browse for it as in the step above.
-Should work now. If not, I have no clue and will have to take it to my Excel forum for help. Don't forget to let me know Excel version and Windows version.
Thanks for the help testing. I have these addins in my computer and I tried to have the program load it for those that don't, but since I already have them I couldn't verify it would actually load for you those that didn't have it. Really appreciate the help.
Next on deck is a tool that the user selects a beer style and it will give you the correct temperature range for serving, the CO2 pressure you should use for those temperatures, and options for beer line length at different diameters that will provide a proper pour.
I wanted to take a look at this utility, not out of necessity, but curiosity. I already found one of the most useful tools for adjusting water chemistry that is available in a freeware program. It is called Brewater. Check out the link and scroll down a little and download Brewater 3.0. It is quite an amazing little Windows Utility. It has drag sliders that let you make adjustments to ion concentrations without having to type in the numbers. I have used it with much success so far.
This tool is one that I had seen in the past, but not the new 3.0 version. I like what it does, but wanted to automate the process to minimize the error instead of moving sliders. Now v3.0 of this tool seems to do it. One of the difficult things with water adjustments is that when you put in an additive, it usually affects multiple water parameters at the same time. Getting closer in one parameter sometimes means getting further away in another, and this sounded like an optimization problem that Excel Solver is really good at. The only difference in this tool to mine is that mine optimizes by weighting the hardness and alkalinity ions heavier in the error function as they affect pH. Mine also calculates distilled water to account for situations where you need to reduce ions. It is a pretty hairy math problem when you are too high in some and too low in others.
I do like the option this tool has with gram to teaspoon conversion. I decided to go with teaspoons on additions with no option since many people do not have the capability to weigh things in fractions of a gram. The other thing I like in this tool are the preset target water profiles you can load. I was already planning this for v2.0 of mine by way of a drop down box of cities around the world that you select from and it auto-populates the target water boxes.
The only thing I don't like on this tool is that it lets you enter Ca, Mg, and hardness values. By knowing 2 you should always be able to get the third. By letting you enter all 3 values, you have the opportunity to enter numbers that are mathematically inconsistent with each other. The tool does not error out if you do, so I have no idea how valid the results are in this situation. Mine gives you an error if you try to enter all three values. Same thing goes with bicarbonate and alkalinity. Otherwise, this is a really nice tool. Maybe I can speak with the author and he can incorporate some of my suggestions for his v4.0.
Mash Water Chemistry Calculator - Feedback Wanted
Linear Mode
