Bru'n Water Spreadsheet question

Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum

Help Support Homebrew Talk - Beer, Wine, Mead, & Cider Brewing Discussion Forum:

This site may earn a commission from merchant affiliate links, including eBay, Amazon, and others.
We've overlooked some pretty important things here which derive from the fact that the problem is underdetermined (more salts available than ions we wish to match). In the simplest case we only add salts - no acids or bases (including sodium bicarbonate) so the pH can't change. The problem is find x such that A*x = b where A is the matrix that tells us how many moles of chloride and how many moles of calcium come from calcium chloride (2 and 1 respectively), x is a vector of salt additions (in moles) and b is a vector of the desired ion concentrations (moles). As the rank of A is less than the number of elements in b there are an infinite number of solutions and the matrix doesn't have an inverse but we can get the particular solution with the smallest norm using the MPP call it x_p. But this solution may call for negative calcium chloride for example depending on whether b represents something achievable with the selected salts. The complete solution set is x_p + a1*v1 + a2*v2 +... where a1 and a2 etc are arbitrary coefficients and v1 and v2 etc are vectors which span the nullspace of A. In words, the complete solution set is the particular solution plus any that maps to the null space of A. SVD factors A into the product of three matrices: A = U*w*V_T and the pseudo inverse of A is A# = (A_T*A)^-1*A_T = V*w^-1*U_T so that x_p = (A_T*A)^-1*A_T*b = V*w^-1*U_T*b and it's clear the the shortest solution is easy to find given that A has be decomposed.But one of the goodies that comes free with SVD is that the basis vectors for the null space are the columns of V that correspond to the 0 singular values in w. Thus SVD gives the complete solution set in one step - the singular value decomposition of A. If number of solutions found per second is a criterion then MPP-SVD wins hands down.

But given that we have all the solutions we are in a quandary. There are an infinite number of them an infinite number of which will be valid and an infinite number invalid. x_p may not be a valid solution (but it may - it would be easy enough to test it). We need to find a valid one. More to the point, when we include the addition of acids and bases the pH changes and we want to keep the synthesis pH within bounds. We cannot simply add pH to the target vector and solve A*x = b because pH is not linearly related to the salt/acid/base contents. What we have to do, therefore, is linearize the problem about some solution x0, map it to b0 by A*x0 = b0 and then solve J*∆x = b-b0 for ∆x. Here b -b0 is the residual (error) vector which, clearly we want to minimize and the matrix A has been replaced by the Jacobian, J, matrix of partial derivatives of the ion concentrations and pH with respect to the salts/acid/bases. ∆x is found from the SVD of J as above. It is then added to x0 to give x1 = x0 + ∆x which will reduce the residuals. We can dot ∆x with a vector pointing into the portion of solution space we'd like to be in and thus control the nature of the solution we get (a particular chloride to sufate ratio, for example). Thus we are using a directed search for one of the solutions with some properties we want. Once we have that there is no point in looking for other solutions but there is nothing to stop us from doing that if we want.

With an heuristic algorithm you are doing the same thing with the difference being that you are moving around in the solution space heuristically whereas with MPP you are moving about deterministically. Thus the latter will converge to a solution faster and that will be its main advantage over an heuristic algorithm. Neither will give solutions better than the other. Both will give a many solutions as you want but there is no advantage to having more solutions.
 
Last edited:
Because not everyone has Excel and its Solver capability. While that self calculating function could be incorporated, I'm impressed that its too much trouble for some people to take less than a minute to get their additions figured out. Sorry for the disappointment. There are too many brewers that don't have the resources to own Excel.
While everyone might not be able to have Excel, I'm guessing mainly because it's not affordable. Using Google Sheets works just as well, unless there's something in this spreadsheet that requires Excel. Could also use Open Office.

I haven't played with adjusting my water because getting all the right info from my local water board hasn't been easy. But, there's alternatives to Excel that have 95% of the functionality.
 
Back
Top