Mixing 2 known water sources - basic math and Beersmith question

[jgodfrey]

Hi All,

I have a Ward Labs report for my tap water and I'd like to dilute it with some R/O water to tone down some of the compounds. I also have a report for the R/O water (from the provider) that I'll assume is accurate for my purposes.

I assume that calculating the compounds resulting from a mix of the two waters is just a direct, linear relationship based on the percentages of the 2 waters in the final mix, right?

For example, if water A is 50 Na and water B is 30 Na than a 50/50 mix of the two waters will be 40 Na ( (50+30)/2 ), right?

From a mathematical perspective, that makes perfect sense to me, but I wanted to make sure there's not some other chemistry-based magic going on here.

Assuming the above is correct, does that hold true for final pH values also? That is, will a 50/50 mix of 7.6 pH and 6.0 pH result in water with a 6.8 pH ( (7.6+6.0) / 2.0) )?

And, a related Beersmith question...

Beersmith's "Water Profile Tool" can supposedly combine 2 known water profiles ("Base Profile" and "Dilute With") and then provide the necessary adjustments to target a 3rd profile ("Target Profile").

For a 50/50 mix of 9 gallons total, I am entering 4.5 gallons of my tap water profile ("Base Profile") and 4.5 gallons of my R/O water profile ("Dilute With"). Then, I select my "Target Profile" and hit "Match Target Profile". That results in a set of calculated additions to approximate the target profile.

Now, as a comparison to the above, I manually create a water profile for a 50/50 mix of my 2 waters (using the above-mentioned simple math). Then, using the "Water Profile Tool", I added 9 gallons of my 50/50 profile as "Base Profile", nothing for the "Dilute With" profile, selected the same "Target Profile" as before, and hit "Match Target Profile".

The resulting additions are not even close to the first method. Essentially, they're all basically 2x of the first method (I assume because the water quantity is 2x the first method). But, I don't understand why that is. I mean, ultimately, both are intended to be the same 50/50 mix of the two base waters...

In one case, I simply created a "pre-mixed" profile and in the other case, I allowed Beersmith to do that for me. Shouldn't the resulting additions be the same in both cases? I assume I'm misunderstanding something in Beersmith, but I'm not sure where my thinking went off the rails.

Thanks for any input you can provide.

 

[jgodfrey]

For the record, here are the results of the Ward Labs report for my tap water in case anyone has any particular input.

pH: 7.6
TDS: 400
Electrical Conductivity: 0.67
Cations / Anions: 7.3 / 7.1

Sodium, Na: 47
Potassium, K: 4
Calcium, Ca: 58
Magnesium, Mg: 27
Total Hardness, CaCO3: 258
Nitrate, NO3-N: < 0.1
Sulfate, SO4-S: 11
Chloride, Cl: 81
Carbonate, CO3: < 1.0
Bicarbonate, HCO3: 253
Total Alkalinity, CaCO3: 208
Total Phosphorus, P: 0.12
Total Iron, Fe: < 0.01

 

[ajdelange]

That uneasy feeling you had that things ought to add linearly but that pH consideration might throw a monkey wrench into the works is spot on. pH does not 'combine' linearly and computing what actually happens when waters with different mineral contents at different pH's mix is a fairly complicated process involving iterative calculations which most of the calculators and spreadsheets that I am aware of don't do. The influence of one water's pH on the other depends, of course, how different the pH's are and on the relative alkalinities. In the case, such as yours where the alkalinity of one water (RO) is much less than that of the other you can assume that all the parameters, including the alkalinity, but not the pH combine linearly. The pH of the blend will be essentially the pH of the water with the higher alkalinity. Thus blending the given water 50:50 with RO water, assuming it's ion content to be 0 across the board, would give a blend with 23.5 ppm sodium, 2 ppm potassium, 129 ppm total hardness, 104 ppm alkalinity etc. The pH will be close to 7.6. If a calculator tells you anything other than that it is in error.

 

[jgodfrey]

@ajdelange,

Thanks for the excellent input. I assumed all of the math would play out linearly. Good to know that pH doesn't fit that mold.

If anyone has input on the (seemingly?) odd Brewsmith results mentioned above, I'm listening.

Thanks again.

 

[ajdelange]

I really can't say much about Beersmith as I don't know much about it. A quick download of it and a look at the water section reveals a couple of things. It purports to be able to calculate the properties of a blend without asking for the pH or alkalinity of either the main or dilution water. That is impossible to do.

When I asked it how to make water with 2 mmol/L Ca++, 2 mmol/L SO4--, 3 mmol/L Cl- and 1 mmol/L Na+ (which is easily done by adding 1 mmol each of CaSO4, CaCl2, and NaCl to a liter of water and gives an exact result) it told me to add some MgSO4 and gave a profile that was in error in each ion concentration, though none of the errors exceeded 10%. But this is the easiest case I can think of. As it does not ask for pH it cannot possibly handle chalk or sodium bicarbonate additions (which it allows) correctly. Thus while I am surprised that the answers it gave you are off by a factor of 2 relative to a back of the envelope calculation I am not at all surprised that they seem to be substantially wrong. I would not, based on this quick look, use that program for water calculations.

 

[jgodfrey]

Thanks again for the input (and for going above/beyond for downloading and taking a look at Beersmith). While I'm no Beersmith expert, I'm not sure we're on the same page here...

The Beersmith tool I'm talking about is the "Water Profile Tool" (accessed via Tools | Water Profile Tool).

In that tool, you specify either 1 or 2 input "water profiles" (Base Profile and Dilute With) and then a "Target Profile" (the one you're trying to build.

While you're correct that the "Water Profile Tool" itself doesn't request any pH values, that info is already part of the profiles you select in the tool.

Individual water profiles can be created via the "Ingredients | Water | Add Water" tool. There, you give the profile a name and then specify all of the important chemical information (including pH).

Once you've created the profile(s) you're interested in using in a batch, you use those profiles in the "Water Profile Tool" when building water to match a given profile.

So, since the pH is defined in each profile that's used by the tool, I assume it's used in the calculations - though I really have no idea.

Does that change anything in your previous analysis?

Thanks again for the input.

 

[ajdelange]

While I'm no Beersmith expert,

Well i'm obviously not either.

I'm not sure we're on the same page here...

Let's have another look.

The Beersmith tool I'm talking about is the "Water Profile Tool" (accessed via Tools | Water Profile Tool).

In that tool, you specify either 1 or 2 input "water profiles" (Base Profile and Dilute With) and then a "Target Profile" (the one you're trying to build.

That is the page I was looking at.

While you're correct that the "Water Profile Tool" itself doesn't request any pH values, that info is already part of the profiles you select in the tool.

What do you do if your water isn't one of the listed profiles?

Individual water profiles can be created via the "Ingredients | Water | Add Water" tool. There, you give the profile a name and then specify all of the important chemical information (including pH).

I can't find that option. If I click Ingredients > Water the available tabs are My Recipies, Water Profile and Water. Perhaps if I fiddled with it longer I'd find the pH entry point. But at this point I can only enter an ion profile but not its pH. If the man knew what he was doing he would most probably ask for input of alkalinity and pH as that is what is measured by the lab. If the lab reports bicarbonate it calculates it from those two parameters. A solid program needs the alkalinity value ans that can be calculated from bicarbonate and pH but I can see, from looking at the Dallas profile (bicarbonate 45, alkalinity 37, pH 9) that he isn't doing the calculation correctly. For bicarbonate 45 and pH 9 the corresponding alkalinity is 42, not 37 as indicated by the program. From the fact that he reports 37 I see right away that he is using the commonly used approximation formula alkalinity = 50*[HCO3]/61. All the popular spreadsheet programs use this AFAIK but as we see here it falls apart when pH gets above 8. Beyond that bicarbonate of 45 does not balance the other ion contents. The balancing bicarbonate content for those is 16.7 mg/L corresponding to an alkalinity of 17 (not the 37 indicated in the program. The fact that he isn't using the correct (its given in the Sticky on bicarbonate and carbonate) formulas here (they are in the Sticky on bicarbonate and carbonate) suggests that he isn't handling pH correctly in other places either.

I did also check the first profile and it does balance.

Does that change anything in your previous analysis?

No. While I don't feel that I have by any means given this program a thorough review I have learned over time where most of these popular programs fall short and know where to prod them to see if new ones repeat the errors of their predecessors. Most of them, so far, do, but I am still hopeful that someone with the programming skills that obviously went into this one but who also understands the chemistry will undertake to do a calculator. In this one I think its great that he is using linear programming to get a 'best' solution but clearly he hasn't implemented it right as it doesn't give a correct answer for the simplest of all possible target profiles.

 

[jgodfrey]

I can't find that option.

It sounds like you've been able to access the "Water" tab. In case not, that can be done via either of:

1. Main menu item "Ingredients" | "Water" ... or ...
2. From the "Tree" control on the left (Ingredients | Water node)

Either of those should open a "Water" tab in the main application area.

That tab contains all of the water profiles that have either 1) been included with the app, or 2) created/imported by a User. Double-clicking one of those profiles opens a dialog where you can edit the selected profile. That dialog contains all of the chemical details about the water profile, including the pH.

To create a *new* water profile...

As you select various "tabs" in the application, the buttons found in the "ribbon control" (area just below the main, top-level application menu) change to provide commands appropriate for the selected tab. When the "Water" tab is selected, the ribbon buttons change to include an "Add Water" button. Clicking that will open the same "edit" dialog mentioned above, but for a *new* water profile.

That dialog looks like this.

 

[jgodfrey]

In addition to the above "how to" response, thanks for the additional details. While much of what you've said is beyond my current understanding, it's good to know that I should take the provided results with a grain of salt.

Thanks again.

 

[ajdelange]

Don't know why I couldn't find that before. Probably staring right at it without seeing it.

Anyway, experimenting with the Burton profile I find that changing the pH field makes no change in alkalinity or bicarbonate fields and that's just not right. Accepting 300 ppm as the alkalinity, the water's bicarbonate content would be 368 mg/L or 370 mg/L depending on whether, respectively, the Ward Labs titration end point of 5.4 or the ISO titration end point of 5.5 was used in the alkalinity measurement. I think we can accept the program's 366.6 as close enough for government work. At pH 6, however, the bicarbonate would be 381 mg/L and at pH 9 it would be 329 but putting those values into the pH field makes no change to any other field. Thus it appears that at least in terms of the ion content of a source water the pH entry has no effect. Perhaps it is given as a warning that it is only at the pH given for the supplied profiles do they balance. Were that the case though I wouldn't think that he would allow you to change it. Thus it seems that this program allows you to enter a water profile that can't exist. Most of the programs give the user an indication of ion balance in order to avoid this problem. Or perhaps the info is here somewhere and I haven't found it.

It's really not that daunting to put together an Excel spreadsheet that will allow you to
1. Calculate the ion content and pH of a blend of two waters
2. Calculate the set of salt additions that gives you the best percentage deviation from a desired profile (preferrable to one that gives you the best fit based on arithmetic deviations)

In both cases one relies on Excel's powerful optimization tool. I'll be happy to get into the details of how to do this if you like.

 

[jgodfrey]

If you're game for going into some of those details, that'd probably be a great service to the community and I'd be really interested. However, I can't promise that I'd actually do anything constructive with the info. With that in mind, and knowing that it's probably a big ask to go down that path, I'll leave that up to you.

Either way though, thanks for the guidance you've provided here.

 

[ajdelange]

Let's give it a shot then. Open a empty Excel work book and paste the following, down to ****, into column A. Be sure to copy a space above Ca++ mg/L so that Ca++ mg/L goes into cell A2 and cell A1 is empty.

Ca++ mg/L
Mg++ mg/L
Na+ mg/L
K+ mg/L
Cl- mg/L
SO4-- mg/L
Alk, ppm as CaCO3
pH
Volumes, L

HCO3- mg/L
CO3-- mg/L


H+ mEq/L
OH- mEq/L
r1
r2
f0
f1
f2
Qw mEq/L
Qc mEq/mmol


Alk mEq/L
Water Alk
Carbo Alk
Carbo
Qcarbo

QCa++
QMg++
QNa+
QK+
QCl-
QSO4--
Qwater
Qother

BLEND
Carbo mmol/L
Qcarbo mEq/L
QCa++
QMg++
QNa+
QK+
QCl-
QSO4--
Q other
Qwater

SUM of Q's
****

Now paste the following into column B. Start your copy at "Source 1" so that it goes into cell B1

Source 1
18.5
10
17
2
35
27
50
7
3

=B30*B21*61
=B30*B22*60


=1000*10^(-B9)
=1000*10^(B9-14)
=10^(B9-6.38)
=10^(B9-10.38)
=1/(1+B18 +B18*B19)
=B18*B20
=B19*B21
=B16-B17
=-B21-2*B22


=B8/50
=D23-B23
=B27-B28
=B29/(D24-B24)
=B30*B24

=B2/20
=B3/12.15
=B4/23
=B5/39.098
=-B6/35.45
=-B7/48.04
=B23
=-SUM(B31:B39)

This works on a Mac (i.e. copying from the post). So try it and let me know if it works for you. You should have a functioning spreadsheet with two columns. The formulas should be replaced with numbers.

If you have success I'll talk about what the various rows mean in another post tomorrow.

 

[ajdelange]

This is a comment on the statement that I made in No. 3 to the effect that under certain conditions the alkalinity of a blend of water would be close to that obtained by a weighted linear combination of the two sources' alkalinities. In a sort of "Doh!" moment I realized that the alkalinities combine linearly under any conditions and that the pH shift is that consistent with that combined alkalinity. Thus to combine two sources compute the linear combination of calcium, magnesium... alkalinity and carbo (the carbon in H2CO3, HCO3- and CO3--) and then compute the pH with those combined parameters. The spreadsheet in No. 12 as given so far computes the carbo for one source and as we go on we'll get to how to use it with the carbo from another source to get to the combined pH. What all this means is that while calcium and magnesium, for example, combine linearly, bicarbonate, carbonate and pH do not. Spreadsheets and calculators that calculate blends by linear combination of bicarbonate, which many authors seem to think is the most important parameter (it isn't - alkalinity is), will produce errors. Fortunately, they are usually fairly small.

That alkalinity combines linearly follows trivially from its definition as the numnber of mL of 1 N acid required to bring 1 L of a water sample to a reference pH (4.5 in the ISO procedure). If it takes x mL per liter of Sample1 and y mL per liter of Sample2 then it is obviously going to require x*L1 + y*L2 ml to bring L1 liters of Sample1 and L2 liters of Sample2 for (x*L1 + y*L2)/(L1 + L2) per liter of the blend.

 

[jgodfrey]

Sorry for the delayed response. I spent yesterday brewing... ;^)

Anyway, so far so good... The system where I did the initial testing of your spreadsheet info doesn't have a copy of Excel, so I tested it using Google Sheets. While everything so far seems to work as intended in GS, if that's not the case as you continue here, I'll switch to a system with Excel.

 

[ajdelange]

Sounds good. So we can start talking about the individual lines we have so far.
The A column is obviously just labels. Column B deals with the water form the first of two source waters which are to be blended. Cells B2:B10 contain the input data alll of which are in mg/L except the alkalinity which is in units of ppm as CaCO3, pH which is pH units and volume which is in liters. B12:B13 contain the bicarbonate and carbonate concentrations for the parameters in B2:B10. Not that if you look at the formulas you will see that they depend on things calculated lower in column B. I just put them here to group them with the other parameters descriptive of this water. B16:B24 are the heart of the spreadsheet. In these cells we compute the way in which charges are distributed between H+, OH-, H2CO3, HCO3- and CO3-- ions. The H+ and OH- charges are per liter of water and depend only on its pH. The ratio r1 is the ratio of the concentration of bicarbonate ions to carbonic acid molecules and depends only on the sample pH and the first pK (acidity constant = 6.38) of carbonic acid. r2 is the ratio of the concentrations of carbonate ions to bicarbonate ions and it depends only on the sample pH and the second pK, 10.38, of carbonic acid. Note that in fact r1 and r2 do depend slightly on the presence of all other ions such as Na+, Cl-, SO4-- etc. but these effects are small and we ignore them here for simplicity. So if there are x moles of carbonic acid present there must be r1*x moles of bicarbonate ion and thus r2*r1*x moles of carbonate ion. It's plain from this that if we denote the total moles of carbon from the carbonic acid system as Ct then Ct = x + r1*x + r1*r2*x and the fraction resident in carbonic acid is x/(x + r1*x + r1*r2*x) = f0 = 1/(1 + r1 + r1*r2). This is really the key to understanding brewing water chemistry so think about it a bit. It is a direct consequence of the law of mass action applied to the dissociations of carbonic acid and bicarbonate ions. We call the fraction of Ct associated with carbonic acid f0 as it carries 0 charge. Continuing, the fraction of Ct which is in bicarbonate ions is simply f1 = r1*f0 designated by f1 as bicarbonate carries 1 negative charge. And f2 = r2*f1 is the fraction of Ct carbon which is resident in carbonate ions.

B23 is the sum of the H+ and OH- ions in one liter of water and represents the charge on one liter of water from the water itself.
B24 is simply Qc = -f1 -2*f2 which is the charge on all the ions associated with 1 mmol of Ct distributed, as it is at this pH, over H2CO3, HCO3- and CO3--. Thus Qc is the charge on carbo species per mole of carbo.

Alkalinity is defined as the number of mEq of acid required to change the pH of a sample from the pH at which the sample presents to a reference pH. To measure it a lab adds acid of known strength (usually 0.1 N) to a carefully measured volume (usually 0.1 L) of sample until the reference pH (usually 4.5) is reached. If 0.1 N acid is used with an 0.1L sample then the mEq/L acid required, which defines the pH, is simply the number of mL used. The lab usually multiplies the number of mEq/L by 50 to get the ever popular and confusing ppm as CaCO3 units and reports that. But the basic wrok they did and we will do from here on is much more conveniently handled in mEq. Because of the widespread use of ppm as CaCO3 to describe alkalinity we have you enter your alkalinity in row8 as ppm as CaCO3. In Row 27 we convert that to the mEq/L we need for further calculation.

Alkalinity has two parts. The first part is the mEq/L acid required to change the charge on the water from what it is in B23 to what it is at the reference pH. How do we get that? Copy cells B16:B24 and paste them first into C16:C24, than D1624 and finally into E16:E24. These three block calculate, then, the same things as B16:B24 except for different pH's as entered in C9, D9 and E9. Cell C9 gets the pH of the second source water and into D9 we put the reference pH which is, for ISO alkalinity 4.5 though Ward Labs uses 4.4. Then we have in D23 the mEq/L charge on the water at the reference pH. Because at the lower reference pH there are more H+ than OH- ions D23 is going to be more positive than B23 and so D23 - B23 is the amount of acid required to get the water itself from the pH of Sample1 to the reference pH. This is the alkalinity of the water itself - not the alkalinity of the water and the stuff in it. If we take the alkalinity of the sample and deduct the alkalinity of the water what must be left is the alkalinity of the stuff in it which are the carbo ions. This is in B29. It it equal to the number of mmoles of carbo in a liter of the water multiplied by the quantity of protons (amount of acid) that must be supplied to 1 mmole of carbo to change its charge from that at the pH of Sample1 to that at the reference pH. This is (D24-B24) and so the mmols of carbo in a liter of Sample 1 must be B29/(D24-B24). Once we have Ct, the amount of carbo, we can multipy this by Qc, the charge per mmol, to get the total charge on the carbo species (HCO3- and CO3--) it is this number, in B31, that is properly used in a balance calculation. In B22:B38 we calculate the charges on other ions by multiplying their concentrations in mg/L by their equivalent weights in mEq/mg. We then put the charge on a liter of water into cell B39 and sum from B32:B39 and put that value times -1 into cell B40. This guarantees that the sum of all the charges, including that from some unmeasured ion which appears in B40 is 0 as must be the case for any physically realizeable water. We don't use these data for anything other than to check that the magnitude of B40 is pretty small. It represents errors, in measurements made or by omission of measurement on ions that are present and is thus, a measure of the quality of the water profile we entered into B2:B9.

 

[ajdelange]

Here's what goes into Column C
Source 2
60
10
17
2
35
27
150
9
6

=C30*C21*61
=C30*C22*60


=1000*10^(-C9)
=1000*10^(C9-14)
=10^(C9-6.38)
=10^(C9-10.38)
=1/(1+C18 +C18*C19)
=C18*C20
=C19*C21
=C16-C17
=-C21-2*C22


=C8/50
=D23-C23
=C27-C28
=C29/(D24-C24)
=C30*C24

=C2/20
=C3/12.15
=C4/23
=C5/39.098
=-C6/35.45
=-C7/48.04
=C23
=-SUM(C31:C39)


=($B$10*B30+$C$10*C30)/$D$10
=C43*E24
=($B$10*B33+$C$10*C33)/$D$10
=($B$10*B34+$C$10*C34)/$D$10
=($B$10*B35+$C$10*C35)/$D$10
=($B$10*B36+$C$10*C36)/$D$10
=($B$10*B37+$C$10*C37)/$D$10
=($B$10*B38+$C$10*C38)/$D$10
=($B$10*B40+$C$10*C40)/$D$10
=E23

=SUM(C44:C52)

And this goes into column D from D9 to D61
4.5
=B10+C10





=1000*10^(-D9)
=1000*10^(D9-14)
=10^(D9-6.38)
=10^(D9-10.38)
=1/(1+D18 +D18*D19)
=D18*D20
=D19*D21
=D16-D17
=-D21-2*D22




















=C45*20
=C46*12.15
=C47*23
=C48*39.098
=-C49*35.45
=-C50*48.04
=C51*(87.62/2)
=C43*(D24-E24)
=(D23-E23)
=D52+D53
=50*D54
=(B10*B8+C10*C8)/D10
=C43*E21*61
=(B12*B10+C12*C10)/D10
=C43*E22*60
=(B13*B10+C13*C10)/D10
=(B10*B9 + C10*C9)/D10

And finally this goes into column E for E9 to E61
8.36376993110522






=1000*10^(-E9)
=1000*10^(E9-14)
=10^(E9-6.38)
=10^(E9-10.38)
=1/(1+E18 +E18*E19)
=E18*E20
=E19*E21
=E16-E17
=-E21-2*E22




















Ca ++ mg/L
Mg++ mg/L
Na+ Mg/L
K+ mg/L
Cl- mg/L
SO4-- mg/L
Other as Sr
Carb alk mEq/l
Water alk mEq/L
alk mEq/L
alk ppm
alk lin approx
HCO3- mg/L
HCO3- lin approx
CO3-- mg/L
CO3-- lin approx
pH lin approx

 

[ajdelange]

The columns in the last post complete the spreadsheet. If you have pasted them into Excel and hit Ctrl-` (I don't know how to do this in any of the Ecel imitators) you should see something that looks like this:

 

[jgodfrey]

I haven't had time to experiment with this yet, but I have it all in GS and it seems to be functional.

FYI - Some of the cut/paste info for columns D&E are intermixed in your above post, but it's not hard to straighten out based on the provided screen shot.

Thanks!

 

[ajdelange]

FYI - Some of the cut/paste info for columns D&E are intermixed in your above post, but it's not hard to straighten out based on the provided screen shot.

Then it's a good thing that I posted that screen shot! But I also went back and corrected No. 17 to fix this.

I'll give people a day or two and then start posting on what the spreadsheet does.