I've been trying to find the answer to this same problem for a while, Bigscience. The problem I see is, how can we be sure the sparge is fully rinsing the salts from the grain? If so, how much water does it take to fully rinse the salts from the mash? Are all salts rinsed at the same rate? etc. There are many variables. So I've always worked under the assumption that the salts that are retained in the mash, stay in the mash. No one seems to be able to answer this question in my research.
EDIT: re-read the post and realize my issue is different. The problem is not with the spreadsheet but the way we treat the kettle additions. Since there is water retained by the grain, we actually add salts to the beer for more than the initial boil volume. I'll often add salts for 4 gallons of strike and salts for 6 gallons to the kettle while my initial boil volume is 7 gallons.
Keep in mind we are trying to modify our water to match the water which would be best for the beer we are brewing. So if Smallsville Michigan had perfect water for an IPA, I would try to match that. And I would want to adjust for the total amount of the water I was going to use, say 9 gallons, just like if it were perfect to begin with. Mash water is adjusted during the mash, so I want to adjust for all of my mash water, even though some of that water is left behind. Sparge water is adjusted in the kettle, but since most of that water is not left behind, I want to adjust for all of it as well. Therefore making it very similar to if you had perfect water to begin with.
Heres the scenario:
Assumptions - total dissolution of all salts. i.e. everything dissolves in the mash and doesnt stick to the grain or precipitate out of solution. The absorbtion factor of the grain does not change after subsequent batch sparges.
Preboil amount required 14.3 gallons
20lb of grain with a 0.12gal/lb absorption at a 1.5qt/lb mash ratio
Strike water is at 7.5 gallons and 10 grams of a salt is added.
2.4 gallons of water will be held in the grain due to absorption. (Im assuming the most efficient system with no other dead space or hold back volumes for simplicity sake)
5.1 gallons of run off will make it to the kettle and bring with it 6.8 grams of salt. (10/7.5*5.1)
3.2 grams of salt will be left in the mash with the 2.4 gallons of water. (10-6.8)
Now is where things can get a little trickier. For a batch sparge, the remaining salt will get diluted with each sparge. For a fly sparge, if you are adding the water at the same rate you are removing it AND you are sparging in a reasonable amount of time (not over 12 hours), a gradient will form and the saltier water will be replaced by the fresh untreated water from above. Im not sure of a mathematical way to describe it but Id bet >95% would be removed. Again, this is an unqualified statement. (As Ill show below, for a 2 batch sparge, 96% will be removed.)
Back to the batch sparge. This we can describe.
After the first runnings, 3.2 grams of salt are left in 2.4 gallons stuck in the grains. To achieve the 14.3 gallons of preboil volume required, we need 9.2 gallons of sparge water that will be divided into 2 batches. After the first addition, we have 3.2 grams in 7 gallons total in the mash. When we drain away the 4.6 gallons to the kettle, we have 2.4 gallons left with 1.1 grams of salt left in it. When we repeat for the second batch, this salt is diluted out again. Once weve collected the 14.3 gallons of wort, there will be 2.4 gallons of water stuck in the mash with 0.376 grams of salt in it.
So of the 10 grams we started with, 9.6 made it to the kettle with batch sparging. To keep the same concentration we had in the mash water in the final kettle volume we need a total of 19.1 grams salt. (10/7.5) x 14.3 So to add to the kettle to get this concentration, we need to add 9.4 grams to the kettle. If we use the EZ method wed add 12.3 grams. By doing this, wed adding 29% more salt than we should be to the kettle. In the final concentration, were off by 2.8 grams or 15% of the total concentration.
While some may say that 15% may not be that big of a deal for a water profile, I would say, why not be as accurate as we can?
This may also be an issue like religion or bbq. Everybody has their way of doing it and are convinced theirs is the only way. But then again, math is math.