Membership questions? Log in issues? Email info@brewersassociation.org

Author Topic: Palmer Spreadsheet Error  (Read 21222 times)

Offline mabrungard

  • I spend way too much time on the AHA forum
  • ********
  • Posts: 2902
  • Water matters!
    • Bru'n Water
Palmer Spreadsheet Error
« on: December 14, 2010, 06:12:55 pm »
After reviewing a water profile created by a brewer using Palmer's Spreadsheet, I found a very serious error that must be corrected.  

The bicarbonate concentration calculated from the addition of chalk is in error.  It appears that the calculation is actually giving the carbonate concentration instead of the bicarbonate concentration.  Since carbonate cannot exist at typical mash pH, it must be converted to the bicarbonate form.  That means that the carbonate concentration should be multiplied by 2.033.  This also means that the alkalinity calculated for the chalk addition in the spreadsheet is also in error and should be multiplied by 2.033.

Another significant error in the spreadsheet is the residual alkalinity that the spreadsheet recommends based on the beer color.  It is far too aggressive and recommends far too high a RA value to the brewer.  There are no water profiles from the historic brewing centers with a RA higher than about 180.  This spreadsheet will frequently point a brewer to recommended RA values of 300 to 400.  That is extremely excessive and leads to soda water beers.  A more appropriate correlation between RA and SRM is as follows:  RA = SRM x 4.5.  And given that correlation, the maximum RA a brewer should ever use is about 200.  

Until the revised spreadsheet is issued, I recommend that brewers refrain from using this program.
« Last Edit: December 15, 2010, 06:24:59 am by mabrungard »
Martin B
Carmel, IN

BJCP National
Foam Blowers of Indiana (FBI)

Brewing Water Information at:
https://www.brunwater.com/

Like Bru'n Water on Facebook
https://www.facebook.com/Brun-Water-464551136933908/?ref=bookmarks

Offline punatic

  • Official Poobah of No Life. (I Got Ban Hammered by Drew)
  • *********
  • Posts: 4583
  • Puna District, Hawaii Island (UTC -10)
Re: Palmer Spreadsheet Error
« Reply #1 on: December 14, 2010, 06:21:17 pm »
There is only one success: to be able to spend your life in your own way.


AHA Life Member #33907

Offline Kaiser

  • Senior Brewmaster
  • ******
  • Posts: 1797
  • Imperial Brewing Geek
    • braukaiser.com
Re: Palmer Spreadsheet Error
« Reply #2 on: December 14, 2010, 08:02:36 pm »
The bicarbonate concentration calculated from the addition of chalk is in error.  It appears that the calculation is actually giving the carbonate concentration instead of the bicarbonate concentration.  Since carbonate cannot exist at typical mash pH, it must be converted to the bicarbonate form.  That means that the carbonate concentration should be multiplied by 2.033.  This also means that the alkalinity calculated in the spreadsheet is also in error and should be multiplied by 2.033.

Martin,

You seem to be stumbling onto a lot of things that I have stumbled upon as well, which I like. I noticed the same when I calculated the ion balance of the water profiles created with that spreadsheet. However, I set out to test this in a mash pH experiment and found that while the spreadsheet seems to be incorrect the predicted pH change is correct. Eventually, after many more experiments, I came to the conclusion that undissolved chalk has an inexplicably odd behavior when it comes to adjusting mash pH.

Check out these links:
* How much alkalinity does 1 ppm of CaCO3 (Chalk) really add?
* Undissolved vs. dissolved chalk in the brewing water
* The effect of brewing water and grist composition on the pH of the mash

You may also want to give my water spreadsheet a try: http://braukaiser.com/documents/Kaiser_water_calculator.xls It handles undissolved chalk similar to what Palmer does but it also supports dissolving chalk.

Quote
Another significant error in the spreadsheet is the residual alkalinity that the spreadsheet recommends based on the beer color.  It is far too aggressive and recommends far too high a RA value to the brewer.  There are no water profiles from the historic brewing centers with a RA higher than about 180.  This spreadsheet will frequently point a brewer to recommended RA values of 300 to 400.  That is extremely excessive and leads to soda water beers.  A more appropriate correlation between RA and SRM is as follows:  RA = SRM x 4.5.  And given that correlation, the maximum RA a brewer should ever use is about 200.  

I do agree. For some reason John found that roasted malts are more acidic per unit of color than crystal malts. I found that the opposite is true and as a result you need  far less alkalinity in dark beers than previously thought. Unfortunately John did not publish how he arrived at his SRM->RA formula.

Kai
« Last Edit: December 14, 2010, 08:11:17 pm by Kaiser »

Offline tschmidlin

  • I must live here
  • **********
  • Posts: 8198
  • Redmond, WA
Re: Palmer Spreadsheet Error
« Reply #3 on: December 15, 2010, 12:13:31 am »
I love this forum :)
Tom Schmidlin

Offline James Lorden

  • Brewer
  • ****
  • Posts: 337
  • Forest Hill, MD
Re: Palmer Spreadsheet Error
« Reply #4 on: December 15, 2010, 08:34:56 am »
James Lorden
Beer Drinker Beer Maker & Beer Judge

Offline denny

  • Administrator
  • Retired with too much time on my hands
  • *****
  • Posts: 27093
  • Noti OR [1991.4, 287.6deg] AR
    • Dennybrew
Re: Palmer Spreadsheet Error
« Reply #5 on: December 15, 2010, 08:53:11 am »
Martin or Kai, have you contacted John with your concerns?
Life begins at 60.....1.060, that is!

www.dennybrew.com

The best, sharpest, funniest, weirdest and most knowledgable minds in home brewing contribute on the AHA forum. - Alewyfe

"The whole problem with the world is that fools and fanatics are always so certain of themselves, and wiser people so full of doubts." - Bertrand Russell

Offline Kaiser

  • Senior Brewmaster
  • ******
  • Posts: 1797
  • Imperial Brewing Geek
    • braukaiser.com
Re: Palmer Spreadsheet Error
« Reply #6 on: December 15, 2010, 09:20:22 am »
Martin or Kai, have you contacted John with your concerns?

I had. But at the time he didn't show much interest or had time to spend on this. He also didn't show much interest when I mentioned this to him at the conference.

I don't really need his input on this. Most of John's work is based on A.J. deLange's stuff anyway and I had e-mail conversations with A.J. about this. He can't explain this observation either.

I think we need to get more brewers like Martin to question why the addition of 100 ppm CaCO3 doesn't raise the water's alkalinity (not residual alkalinity) by 100 ppm as CaCO3. Maye more brewers will adopt the spreadsheet I have. With some more data about undissolved chalk and its effects on the mash pH I could even put in a curve that determined the "apparent" RA increase that comes from undissolved chalk. This would make the relation between chalk and pH more apparent.

Kai

Offline denny

  • Administrator
  • Retired with too much time on my hands
  • *****
  • Posts: 27093
  • Noti OR [1991.4, 287.6deg] AR
    • Dennybrew
Re: Palmer Spreadsheet Error
« Reply #7 on: December 15, 2010, 09:26:18 am »
It's too bad that John didn't have time to deal with this.  Many brewers use his spreadsheet and accept is as "gospel".  In that case, it needs to be accurate.  I'm glad we have people like Martin and you questioning things and trying to explain them.
Life begins at 60.....1.060, that is!

www.dennybrew.com

The best, sharpest, funniest, weirdest and most knowledgable minds in home brewing contribute on the AHA forum. - Alewyfe

"The whole problem with the world is that fools and fanatics are always so certain of themselves, and wiser people so full of doubts." - Bertrand Russell

Offline Kaiser

  • Senior Brewmaster
  • ******
  • Posts: 1797
  • Imperial Brewing Geek
    • braukaiser.com
Re: Palmer Spreadsheet Error
« Reply #8 on: December 15, 2010, 09:57:46 am »
I started pushing my water spreadsheet before I went on my brewing hiatus and haven't picked up that effort. After all the question is not so much the RA that is calculated by the spreadsheet but it is "can you brew better beer with a more accurate spreadsheet". John does recommend to limit the RA to 250 ppm and there is also a lot of leeway when it comes to the correct RA for a given beer. In addition to that he also doesn't account for mash thickness.

Maybe Martin and other brewer's interest motivates me to write the next version of my water spreadsheet which will take more grist information into account. I do have a lot of the data necessary for that. Ideally this work should be incorporated into a commonly used tool like BeerSmith.

I also think that you can add all your salts to the mash and don't add any to the sparge water.

Kai

Offline richardt

  • Senior Brewmaster
  • ******
  • Posts: 1227
Re: Palmer Spreadsheet Error
« Reply #9 on: December 15, 2010, 10:28:54 am »
I started pushing my water spreadsheet before I went on my brewing hiatus and haven't picked up that effort. After all the question is not so much the RA that is calculated by the spreadsheet but it is "can you brew better beer with a more accurate spreadsheet". John does recommend to limit the RA to 250 ppm and there is also a lot of leeway when it comes to the correct RA for a given beer. In addition to that he also doesn't account for mash thickness.Maybe Martin and other brewer's interest motivates me to write the next version of my water spreadsheet which will take more grist information into account. I do have a lot of the data necessary for that. Ideally this work should be incorporated into a commonly used tool like BeerSmith.
I also think that you can add all your salts to the mash and don't add any to the sparge water.
Kai
THIS! 

I'd love to have a Grand Unified Theory of Brew Salt Additions hammered out that takes mash thickness, temperature, grist composition and accurately predicts pH, RA, and so forth.  I'd love to see it incorporated into BeerSmith along with better instructions for data entry as well as for brewing (e.g., Kai's recommendation to add all the salts directly to the mash, not the HLT or the Sparge Water).

Martin, thank you for looking into it, as well.  Everytime you post something about water chemistry, believe me, I pay attention. Your expertise is evident--it takes me a few reads and re-reads to "get it.")  Thanks for your input.  Perhaps a collaboration with you, Kai, and A.J. and any others who are interested (e.g., those who design and run BeerSmith) is in order.

Offline James Lorden

  • Brewer
  • ****
  • Posts: 337
  • Forest Hill, MD
Re: Palmer Spreadsheet Error
« Reply #10 on: December 15, 2010, 10:49:57 am »
Martin, thank you for looking into it, as well.  Everytime you post something about water chemistry, believe me, I pay attention. Your expertise is evident--it takes me a few reads and re-reads to "get it.") 

Couldn't agree more.  I also feel like it puts a little pressure on us that "sort of get it" to ask intelligent questions so that the information can trickle down through us to those who "don't get it".

One of the great things I have noticed about many of the complicated topics lately is that they have been working down from the complicated theory, to the simplified synopsus, and finally to must have take aways as the conversation evolves.  There's a little something for everyone....

Keep up the good work everybody!
James Lorden
Beer Drinker Beer Maker & Beer Judge

Offline Kaiser

  • Senior Brewmaster
  • ******
  • Posts: 1797
  • Imperial Brewing Geek
    • braukaiser.com
Re: Palmer Spreadsheet Error
« Reply #11 on: December 15, 2010, 11:43:36 am »
Being able to predict mash pH, or just about anything that could be predicted in brewing, is very much a home brewer thing. There seems to be little data available from the commercial brewing worlds and our understanding of mash pH is largely based on research done before WWII.

Over the time I have come to the conclusion that we may want to change the way mash pH adjustment is taught to home brewers. Let’s even forget about water profiles for a moment and see the mash pH problem as changing the pH of a pH buffer. The buffer is malt and its has a natural pH of ~5.75 in distilled water. What raises the pH are:

* bicarbonate/carbonates
* hydroxide (bases)

What lowers the pH are

* mailard products in the malt
* acids
* Ca and Mg

You quantify the amount of all of these substances, calculate the balance  and use that to predict the pH change of the mash. There will be inaccuracies that come from the fact that malt is not a simple buffer substance but that it is a mix of pH buffering substances.

The notion of the water’s residual alkalinity is useful to estimate how suitable a given water is for a given type of beer but I don’t think that we should use it to actually predict mash pH.

I have done a number of experiments already and am currently working on experiments where I add known amounts of salts and acids to small mash sampled to see how their pH reacts. And again, chalk was the odd one. In some of the experiments the mash pH didn’t change at all after I added chalk.

Kai


Offline denny

  • Administrator
  • Retired with too much time on my hands
  • *****
  • Posts: 27093
  • Noti OR [1991.4, 287.6deg] AR
    • Dennybrew
Re: Palmer Spreadsheet Error
« Reply #12 on: December 15, 2010, 11:55:41 am »

I also think that you can add all your salts to the mash and don't add any to the sparge water.

That's exactly what I do.
Life begins at 60.....1.060, that is!

www.dennybrew.com

The best, sharpest, funniest, weirdest and most knowledgable minds in home brewing contribute on the AHA forum. - Alewyfe

"The whole problem with the world is that fools and fanatics are always so certain of themselves, and wiser people so full of doubts." - Bertrand Russell

Offline tschmidlin

  • I must live here
  • **********
  • Posts: 8198
  • Redmond, WA
Re: Palmer Spreadsheet Error
« Reply #13 on: December 15, 2010, 11:56:42 am »

I also think that you can add all your salts to the mash and don't add any to the sparge water.

That's exactly what I do.
Same here. :)  Glad to have it validated by a water expert.
Tom Schmidlin

Offline Kaiser

  • Senior Brewmaster
  • ******
  • Posts: 1797
  • Imperial Brewing Geek
    • braukaiser.com
Re: Palmer Spreadsheet Error
« Reply #14 on: December 15, 2010, 12:01:47 pm »
Same here. :)  Glad to have it validated by a water expert.

If you are using R/O or other soft water all that is going to happen is that your pre-boil pH is going to be identical or closer to your mash pH since the sparge water is not bringing in any additional minerals that could change the pH. It only dilutes the solution which tends to have little effect in well buffered systems.

Having the pre-boil pH close to the mash pH isn’t a bad thing. They both have about the same optimal pH range.

Kai