Shouldn't spreadsheet be automatic

[esahlong]

It seems all the water spreadsheets require tedious manual addition of salts and acid. Why can't this be automated easily to reach target? It is very time consuming and bothersome to constantly enter value and guess, though it gets better with time/experience, still automation would be beneficial.

 

[cyanmonkey]

Beersmith will do this.

 

[Hello]

I agree there is a good deal of manual entry that you must do; however, if you know of a way to automate these things then I would certainly be pleased to check it out. Identifying an issue without proposing a solution just kind of makes this thread a complaint thread.

Perhaps the better way to put this out there would be to ask anyone if they're aware of a spreadsheet that automates some of the need to enter things manually. In truth, I cannot see how this would be automated though.

Edit: I had no idea BeerSmith did it. I'm going to check this out today.

 

[cgrivois]

Would be nice to enter your water profile, enter target profile, enter grist and water amounts then mash the enter button.

 

[GotPushrods]

Sometimes solving a multivariable equation isn't as straight forward as you think. If there is a solution at all. You could use the Solver tool of Excel, but it's all really an academic exercise anyway.

The way I see it, the first 10 to 20 seconds of work get it close enough. After that, nobody has a palette that will discern the differences. A few ppm is irrelevant to the end perception (which is the whole point, right?) and is another good example of why chasing an exact profile is pretty futile.

 

[mabrungard]

Sometimes solving a multivariable equation isn't as straight forward as you think. If there is a solution at all. You could use the Solver tool of Excel, but it's all really an academic exercise anyway.

The way I see it, the first 10 to 20 seconds of work get it close enough. After that, nobody has a palette that will discern the differences. A few ppm is irrelevant to the end perception (which is the whole point, right?) and is another good example of why chasing an exact profile is pretty futile.

While I agree that it is nice to push a button and have an answer pop out, I also find that it only takes a few seconds to work through a few potential additions to produce a profile that is 'close enough'. It is foolish to try and get additions down to the gnat's ass. Brewing water chemistry doesn't have to be that exacting and there is no way that anyone would be able to tell that the chloride ion was at 45 ppm vs. 40 ppm.

Another thing that the users of Bru'n Water have been pushing for is to get rid of any sort of macro features (drop down boxes and sliders), so that the program will work on more computer platforms. Adding Solver to Bru'n Water is exactly the wrong direction according to their comments. (By the way, a user did create a Solver and it works well, but that isn't something that the majority want).

 

[philipCT]

I do agree that some kind of rough fit should be possible. Maybe not using spreadsheet tools, but certainly in an app.

I know that there are whole disciplines of mathematics dedicated to solving multivariate equations, but if you left the tolerances fairly loose, it seems like some of the work could be taken out of the front-end of matching a desired profile. Maybe we have some AI programmers on the team?

On another note, if Beersmith has water chemistry, I'll be horn-swaggled, but PLEASE let me know if I'm wrong.

Edit/Postscript: I do agree with Martin that once you get used to Bru'nwater, it's pretty quick and easy. I did find that it took me some time to really understand how to use it though, but I am kind of dull

 

[GilaMinumBeer]

On another note, if Beersmith has water chemistry, I'll be horn-swaggled, but PLEASE let me know if I'm wrong.

"New Feature: BeerSmith now includes a Calculate Best Additions button that automatically finds the best water additions to match your base and target water profile. It does this using a "best fit" linear programming model."

http://www.beersmith.com/help2/index.html?water_profiler.htm

 

[thekraken]

FWIW I agree with OP and vote for an auto calc button.

My feeling is if your going to use a computer to crunch some of the numbers you might as well let the computer crunch all of the numbers be as exact as possible in a fraction of the time it would take to do it by hand. Now you (theoretically) know exactly what you need to achieve exactly what you want, you can then make judgement calls and be as precise or imprecise as you want.

 

[esahlong]

(By the way, a user did create a Solver and it works well, but that isn't something that the majority want).

Where can this solver be downloaded?

 

[esahlong]

Beersmith will do this.

Does Beersmith's water feature compare better or equal to the other spreadsheets?

 

[ajdelange]

Solver comes with Excel but you have to do something minor to 'install' it and make it available. Or you did. At one time Microsoft took it out and you had to get it from its manufacturer. Then they put it back in. It appears under the 'Tools' menu when it is ready to go. If it is there you can start using it. If it isn't then do a search for it in Excel help.

It is very powerful. I use it all the time to predict mash pH (it iteratively tries trial pH's until it finds the one that zeroes the sum of all the mash component proton deficits). I can also use it for synthesis of ion profiles but I seldom do that anymore unless someone has a question here. It systematically tries sets of salts searching for the set that minimizes the rms error between desired and achieved ion profiles. And it does all kinds of other stuff too like computing the amount of acid needed for a particular mash pH given a given set of grains or how much carbon dioxide needs to be dissolved to bring a given water to saturation WRT the atmosphere.

I find that in general people are afraid of it for some reason. But some of the problems brewers are interested in can't be conveniently solved (other than by groping as discussed here) without it. I asked at an MBAA talk how many in the room used it. Only a few hands, e.g. Karl Siebert's, went up.

 

[esahlong]

Solver comes with Excel but you have to do something minor to 'install' it and make it available. Or you did. At one time Microsoft took it out and you had to get it from its manufacturer. Then they put it back in. It appears under the 'Tools' menu when it is ready to go. If it is there you can start using it. If it isn't then do a search for it in Excel help.

It is very powerful. I use it all the time to predict mash pH (it iteratively tries trial pH's until it finds the one that zeroes the sum of all the mash component proton deficits). I can also use it for synthesis of ion profiles but I seldom do that anymore unless someone has a question here. It systematically tries sets of salts searching for the set that minimizes the rms error between desired and achieved ion profiles. And it does all kinds of other stuff too like computing the amount of acid needed for a particular mash pH given a given set of grains or how much carbon dioxide needs to be dissolved to bring a given water to saturation WRT the atmosphere.

I find that in general people are afraid of it for some reason. But some of the problems brewers are interested in can't be conveniently solved (other than by groping as discussed here) without it. I asked at an MBAA talk how many in the room used it. Only a few hands, e.g. Karl Siebert's, went up.

I agree, it would easily allow one to play around with the variables. (As opposed to manually "starting over" if you will.)

 

[cyanmonkey]

Does Beersmith's water feature compare better or equal to the other spreadsheets?

It compares, but not perfect with spreadsheets as much as they don't compare with each other. It is very close to Bru 'n Water, however.

 

[Braufessor]

I don't see where this would save a lot of time in the end, or make it any easier. You would still need to enter your grains, sparge and mash volumes, preference of which additions you would be using..... gypsum, CaCl, Baking soda, Lactic or phosphoric acid. You would have to designate dilution % with RO and pick a style guideline..... By that point, the only thing that is left is to put a couple numbers in the gypsum or CaCl box....... that is about the least tedious aspect of the entire process in my opinion.

I always save my spreadsheets for my beers and then just reopen older ones to tweak anything that might be new with the latest version of a batch.

 

[cyanmonkey]

"New Feature: BeerSmith now includes a Calculate Best Additions button that automatically finds the best water additions to match your base and target water profile. It does this using a "best fit" linear programming model."



http://www.beersmith.com/help2/index.html?water_profiler.htm

Another note, from what I can tell Beersmith doesn't predict mash ph or acid additions, just salts.

 

[Yooper]

"New Feature: BeerSmith now includes a Calculate Best Additions button that automatically finds the best water additions to match your base and target water profile. It does this using a "best fit" linear programming model."

http://www.beersmith.com/help2/index.html?water_profiler.htm

yes, but.................it's a 'dumb' best addition calculator. What I mean is, it will match up the sulfate level, etc- but the flavors won't be optimized and neither will the mash pH. In other words, to match the calcium level, it may ask for calcium carbonate (chalk) which you generally never want to use and this will have a flavor impact.

There really isn't any substitute for doing it yourself- but we're talking about maybe three minor additions total for each beer.

Does Beersmith's water feature compare better or equal to the other spreadsheets?

It's worse, from what I've seen. Ridiculous additions that won't give the beer the correct flavor although the ions sort of would mash up.

Mash pH is really the most crucial part of the whole thing. It really doesn't matter if calcium is 48 ppm vs 98 ppm, or if sulfate is 137 vs 155, for example, if the mash pH is 5.9.

 

[esahlong]

A lot of mis-understanding and mis-information here.

A good automatic spreadsheet would target what the user enters and use that which the user selects.

It would make it very easy to play-around with different target profiles and mash pHs.

The different platform argument seems a little absurd as what platforms don't support spreadsheets? Bru N Water seems to run fine on Mac, Windows, Linux...

The arguments of "it only takes a couple minutes" and "you don't have to be precise" are invalid as if a computer is doing the work, then let the computer be as precise as the user wants. The length of the time the computer works on a problem should be selectable also.

I really don't see the problem, except that no-one has pursued this. (Well almost no-one according to Martin.)

Most people here are being negative about a positive improvement to the water spreadsheets. So there's a button on the spreadsheet, click it if you value the improvement or ignore it if you don't.

 

[mchrispen]

I am going to lean the direction of the feedback of the Bru'n users. I want absolute control of what I am putting into my beer and have found that there maybe several directions to take in achieving a given goal - all with slightly different compromises... an example might be using pickling lime v baking soda (more calcium v more sodium) or using Epsom v gypsum (adding magnesium v calcium) in achieving my target sulfate and chloride levels.

The solver might be a solution for some people... but you lose some creativity in the process.

The BeerSmith calculator does not help to manage mash pH, and that is the critical missing element. Now - I would get behind Bru'n Water capability licensed into BeerSmith. That might be an interesting twist.

EDIT: One more comment. If everyone donated to use Bru'n Water, then the arguments would be much stronger. The reality is that the vast majority use the free spreadsheet - which limits the impetus to make massive (and effectively programming and debugging Excel across those platforms is a challenge) interactive elements to the program.

 

[esahlong]

I am going to lean the direction of the feedback of the Bru'n users. I want absolute control of what I am putting into my beer and have found that there maybe several directions to take in achieving a given goal - all with slightly different compromises... an example might be using pickling lime v baking soda (more calcium v more sodium) or using Epsom v gypsum (adding magnesium v calcium) in achieving my target sulfate and chloride levels.

The solver might be a solution for some people... but you lose some creativity in the process.

The BeerSmith calculator does not help to manage mash pH, and that is the critical missing element. Now - I would get behind Bru'n Water capability licensed into BeerSmith. That might be an interesting twist.

That makes absolutely no sense at all. Why wouldn't you be able to choose the salts and profiles and pH to target.

 

[ajdelange]

Sometimes solving a multivariable equation isn't as straight forward as you think. If there is a solution at all.

It's pretty clear if we are trying to figure out how much calcium chloride and how much calcium sulfate and how much magnesium sulfate and how much sodium bicarbonate to add to even DI water (simplest case) to get desired concentrations of sodium ion, calcium ion, chloride ion etc. given that calcium and chloride are in fixed proportion in CaCl2 and similarly for other salts and that the desired ion concentration may not be balanced that there will be no solution. Put another way if x is the vector of salt amounts and y the vector of desired ion concentrations and A a matrix with entries relating to the amount of chloride in a gram of calcium chloride, the amount of calcium in a gram of calcium chloride, the amount of chloride in a gram of sodium chloride etc. the system of equations we'd need to solve is A*x = y. As noted there may be (in fact, probably isn't) any solution y = (1/A)*x and so we seek the best solution i.e. the one that minimizes |A*x -y| or one that minimizes some other cost function calculated from the matching errors. This is where Excel excels. Add the nuance of allowing the pH to shift in order to get a better match and you have a problem that only Excel (or similar linear programming software) can solve.

You could use the Solver tool of Excel, but it's all really an academic exercise anyway.

I'll make bold and say that if you aren't using Excel you might as well guess as without it you aren't going to get very good solutions. And, BTW, that's what I usually do. I only fiddle with spreadsheets because I'm interested in finding out whether it is possible to build a good one (I am doubtful at this point).

 

[mchrispen]

What makes no sense? BeerSmith only manages to solve ion concentrations and has no consideration for mash pH, other than the ability to notate the value.

Relative to the spreadsheet, anyone is welcome to create their own solver that rides on top of Martin's work. What doesn't make sense is the argument that this should be done in a market where the vast majority of users are not paying for the functionality. I would also submit that such interactivity is better served in an app (like a website) to avoid cross-platform issues and cross-application issues (Office v Freeware solutions).

I guess I am happy that for once we are discussion functionality rather than accuracy of estimates.

 

[philipCT]

"New Feature: BeerSmith now includes a Calculate Best Additions button that automatically finds the best water additions to match your base and target water profile. It does this using a "best fit" linear programming model."

http://www.beersmith.com/help2/index.html?water_profiler.htm

Oh, yeah. Can't believe I forgot a about that. I'm now a religiously fanatic Brun'water user, so I don't know if I can adopt the BeerSmith tool for that - even though I use BeerSmith for nearly everything else, but maybe I'll take another look at it.

 

[cyanmonkey]

Oh, yeah. Can't believe I forgot a about that. I'm now a religiously fanatic Brun'water user, so I don't know if I can adopt the BeerSmith tool for that - even though I use BeerSmith for nearly everything else, but maybe I'll take another look at it.

I'll use Beersmith to calculate and then import into Bru 'n Water to fine tune. It's nice for me to hit a button and there it is as opposed to starting from zero. Beersmith just gives me a head start.

 

[mchrispen]

Why wouldn't you be able to choose the salts and profiles and pH to target.

So - that is different than an "Easy" button. To be clear, I have no issue with auto-generating suggested mineral additions, but would not likely use the feature unless I was out some specific mineral salt. Perhaps the curmudgeon is coming out in me - I have been using BWS for a couple of years and am pretty accurate as to what concentration of something will yield in ions... takes me just a few minutes at best to produce my first round and I might revisit it in planning should I want something perhaps more hop forward or more malty.

 

[esahlong]

So - that is different than an "Easy" button. To be clear, I have no issue with auto-generating suggested mineral additions, but would not likely use the feature unless I was out some specific mineral salt. Perhaps the curmudgeon is coming out in me - I have been using BWS for a couple of years and am pretty accurate as to what concentration of something will yield in ions... takes me just a few minutes at best to produce my first round and I might revisit it in planning should I want something perhaps more hop forward or more malty.

My guess is that if the tool were available you'd use it. A few minutes vs. a few seconds ... You would then be using the tool to enhance and tweak your profiles.

What is BWS?

 

[esahlong]

What makes no sense? BeerSmith only manages to solve ion concentrations and has no consideration for mash pH, other than the ability to notate the value.

Relative to the spreadsheet, anyone is welcome to create their own solver that rides on top of Martin's work. What doesn't make sense is the argument that this should be done in a market where the vast majority of users are not paying for the functionality. I would also submit that such interactivity is better served in an app (like a website) to avoid cross-platform issues and cross-application issues (Office v Freeware solutions).

I guess I am happy that for once we are discussion functionality rather than accuracy of estimates.

Martin commented that someone did make a solver for his spreadsheet. How much time/work was put in? I'm sure that person wasn't paid and just did it for fun.

The problem with charging for something like this is that it's not an application. It's a spreadsheet with a bunch of formulas.

Also, how mature is the science behind it?

The Brewers Friend calculator seems to be in a position where it would benefit from something like a solver/addition calculator.

No argument about application vs spreadsheet. Unless the application is web based you'll also get into platforms.

People seem happy to just have "something". That doesn't mean that the something that people have is correct, as long it gets them within range, it's all they have to go on.

 

[ajdelange]

How much time/work was put in?

None. Solver is part of excel. All you do is tell it what you want it to do to a target cell (e.g. zero proton deficit) and which cell or cells you want it to adjust (say amounts of hydrochloric acid and sulfuric acid) subject to any conditions you want (e.g. 2 times as much hydrochloric as sulfuric).

The problem with charging for something like this is that it's not an application.

You already paid Bill Gates for it when you bought Office.

Also, how mature is the science behind it?

Very. People have been working optimization problems like this for as long as there have been computers. For pathological problems Solver cannot find a solution or finds a local minimum (maximum) instead of a global one. So whenever someone tells you that Microsoft never produced any software that wasn't a POS, tell them about Solver (except that they didn't create it).

 

[cyanmonkey]

None. Solver is part of excel. All you do is tell it what you want it to do to a target cell (e.g. zero proton deficit) and which cell or cells you want it to adjust (say amounts of hydrochloric acid and sulfuric acid) subject to any conditions you want (e.g. 2 times as much hydrochloric as sulfuric).



You already paid Bill Gates for it when you bought Office.









Very. People have been working optimization problems like this for as long as there have been computers. For pathological problems Solver cannot find a solution or finds a local minimum (maximum) instead of a global one. So whenever someone tells you that Microsoft never produced any software that wasn't a POS, tell them about Solver (except that they didn't create it).

I've seen it referred to as Goalseek.

 

[ajdelange]

I've seen it referred to as Goalseek.

That's actually another Excel tool (also found in the Tools) menu which allows you to set one cell to a desired value by changing one other cell. It is thus an extremely simplified version of Solver which allows you to set a cell to min, max or specified value by varying dozens (not sure what the limit is) of other cells subject to dozens of constraints.