The shortcomings are some of the things I mentioned in my previous post. I guess the top level comment would be that pH is not considered and when you have a system that involves a polyprotic weak acid (carbonic acid) the key to the whole thing is pH which regulates the relative amounts of carbonic, bicarbonate and carbonate in a system.
Looking at the first line - base line water, this particular mix of ions can only exist at pH 9.02. That's higher than the WHO recommendation but it is possible that someone's tap water is actually that high in pH. Moving to the second line, the Burton target, that mix of ions can only exist at pH 9.97 which is a pretty ridiculous pH. I.E there are errors in both the baseline and Burton target waters. This is very common in the published target waters one finds in magazines, on the web etc. The discrepancies in peoples' water reports are usually smaller and are caused by measurement errors and neglect of ions present in low concentration (iron, potassium, strontium, aluminum, boron, copper...) on the part of the lab when the brewers sends off for lab analysis and, in the case of water supplier reports, by the use of averages, measurements of different ions done on different days of the week and analysis errors on the part of municipal labs.
As a minimum, any water analysis spreadsheet or program needs to check target and source reports for electrical balance and provide some means of correction. Electrically imbalanced water cannot physically exist and that means you can't synthesize it.
In the basic optimization routine you are assuming that 1 gram per gallon of sodium bicarbonate contributes 189 mg/L bicarbonate. That isn't exactly true under any circumstances because at most 98% of 189 mg/L stays as bicarbonate and 1% each convert to carbonic acid and carbonate ion. This is at pH 8.35 or so. At pH 7 20% converts to carbonic so only 80% of 180 mg/L remains as bicarbonate.
With calcium carbonate things become hairier still. Calcium carbonate contributes
no bicarbonate to the solution until enough protons are supplied to the reaction
CO3-- + H+ <--> HCO3-. For small carbonate additions the water itself provides enough but there is, of course, a pH shift which must be taken into account. For larger additions, external acid must be supplied. Nature uses carbonic. If you are trying to match a natural profile you must use carbonic too. Otherwise only poor matches are possible.
So your algorithm needs to obtain information about source and target pH, use the pH information to determine the relative fractions of carbonic, bicarbonate and carbonate and then determine the pH at which electrical balance occurs for each trial addition of salts. Then compute the residual on operate on it to "correct" the trial additions until the conditions of electrical balance and mmse are acheived. I've done this with simulated annealing and I think it could be done with iterative Moore-Penrose but as I've noted, the Excel Solver (don't know how it works but it's not annealing) will do the job.
So unfortunately, you have just scratched the surface here. The problem is not linear (because of the dependence on pH) and so an iterative solution will be required. Take out the bicarbonate and carbonate and what you have is fine as hydrochoric and sulfuric acid can be assumed completely dissociated at mash and brewing liquor pH.
Just as a matter of interest I calculated the salt additions necessary to match the example source (at pH 9.02) to the Burton target at pH 9.97. The additions are CaSO4: 2.626, MgSO4: 2.232, NaCl: 0.148, NaHCO3 0.019, CaCl2: 0.019, CaCO3: 0.732 and, most important CO2 0.118 all grams per gallon. As the match is to the profile at a pH at which the profile can exist the match is good: 1.1% maximum error. If I try to match at a more reasonable pH e.g. 7 all the numbers change but not that much with the exception of CaCO3 which goes to 0.364 and CO2 which goes to 0.293. As this match is to a profile which never existed it is not so good. Maximum error is 8%.
Again I invite you you to look at the NUBWS which can solve problems like this and at the users manual for it both available at
www.wetnewf.org. This will help you get an idea of the magnitude of the problem. The underlying chemistry can be found at the same site in the Cerevesia or New Brewer articles posted there.
