Befuddled by the Defuddler

[ARNWD]

I am trying to understand @peebee 's Defuddler spreadsheet.

When targeting a water profile I use Graham Wheeler's Liquor Treatment Calculator over on JBK. I do have all the six required values from water reports and by using the Salifert alkalinity test kit but the anion's and cations don't balance. Normally I just adjust the calcium amount to bring both values for the cations and anions into balance but if the source values could be improved I would be happier. I know the values do not need to be exact - I can't measure out a single ppm of liquor salts! It is just that the most recent water report (year ended 2022) has a larger discrepancy than normal.

Remembering the Defuddler, I have downloaded and had a look but I am not sure what I should be entering and where. It may not help anyway and the data I have may be good enough anyway for Wheeler's Liquor Treatment Calculator.

@peebee if you want to have a look at the data I have the water reports here on Dropbox.

The Salifert kit syringe measures almost consistently on 0.84 mls which I make out to be 39ppm as CaCO3.

The latest values I will be using from the 2022 water report are:
Calcium 50.53
Magnesium 7.75
Sodium 14.83
Total Cations = 3.81

Carbonate 23.65
Sulphate 83.19
Chloride 22.14
Total Anions = 3.14



ARNWD

 

[aamcle]

Try the Calculator at Brewersfriend.

 

[Sadfield]

The Salifert kit syringe measures almost consistently on 0.84 mls which I make out to be 39ppm as CaCO3.

Because of this consistency, I wouldn't be too bothered about the other numbers I'd just crack on using them as they are in whatever calculator, if they're isn't issues with what's in the glass.

 

[peebee]

I always begin queries with the "Defuddler" spreadsheet with:

The "Defuddler" is not a water profile calculator! There isn't any point using it instead of a calculator like "Brewersfriend", the "Defuddler" only "conditions" the information you've gathered about your water (from public or private water reports, test kits, and any user "assumptions") and allows you to remove the confusing junk you never need before using a calculator (such as "Brewersfriend"). It fills a gaping hole in calculators such as "Brewersfriend" that assumes you know enough about water chemistry to use the calculator correctly. More times than not, the user doesn't have that knowledge and has to depend on techniques like using RO water instead of tap.

The most notable job the "Defuddler" has to do is remove all the utterly baffling codswallop of "Water Hardness" and "as Calcium Carbonate". Some (very few) do know enough about "Hardness" to make it work. For the rest, it's a source of confusion, misunderstanding and forced errors.

Having said that ... I'll answer @ARNWD's query: This is all "under the surface" stuff, or what goes on in the spreadsheet's "Foetid Mire". Which I normally advise people to stay clear of unless forced to; like entering a value for "Total Hardness" to get figures for Calcium and Magnesium that couldn't be added to the top six "surface" boxes because they weren't directly reported (I like mentioning that because it's an excellent example of how baffling "Hardness" is ... "Magnesium" from the Total Hardness figure which is expressed "as CaCO3"?!?).

But @ARNWD is diving much deeper. The "balancing" workings. Possibly because he doesn't like the less than subtle "red" warnings provided by my spreadsheet. I'm tweaking areas of that to get some answers I want: Low Alkalinity Water (Low TDS) - Jim's Beer Kit

I've got to go out now ... I'll be back! Meanwhile, here's a screenshot (just a snip) of the "Defuddler" with those "less than subtle red warnings" (and "Calcium" figure derived from "Hardness" with the help of a "real" Magnesium figure):

 

[peebee]

@ARNWD: You say you've got the top six boxes filled. Which is all the "Defuddler" is intended to do; but you want some extras out of it! Well, I'll just catch up with where I think you are and go from there. I will mention though, you are expecting this to work with "Graham Wheeler's Liquor Treatment Calculator" which is an older "Water Hardness" based calculator, and some of the results from the "Defuddler" might not fit directly into it (but we'll fiddle with that later). The results from the "Defuddler" are very much "Alkalinity" centric; such that most American brewers reading this are going to wonder what the fuss is about:

Using Yorkshire Water's "mean" values. The values are marginally different from yours above, but they are the most up-to-date from your "Dropbox":

It balances! Because I've used the "recommended" (by me that is!) route of filling in the "Bicarbonate" from the "Foetid Mire" because the Water Report doesn't give any alternatives ("Alkalinity" reported as bicarbonate or CaCO3 - the favoured routes, "KH" or "Carbonate Hardness", "Temporary Hardness", or "Total Hardness" along with Calcium and Magnesium figures): All but "Alkalinity" require fishing about in the "Foetid Mire". I've also added the Report's "Nitrate" to help make the alkalinity figure more accurate.

In building an "Alkalinity" the "Defuddler" is using a "mass balancing" approach. It adds up all the cations it knows about, subtracts all the known anions, and assumes the remainder is bicarbonate. There are things wrong with that assumption, but not that will have a really bad affect on the answer in our situation (beer brewing). The values have to be converted to and fro with "mEq/l". Enough to do in anyone's head which is why I keep it hidden (from me especially).

This mass balancing driven approach is fine for most, but starts to slip for users with low alkalinity waters (or seems to for me which is why I'm currently inspecting it). What the "Defuddler" needs is the reverse approach, an "Alkalinity" driven approach. One where you've got a decent "Alkalinity" value and use it along with mass balancing to determine something like "Calcium". I consider anyone with an "Alkalinity" (as CaCO3) less than 60-100 as "low". You're at approximately 55ppm.

See why I'm taking such an interest in your post now?

I'm just off again. I'll be back!

 

[ARNWD]

Thank you so much for replying.

The numbers are different from those I posted earlier as you appear to be using the year ended 2014 water report, have you mislaid your reading glasses?. No need to change tho - I am just trying to understand how the Defuddler works. The latest water report is January 2022 - December 2022. But thinking about it there was a drought in 2022 which is why the ion imbalance may be higher than normal as I am using "normal times" alkalinity with a higher than normal reported ions.

However, I shall await your further comments before getting ahead of myself.



ARNWD

 

[peebee]

The numbers are different from those I posted earlier as you appear to be using the year ended 2014 water report, have you mislaid your reading glasses?.

You'll know it when I've mislaid my reading glasses, my posts will be at least twice as unintelligible than when I'm wearing them.

Explains why I couldn't find the "2023" report ... there's not one? But ten years out? (I was using the 2014 report!). Forget "have you mislaid your reading glasses?", I think I really do need a checkup again?

Here go. I've still to collect some bits together, but this is where the current "balancing" is going on:

In the Mire as you might expect. The results are coming up from the depths where the mass balancing is calculated. It must be manually entered into "Temporary Hardness" and then again into "KH" above it: The Spreadsheet's policy is not to allow results from deeper in the Mire to entered automatically into shallower ehtry points (that way you get plenty of chance to notice any daft entries).

The "balancing" is between that "calculated balanced value" and the "documented value" with a customerisable error window (here set to plus/minus 5%). Of course there is no error if the values are copied upwards like mentioned above (but don't tell it 0% error or some minor rounding hiccup trips it up).

Higher up the balanced value is presented to put (manually) into the "Bicarbonate" box:

(68.25mg/L as bicarbonate). This is only helping for using "mass balancing" to estimate "alkalinity". If you want to estimate the "mass of something" (Calcium you said you wanted) from the "alkalinity" value you have to do it manually with the "suck-it-and-see" (iteration?) approach (make little changes to "Calcium" until it adds up right).

At which point you definately attain the Spreadsheet's "Advanced Bog Snorkler" category!

I'm working on alterations to allow "Alkalinity" led balancing 'cos I recently got myself one of these:

And I'm rather keen to have it take "priority" in my calculations (it cost a small fortune ... well, £80, plus £30 per annum to keep the reagent up-to-date). The device is reporting "15mg/L as CaCO3 Alkalinity", this pharting about only makes sense for Alkalinities below 100mg/L as CaCO3? I'm hoping to tie in some relationship with "Calcium" amounts 'cos I don't want to buy more reagent! Any tie in is very likely to be "personalised".

 

[peebee]

I'm still working on it!

The reason I splashed out on one of those Hanna Checkers is because of my low Alkalinity water. The mini titration kits (like the Salifert kits) just do not have the resolution. For example: The "KH" - "Alkalinity kit would arrive at 15mg/L (like the Hanna Checker pictured above) by adding one single drop of reagent. What if the Alkalinity was ... say ... 8mg/L, or 19g/L? The Hanna Checker has a resolution of 1mg/L. I don't want a solution that relies on it though (they're expensive!).

The Salifert Calcium kit mentions a "low-resolution" mode. I did attempt a "high-resolution" mode with some success (twice the water, twice the indicator dye twice the pH adjuster ... that bottle of 5% NaOH ... , and halve the result ... ah, and twice the care performing it!). Haven't tried (or figured out) such a trick with the "Alkalinity" kit yet.


Making the "Defuddler" change the way it determines "Alkalinity" or "Calcium" was easy. I'll post an update shortly (there's still a bit of suck-it-and-see or "iteration", but it's easy).


And I need to look at how Graham's calculator handles working with "Alkalinity" centric data rather than "Hardness" centric (pretty easy).

 

[peebee]

One thing I would like to make clear: All this nerdy-geekiness I'm spouting is a sham: The "Defuddler's" inner workings are using a "workman" approach (okay, the jeans and sweatshirt type workman, the "Hardness" stuff is only for the donkey-jacket and steel toe-capped types). There's a lot, lot, more to water chemistry and Alkalinity than I'm using. I'm just skimming a fraction of it, and only in the area of drinking water and beer mashing. I reckon that's more than enough.

If you want to know more, there's the popular book by John Palmer called "Water". It adorns many a homebrewers' bookshelf. Where I believe it stays! I think it's way too detailed, covers stuff well outside a homebrewer's interest, and offers little (the detailed stuff offers nothing?) to improve the beer a homebrewer produces. A good read for a proper nerd though ... I don't think I come close.

 

[peebee]

@ARNWD: I'm gathering some "errors". I'm probably stretching the original idea outside of its boundaries - adjusting "Alkalinity" while assuming other settings stay still. Can't happen. I'll try to recover the situation, but meanwhile be careful of (perhaps that should be "ignore") this "new" balancing stuff!

I couldn't understand why reducing alkalinity was increasing calcium. Well, perhaps I'd accidentally overlooked something? Oooh, I can be proper daft at times. Normality will resume ... might take a few days meditating in a hot desert without food (and beer!).



Maybe some good will come of this exercise ... for me! I was getting despondent that my efforts weren't getting me closer to the answers I wanted. Hardly surprising.

 

[ARNWD]

@ Peebee,

Been busy all week so I havn't had the time to read past post #7 let alone fire up the Defuddle. This just to let you know I will be reviewing your posts soon.

ARNWD.

 

[peebee]

The issue that cropped up is due to the unlikely (imaginary?) connection between "Alkalinity" and "Total Dissolved Solids". I was adding up the major ions (as anions and cations in mEq/L units, but not any "as" bicarbonate), subtracting the anions from the cations, and declaring the difference to be "bicarbonate". Which is reasonable, provided you don't want to change the "Alkalinity" figure.

Most enter values from a single report, have no reason to fiddle with the "Alkalinity" figure, never unknowingly engage the "ion-mass" balancing mechanism, and therefore never see the issue. In this thread I'm intentionally messing with the "Alkalinity", dragging ion values from various sources, and running headlong into the issue. ... The issue: I can't change the value for bicarbonate without making a corresponding change to the cations ("metal" ions). Should put an end to my calcium figure seemingly increasing in value as I reduce the bicarbonate value.

That sort of sorted, I'll get it fixed fairly quickly. It's particularly relevant to low alkalinity (roughly, "soft") water users ... which includes me! So, I should have plenty of motivation to fix it.

 

[peebee]

Updated "Defuddler" partially. Read about it <here> (Jim's Forum). I'll update it properly later with the "advanced", or "Bogsnorkler" mode. At the moment it's only downgraded to disallow unsupported "tricks" (it wasn't intended to do clever stuff, and it can't, so needs another tool because it does the job it was made for as it is).

It's created in Excel and I haven't debugged it in anything else (e.g. Google Sheets). Working on it ...

 

[peebee]

Still working on it! I hope to post the new version of "Defuddler" within a week which I hope will meet your (@ARNWD ) needs. Meanwhile: Here's something it won't easily handle (sourced from US of America) ...

The topic matter is well off-subject! The thing to note is six wells, from which they blend (randomly from the end-users perspective no doubt) the city water supply. We hardly know we're born over here.

Unfortunately, there are those over here in UK who insist on copying US trends, even when those trends are born from an entirely different situation!

 

[peebee]

And ... still working on it!

This was a whole lot harder than I first thought! Getting a decent "Alkalinity" reading for sub-100mg/l (as CaCO3) water, that is (the water companies will most likely be dosing such water with lime to get the pH up). The Hanns Alkalinity Checker I was putting so much faith in (if you spend that much for one you expect the best) is now a source of suspicion. The (out-of-date) Salifert test kits (the ones everyone is using) are gaining much greater confidence (for me) in the reliability of the tests. Though I do slightly hack the kits to work at such low Alkalinity:

The sample size is increased ... 32mls (instructions say 4ml, i.e. x8) for the Alkalinity kit, 8ml (instructions say 2ml i.e. x4) for the Calcium kit. Everything else is bumped up the same, but the final result is divided by the same magnitude. Calcium (Salifert kit) came back 20ppm, within the reported 17.45ppm from the water supplier. "Alkalinity" (Salifert) came back with 0.14 mEq/l, the Hanna device returned 0.26 mEq/l. But that translates as 7ppm as CaCO3 (the units most here are used to) and 13ppm as CaCO3, so maybe not so bad? Pheonix Analytical have twice come back with 0.16 mEq/l so you can see why I'm losing faith with Hanna. I can't understand anyone wanting to use 100% RO water, which will probably have no reliable Alkalinity test anyway (it will be about the same levels as I'm struggling with) and for the brewer will most likely put down as zero anyway. I'm being finnicky because my mash pHs for light coloured beers have been coming out too low for a while now (<5.0 pH).

BTW: Don't use those tables in the Salifert kit instructions. You just end up doing more work and increasing the chance of errors. Watch out for Calcium kit with only two bottles (missing the 2% NaOH) ... they won't work properly with Calcium levels < 300ppm (that's high!). They publish the formulas to use instead of tables:
Alkalinity ... (1 - reading off the syringe) x 5.71 mEq/l ... x16 if you want result as dKH
Calcium ... (1 - reading off the syringe) x 500 ppm

Anyway, enough of them woes, the "Defuddler" is supposed to be ironing out those issues. But I'm altering it from an entirely "ppm" from reports led input to a self-balancing, automatically calculated, "Alkalinity" led tool. But I won't let go of the "ppm" lead bits. I'm getting tied in knots with it. The other headache is "adapting" the "Alkalinity" while not properly understanding how the chemistry retains "balance".

But it'll come right eventually.


[EDIT: "Led" for "Lead". Always doing that, don't know what I've got about "lead"?]

 

[peebee]

I've been working hard on my "water mineral" pre-processor ... the "Defuddler" (hey ... I can hear the groaning from here you know). Expanding it, making it simpler, making it more accurate, etc. I've been doing much of the work on the sister site to this one "HomeBrewTalk.com" 'cos there's more of them (it's an American site, they're a bit more "critical" too, so I learn more about what I'm doing) and they have some "difficult" waters, often very "Hard" and also with a lot of Sodium which may be due to the ion exchange plants they operate out there.

So, this is a quick catch up. It has not outwardly changed much...

But it now relies exclusively on "calculated" values for "Alkalinity". The published figures would never be significantly different.

The "engine" that drives it is very simple, and I'm still trying to figure out why it's not so prominent in home-brewing. It is based on the formula:

Total Alkalinity = ∑conservative-cations - ∑conservative-anions

And you can read about (in mind-numbing detail) here in Wikipedia. It might be all the talk about "conservative" ions, combined electrical charge balancing and proton balancing, and so-on. Don't know about anyone else, but it used to scare the pants off me. It's all jargon, you only need to know a snatch, and it's all so simple. Anyway, a diagram should illustrate what the "Defuddler" is handling:

The "Conservative Anions" plus the "Alkalinity Anions" will equal the "Conservative Cations". The ions will need to be converted to "equivalents" (such as milli-Equivalents per Litre - mEq/l) to make this easy (all done by the sreadsheet, so notthing to learn). There will be numerous other ions in treated drinking water, but the main players are listed in the diagram (quantities less than 1mEq/l can be safely ignored). "Nitrate" at about 1-6mg/l is probably the smallest ion quantity to be counted. Potassium may make an appearance but is of no great importance and lost amongst the sodium

This diagram illustrates how the calculations can do what they do ... make very tight estimates of Alkalinity. Which in this environment will be "Carbonate" Alkalinity (hence represented "as" bicarbonate ions).

Now this diagram might be parallelled by a "Hardness" version. I must make it clear so this following diagram doesn't trigger any mis-comprehensions comparing it with the "Alkalinity" version: IT IS NOT CORRECT!!!

This suggests "Temporary Hardness" is the same as Alkalinity. IT IS NOT! But you often see them quoted (incorrectly!) with the same concentrations in water reports. What is obviously missing is the Sodium (and Magnesium but that can be roughly extracted because it is in there). Hardness has no concept of "Sodium" and assuming "Temporary Hardness" is the same as "Alkalinity" can cause problems, especially when the water harbours sizeable quantities of Sodium (I always used to think of them as the same too). But if careful we can extract useful information from "Hardness". As will be seen!


[EDIT: Changes to diagrams to make them clearer.]

 

[dad_of_jon]

"Temporary hardness" relates directly to the strength in Mg of Viagra taken vs units of alcohol drunk.

your welcome!

 

[peebee]

Damn, I'm missing that. I'll see if I can shoehorn it into the spreadsheet ...

Have you got the chemical element "tag" for this "Viagra"? "Vi" is it? I can't seem to find it.

 

[MashBag]

"Temporary hardness" relates directly to the strength in Mg of Viagra taken vs units of alcohol drunk.

your welcome!

 

[peebee]

Now this diagram might be parallelled by a "Hardness" version. I must make it clear so this following diagram doesn't trigger any mis-comprehensions comparing it with the "Alkalinity" version: IT IS NOT CORRECT!!!

Why'd I say "... NOT CORRECT"? I should explain that a bit more ... an excuse another "pretty piccie". I like pretty piccies!

"Hardness" comes from a time when there was no "Calcium", no "Sodium", etc. ... well, not an appreciation it actually exists. About the beginning of the 19th C. those two, Calcium and Sodium, were discovered (thanks to "Sir Humphry Davy" apparently ... Wot? No "e" in "Humphry"?). All fire, air, earth and water before those times. In catching up with the times "Hardness" became all the "multivalent" cations, which are what's responsible for "Hard" water making lousy soap-suds. No "Carbonate" and "Temporary Hardness"? That was got around by noticing "Hardness" metal ions paired with "Carbonate" (actually, "Bicarbonate" mainly) and when the water was boiled the "Carbonate" became insoluble, and dragged the Hardness metals down with it.

Meanwhile science built an understanding of "Alkalinity". Inconveniently (for some?) this new subject overlapped "Hardness": "Temporary Hardness" was the same as "Carbonate Alkalinity" and in water you can generally drink happily virtually all the Alkalinity is due to "Carbonate" (and "Bicarbonate"!) anions.

This all means: Some Alkalinity isn't due to "carbonate", and some (sometimes a significant amount) of "carbonate" could be associated with non-Hardness cations, like "Sodium" and "Potassium". And the result: Plenty of ongoing confusion. But it won't go away while we continue to think this "Hardness" business is important. ... It isn't! It is "important" to some; all that insoluble carbonate stuff clogs the pipes, pumps, boilers, etc. Soluble carbonates ... who cares? Well, brewers for one!


A problem this situation creates: It gives me a subject to clatter on about and bore everyone senseless ... (no, no, no, that's not it? Here go:) Some methods of analysis may not return results specifically for "Hardness" and "Alkalinity". But we've got used to that and allow it to be "covered up" if not too important. So a typical acid titration for "carbonate" for example, doesn't tell you if the carbonate would associate with "Hardness" cations or "non-Hardness" cations, like Sodium. So we get a situation of amateur homebrewers attempting to analyse their methods in tighter and tighter detail, and in fact get further and further from reality.

That's the area I'm trying to redress.