I was wondering which methods work the best for experienced water tinkerers and how much the salts, dissolved chalk or pickling lime, raise pH per gram addition (in your experience, as there is no universal standard).
Experienced tinkerers do not in general, add chalk to the water as to get it to dissolve requires acid, the spreadsheets don't calculate the amount of acid you'd need to dissolve and reach a particular pH and last, but by no means least the two major rules of thumb in brewing water treatment are 1. Alkalinity is bad and 2. Don't add alkalinity to brewing water but instead add it to the mash and do this if and only if a reliable pH measurement in the mash indicates that the pH is too low. In that case add some lime in small increments until the mash pH is correct. Lime is preferable because of rule 1. Alkalinity is bicarbonate in brewing water and if you add carbonate it becomes bicarbonate and while most of that is removed when you get to proper mash pH what's left isn't doing flavor much good.
If you do want to add alkalinity to water using the acid method calculating the amount of acid to add is difficult and must be done iteratively over the "master variable" pH. This is why most spreadsheets don't deal with it. Excel will do the iteration for you through the Solver but setting up Solver is an added complexity which takes away from the utility of a spreadsheet.
But then it occurred to me that the spread sheet doesn't have to do this nasty iterative business if somebody does it once and tells the world what the answer is. I am that somebody and here are the answers for 3 acids.
If you put calcium carbonate in water and acid to the point where all the mineral is dissolved and the pH comes to a particular value the alkalinity and the amount of acid it takes to get to the specidfied pH depends on the amount of chalk per liter and the pH. It also depends on the end point pH used to define alkalinity. Assuming end point pH is 4.3 the alkalinity of mgpL chalk dissolved with hydrochloric acid (or another strong acid such as sulfuric) and brought to pH value pHs is (approximately):
Alk = mgpL*1.0685/(1 + exp((6.3664 - pHs)/6.48654))/100 mEq/L. Multiply by 50 for ppm as CaCO3. Accuracy is good to about 1 ppm as CaCO3 (this is obviously a curve fit).
The amount of acid required to do this is 2.0408 - Alk i.e a constant minus the alkalinity calculated in the last formula in mEq/L. The amount of acid is also in mEq/L
Thus if I put 50 mg/L chalk into my liquor and add acid until the pH is 7.2 the alkalinity from the added chalk will be 0.45 mEq/L (22.5 ppm as CaCO3) and I'd need 1.59 mEq HCl per liter of water.
The only reason, in my mind, for adding alkalinity to water is if you want to duplicate the water profile of some brewing center of reknown so that you can go through all the steps the brewers of yore did. IOW you are striving for the ultimate in authenticity. You will never match real profiles with chalk and hydrochloric acid because that's not what nature uses. She uses carbonic acid. Using carbonic acid the alkalinity is
Alk = mgpL*2.031*(1 - exp((4.3 -pHs)/.42783))/100
Again this is in mEq/L and should be multiplied by 50 for ppm as CaCO3.
This time the amount of acid isn't so important as you don't measure out CO2 with a teaspoon. So I've calculated the pressure of CO2 required to bring the mix to the specified pH with the specified chalk addition. It is
PaCO2 = 10^(5.0938 - pHs)
Thus if in the same example as above except using carbonic acid I would get 1.014 mEq/L alkalinity (50.7 ppm as CaCO3) and require a CO2 pressure of 0.0078 atmospheres do get to this pH. Note that the CO2 pressure depends only on the pH. Note that in this case I got alkalinity (in ppm as CaCO3) about equal to the mass of the chalk I dissolved in ppm also obviously as CaCO3. This is the origin of the "as CaCO3" unit. This will be the case for pH's as low as about 5.75. Also compare to the strong acid where one gets about half as many ppm alkalinity as the CaCO3 he dissolved.
It's a bit tricky to dial in 0.0078 atmospheres from your CO2 bottle so what you do is sparge CO2 through the water or put the water in a Corny keg under modest pressure and when all the chalk is dissolved take the water out of the Cornie and let it stand monitoring pH as the extra CO2 escapes into the air. If you are thinking ahead you will reason that water carbonated in this way will lose CO2 and quite probably precipitate the chalk you went to so much trouble to dissolve as soon as it is heated in the HLT. That's what happens with hard carbonaceous waters to a commercial brewer and that's what will happen to you. Another reason not to bother.
Finally, I also did lactic. Lactic is interesting in that is has protons to give up (is acid) but also has alkalinity at the pH's we are intersted. So it's curve isn't quite as simple as the others and takes a pretty high polynomial to fit. The alkalinity is
Alk = mgpL*(((((((-.000741886191102494*pH+.0310611616245953)*pH-0.532742116315294)*pH+4.76952863143326)*pH-23.2787519265881)*pH+56.9307290160207)*pH-44.2861550535682)*pH-35.0097530587467)/100
This would be a bear to type into a spreadsheet but is in a form easy to cut and paste. Then substitute the appropriate cell number for pH or define pH as a variable. All the seemingly superfluous digits are there because up to the seventh power of pH is being calculated.
Again, this is in mEq/L. Multiply by 50 for ppm as CaCO3.
The amount of acid required to dissolve the chalk and attain the desired pH is 2.7808 - 1.3631*Alk but this time in mmol/L.
Continuing the example the realized alkalinity from 50 mg/L chalk dissolved with lactic acid and brought to pH7.2 is 0.608 mEq/L (30.4 ppm as CaCO3) and requires 1.95 mmol/L 100% lactic acid to achieve this.
So it turns out it is quite easy to allow for pH and correctly calculate the effects of chalk additions to brewing water after all! It should be pretty easy for the spreadsheet authors to add this into their spreadsheets and I hope they will consider doing this.
A final note: my approach involves setting my spreadsheet for ideally dilute chemistry. I cannot come up with simple formulas for alkalinity vs. pH alone if ionic strength is considered. I guess I could take the same approach and come up with fits against pH parametric in ionic strength but then the spreadsheets would have to calculate ionic strength and, AFAIK, none of them do.