Refractometer Correction Spreadsheet

Discussion in 'General Brewing Discussions' started by Nosybear, Oct 2, 2017.

  1. Nosybear

    Nosybear Well-Known Member

    Joined:
    Jul 16, 2012
    Messages:
    9,399
    Likes Received:
    6,636
    Trophy Points:
    113
    Location:
    Aurora, CO, USA
    Until the programmers can implement refractometer-based fermentation tracking, I'm adding a spreadsheet I developed to measure and track gravity using the refractometer during fermentation. You enter the reading in SG, it converts it to Brix, does the refractometer conversion then gives the value in SG. It also graphs the fermentation. Dang it, the file won't let me upload an xls! Any help from the admins?
     
  2. Ozarks Mountain Brew

    Staff Member

    Joined:
    Nov 20, 2012
    Messages:
    7,767
    Likes Received:
    3,976
    Trophy Points:
    113
    Gender:
    Male
    Occupation:
    IT Managment
    Location:
    The Ozark Mountains of Missouri
    if you have office 2016 you can export to a pdf file, even if you don't you can download an app that will
     
  3. The Brew Mentor

    The Brew Mentor Well-Known Member

    Joined:
    Aug 19, 2012
    Messages:
    961
    Likes Received:
    578
    Trophy Points:
    93
    Gender:
    Male
    Location:
    Mentor, Ohio
    Looking forward to it.
    Have you done actual side by side comps?
     
  4. J A

    J A Well-Known Member

    Joined:
    Jan 19, 2016
    Messages:
    3,475
    Likes Received:
    2,689
    Trophy Points:
    113
    Location:
    Austin, Texas
    I got a refractometer specifically so I could track things on the pre-boil side but found it to be such a pain in the butt that I don't want to use it and have reverted to ignoring pre-boil and tracking efficiency, etc. by adjusting numbers to match the more reliable post-boil gravity readings.
    It would be nice to have an easy way to use the refractometer and be able to trust the readings.;)
     
  5. Nosybear

    Nosybear Well-Known Member

    Joined:
    Jul 16, 2012
    Messages:
    9,399
    Likes Received:
    6,636
    Trophy Points:
    113
    Location:
    Aurora, CO, USA
    It won't work as a .pdf, the formulas are eliminated. What I could do is to rename it to an "accepted" format and upload it with the instructions to rename it to Excel.
    Yes, with finished gravity, it is accurate to 1 point (.001) SG. That's better accuracy than I can measure with my normal hydrometer, as accurate as I can get with my finishing hydrometer.
    And yes, it is! No more guessing when to start diacetyl rests, no more guessing when things are done or when to add late syrups.... And the sample sizes are tiny! I filter out the trub from a couple drops of beer, measure, and don't even notice the losses over the entire batch. I've chatted back and forth with Yooper and she says it's an interesting addition to BF, no indication of when....
     
    Trialben likes this.
  6. Nosybear

    Nosybear Well-Known Member

    Joined:
    Jul 16, 2012
    Messages:
    9,399
    Likes Received:
    6,636
    Trophy Points:
    113
    Location:
    Aurora, CO, USA
    Just for reference, the refractometer is accurate pre-boil. It's when fermentation starts that the readings become distorted because of alcohol's higher refractive index.
     
  7. BoomerBrian

    BoomerBrian Active Member

    Joined:
    Jun 27, 2016
    Messages:
    371
    Likes Received:
    216
    Trophy Points:
    43
    Gender:
    Male
    Occupation:
    Software Developer
    Location:
    Oklahoma
    Sweet. I look forward to checking this out. Thanks for sharing.
     
  8. J A

    J A Well-Known Member

    Joined:
    Jan 19, 2016
    Messages:
    3,475
    Likes Received:
    2,689
    Trophy Points:
    113
    Location:
    Austin, Texas
    I don't find that to be the case. The last brew I tested with the refractometer read 1.045 pre-boil with all the runnings in the pot - 7.5 gallons. Dilution/Boil-off calculator shows that my post boil volume of 5.375 should be 1.063. It was 1.049. By using the efficiency percentage that makes the OG match the reading (86%), the calculator says that the pre-boil gravity should be 1.038
     
  9. Nosybear

    Nosybear Well-Known Member

    Joined:
    Jul 16, 2012
    Messages:
    9,399
    Likes Received:
    6,636
    Trophy Points:
    113
    Location:
    Aurora, CO, USA
    I'd check the refractometer - mine always agrees with the hydrometer. Given the small sample size, you may have had a drop or two of water in your dropper or whatever you used to transfer the sample to the refractometer's stage. You're right, your readings don't make sense. I'm not sure why - my readings agree initially and using the correction, throughout fermentation.
     
  10. BoomerBrian

    BoomerBrian Active Member

    Joined:
    Jun 27, 2016
    Messages:
    371
    Likes Received:
    216
    Trophy Points:
    43
    Gender:
    Male
    Occupation:
    Software Developer
    Location:
    Oklahoma
    Make sure you test your refactometer with distilled water to make sure it reads 0
     
    J A likes this.
  11. J A

    J A Well-Known Member

    Joined:
    Jan 19, 2016
    Messages:
    3,475
    Likes Received:
    2,689
    Trophy Points:
    113
    Location:
    Austin, Texas
    Did...I'll try mixing some sugar water to similar gravity and see what it does. Wort color and particulate can throw it off, so by using a wort correction factor (still not certain how to nail that down) I can get to a proper reading. I may be able to adjust to it one way or another.
     
  12. Nosybear

    Nosybear Well-Known Member

    Joined:
    Jul 16, 2012
    Messages:
    9,399
    Likes Received:
    6,636
    Trophy Points:
    113
    Location:
    Aurora, CO, USA
    The particulates give you a "fuzzy" line, make it harder to read. I filter my samples through coffee filter material to get the worst of the particles out of it. As far as the wort correction factor goes, if your refractometer reads in SG, you won't need it - it's already been applied. If you read Brix or Plato, you will need the correction factor, generally about 1.04.
     
  13. Nosybear

    Nosybear Well-Known Member

    Joined:
    Jul 16, 2012
    Messages:
    9,399
    Likes Received:
    6,636
    Trophy Points:
    113
    Location:
    Aurora, CO, USA
    J A likes this.
  14. oliver

    oliver Well-Known Member

    Joined:
    Sep 21, 2015
    Messages:
    959
    Likes Received:
    371
    Trophy Points:
    63
    Occupation:
    LHBS owner
    Location:
    NOLA
    this sounds like a nice tool to have.

    can you upload to google docs and send a link?
     
  15. BoomerBrian

    BoomerBrian Active Member

    Joined:
    Jun 27, 2016
    Messages:
    371
    Likes Received:
    216
    Trophy Points:
    43
    Gender:
    Male
    Occupation:
    Software Developer
    Location:
    Oklahoma
    Interesting. I did not know this. If you have both is there an advantage to reading brix?
     
  16. Nosybear

    Nosybear Well-Known Member

    Joined:
    Jul 16, 2012
    Messages:
    9,399
    Likes Received:
    6,636
    Trophy Points:
    113
    Location:
    Aurora, CO, USA
    Not with my spreadsheet. Brix/Plato refer to the percentage of dissolved sugars using sucrose as a basis. In the initial formulas for converting refractometer readings into useful SG's, I had to convert to Brix but there's no real advantage.

    I've had some trouble uploading the spreadsheet but I've sent it off to OMB - he thinks he can get it posted here.
     
  17. Ozarks Mountain Brew

    Staff Member

    Joined:
    Nov 20, 2012
    Messages:
    7,767
    Likes Received:
    3,976
    Trophy Points:
    113
    Gender:
    Male
    Occupation:
    IT Managment
    Location:
    The Ozark Mountains of Missouri
    Beer Refractometer Correction Fermentation Tracker.xlsx Download
     

    Attached Files:

  18. J A

    J A Well-Known Member

    Joined:
    Jan 19, 2016
    Messages:
    3,475
    Likes Received:
    2,689
    Trophy Points:
    113
    Location:
    Austin, Texas
    I've checked out that page...it's useful info. Thanks. Even when I've used a hydrometer, I've consistently gotten higher pre-boil readings than what would make sense. I've tried a lot of things, but initial readings (in real time when it's helpful information to have) are always wonky. I may be missing something, but I manage to make good beer despite not having access to accurate pre-boil readings so I don't stress too much about it. ;)
     
  19. Gledison

    Gledison Active Member

    Joined:
    Jun 8, 2017
    Messages:
    149
    Likes Received:
    48
    Trophy Points:
    28
    Great Tool.
    my refractometer measures Brix directly. can i just typ in the Brix values?
     
  20. Nosybear

    Nosybear Well-Known Member

    Joined:
    Jul 16, 2012
    Messages:
    9,399
    Likes Received:
    6,636
    Trophy Points:
    113
    Location:
    Aurora, CO, USA
    I didn't set it up that way - but you can modify it to take out the Brix conversion in column E and enter the data directly there. You'll have to know your refractometer's wort conversion factor - measure a few worts with both a refractometer and a hydrometer before pitching, the ratio is the correction factor.
     

Share This Page

arrow_white