Mash Water Chemistry Calculator - Feedback Wanted

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.

Download Mash Water Chemistry Calculator

That looks absolutely great! I'll play with it a little bit and give you some feedback.

Thanks!

Man that is sweet. Not that I understand any of it but it's still cool. Will come in handy once I know what I'm doing.

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.

Tried to run this today. After enabling the macros, here's what I got:

Run-time error '1004'

Unable to set the Installed property of the AddIn class

Click to expand...

debug mode takes me to a window with this:

Private Sub Workbook_Open()
AddIns("Solver Add-In").Installed = True
On Error Resume Next
Application.VBE.ActiveVBProject.References.AddFromFile (Application.LibraryPath & "solver.xla")
End Sub

Click to expand...

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.

johnsma22 said:

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.

Click to expand...

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.

FlyGuy said:

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.

Click to expand...

That's likely what's wrong with mine as well.
I guess it pays to read the OP carefully:

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

Click to expand...

<goes rummaging into the closet to find the Office CD>

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.

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.

johnsma22 said:

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.

Click to expand...

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.

Any thoughts of porting your calculator to Java? The math is all the same...you just need to create an interface for it. Netbeans is a nice Java editor.

I know squat about Java. I'm only a VB for Excel guy.

Update:

v1.1 has been released. Took a suggestion to add the the chemical names to the list and also found a missing set of parenthesis in a formula that was causing a miscalculation of effective hardness and residual alkalinity when using a value in the total hardness field. Here is link (the link at the top has been updated as well):

Mash Water Chemistry Calculator v1.1

I love this spreadsheet and have used it for the last couple brews. Is there any way we could get the additions in grams as well as teaspoons? (Or have a button to switch between..). I suppose .62 teaspoons is close enough to .75 but getting it precise would be nice.

Edit: Here's the copy I have, though Bearcat may have a newer version.

View attachment Water Calculations.zip

Can it be reposted i get an error of page not found.

Conpewter's link above still works. I just got it from his link. Just unzip, enable macros, and you're set! Nice tool. Glad the thread was bumped after 1.5 years!

The link is dead, anyone have this excel program? I would like to download it.

Mine is actually loaded onto this site so it should never go dead.

I realized that after I posted that you link worked. Thank you for following up with me. I checked it out. It looks very cool. I have yet to adjust my water in my brewing sessions but its a topic that my brew club is covering in the next few months. I want to get a head start so I know what they are talking about =)

I'm still learning a lot about it as well. I really want to find someone very knowledgeable that can help me work out out, or has a list of ideal water profiles. I do have one list but the carbonates/bicarbs seem way off of what I think they should be.

this should defiantly be re-written in java.

conpewter said:

Mine is actually loaded onto this site so it should never go dead.

Click to expand...

Thanks - appreciate it. Roadrunner messed with their web page set up and I can't access my pages anymore to update them.

That happens, thank you for making the spreadsheet in the first place!

Just come across this thread. I know its a little old. Has anyone got this to run in Vista using Office 2007? I have the solver add-in installed but when I click on the "click hear to find water adjustments" button I get

Complile error: cannot find project or library.

Any ideas how to solve this? Would be great if I could get this working as its exactly what I'm looking for.

Cheers, Andrew

axp said:

Just come across this thread. I know its a little old. Has anyone got this to run in Vista using Office 2007? I have the solver add-in installed but when I click on the "click hear to find water adjustments" button I get

Complile error: cannot find project or library.

Any ideas how to solve this? Would be great if I could get this working as its exactly what I'm looking for.

Cheers, Andrew

Click to expand...

Open in 2003 compatibly mode.

It opens by default in compatibility mode

I can't for the life of me get this working in Office 2K7. I get the same results as above where I have the Solver Add-In activated but I still get the error identified above. When I tried to fix this in the VBA debugger using Tools>References, that option is disabled/grayed out. Any ideas on how to fix this so I can use this spreadsheet?

UPDATE: Never mind. Got it working (or rather a friend did). You have to go into Design Mode in VBA before you can resolve the missing references, and even then you have to delete the existing SOLVER reference before establishing the new one or you get another error. Regardless, I have a version working with Excel 2K7 now.

I've been contacted by some folks asking exactly how to fix this in Office 2K7 and 2K10. The UPDATE above gives the general instructions, but I'll be more specific. If you get an error like "Compile error: Can't find project or library" in the Visual Basic debugger, you'll need to do the following:

1. Click "OK" on the error dialog.
2. Click the icon that looks like a pencil and a triangle to enter Design Mode.
3. Under the "Tools" menu, click "References..." and you'll see a list of references, some checked and some unchecked.
4. There should be an entry like "MISSING: SOLVER.XLAM". Uncheck that one.
5. If you have another unchecked entry called "Solver", just check that and skip to step 8.
6. If not, you'll need to add it. Click "Browse..." and find "SOLVER.XLAM". For me it was located under "C:\Program Files\Microsoft Office\Office14\Library\SOLVER" and I had to show all files to find it.
7. Click "OK" on the references dialog.
8. Save and close the Visual Basic window to return to Excel.

It should work now. Please let me know if you try to follow these steps and they don't work so that we can come up with reliable steps for folks going forward.

SCWells72 said:

I've been contacted by some folks asking exactly how to fix this in Office 2K7 and 2K10. The UPDATE above gives the general instructions, but I'll be more specific. If you get an error like "Compile error: Can't find project or library" in the Visual Basic debugger, you'll need to do the following:

1. Click "OK" on the error dialog.
2. Click the icon that looks like a pencil and a triangle to enter Design Mode.
3. Under the "Tools" menu, click "References..." and you'll see a list of references, some checked and some unchecked.
4. There should be an entry like "MISSING: SOLVER.XLS". Uncheck that one.
5. If you have another unchecked entry called "Solver", just check that and skip to step 8.
6. If not, you'll need to add it. Click "Browse..." and find "SOLVER32.DLL". For me it was located under "C:\Program Files\Microsoft Office\Office14\Library\SOLVER".
7. Click "OK" on the references dialog.
8. Save and close the Visual Basic window to return to Excel.

It should work now. Please let me know if you try to follow these steps and they don't work so that we can come up with reliable steps for folks going forward.

Click to expand...

Thanks for the follow up on this - much appreciated! I haven't been on the forums much lately and even if I were, I don't have 07 or 10 and would not be able to come up with a solution as you did.

Thanks again,
BB

Okay, this may be the answer to a question no one was asking, but I "ported" this spreadsheet to OpenOffice Calc using its non-linear program solver extension. Overall it's the exact same algorithm posted by Beercat Brewmeister but using Calc's NLP solver library instead of Excel's. It's slower than Excel's, but it generally reaches the same solution. In order to provide a speed/accuracy trade-off I've added a drop-down that lets you select the optimizer's mode: Speed, Balance, Accuracy. Given the small addition amounts generally found in the solution, I've found that Speed yields very good solutions that don't match Excel's perfectly all the time, and Balance is only a little slower but almost always matches Excel's solutions. I honestly don't see any reason to use Accuracy because it's ridiculously slow, but it represents the default settings for Calc's NLP solver.

If you want to give it a try you'll need to install OpenOffice (minimally Calc) and its NLP Solver extension. Then you'll want to configure Calc to allow unsigned macros by going into Tools>Options>Security>Macro Security... and choosing the Medium setting. Finally you'll need to download my Calc version of the Mash Water Chemistry Calculator. Overall the same instructions apply with the only real difference being the "Mode" selection above the "Click Here to Find Water Adjustments" button.

If anyone tries this out, please let me know if/how it works for you. I think next I'm going to try to merge this with Palmer's spreadsheet for one-stop water chemistry shopping.

Scott

Okay, that was easier than expected. I've unified the Mash Water Chemistry Calculator and John Palmer's Mash RA worksheet into a single spreadsheet. You just enter your source and target water profiles and mash water volume and dilution into the former, calculate your salt additions, and all of that is pushed into Palmer's spreadsheet so that you only have to do minor adjustments there. I've posted versions for Excel and OpenOffice Calc. Again, please let me know if these don't work for you, or any other form of feedback you'd like to provide on them. Hopefully they'll end up useful to at least a few other folks.

I know this thread is seriously old, but this is the tool that has been missing everywhere I look. I am trying to get this to work on a Mac in either Openoffice (preferred) or Excel and I am getting solver error messages - tried all of the above solutions... any help here? Thanks!