Linear refractometer correction in google sheets

[davou]

Has anyone had any luck in using the formulas found on this page in their spreadsheets for brew logs?

http://seanterrill.com/2010/07/20/toward-a-better-refractometer-correlation/

I can't for the life of me get them to work; and I've been trying for hours.

 

[itsme6582]

I don't know about the spreadsheet but here's another option. This is from before Northern Brewer sold out.

http://www.northernbrewer.com/learn/resources/refractometer-calculator/

 

[davou]

yeah, I was previously using a calculator, but I'd rather not have to go switching around during brew days. If I can build the formula myself, then I can just have my brew log open and not have to switch around between things while im trying to note stuff down

 

[jerbrew]

Honestly, this is why a hydrometer is best. But you'll have to take notes on your specific beers in order to do this yourself. Each recipe is going to have a different amount of alcohol and residual sugar even if you start with the same OG. You can develop a regression that works for each recipe you have but you'll need to have a comparison value from your hydrometer. This is probably why it's not working for you. There is going to be a base offset different from the default before you can try to fit that regression. And that will likely need adjusting.

 

[davou]

agree to disagree for which is better; the hydrometer requires a much larger sample to be accurate (at least two pulls from my thief).

The regression analasys was already done actually, by the mathematician at the link I provided. The formulas he ended up with are;

G = 1.0929176 – 0.0956887RIi + 0.160699RIf + 0.0103753RIi² – 0.00449931RIf² + 0.000585957RIi³ – 0.00911434RIf³ – 0.0165360RIiRIf – 0.00538394RIi²RIf + 0.0128988RIiRIf²

for cubic and;

FG = 1.00358522 – 0.00123861*RIi + 0.00380186*RIf

for direct linear.

I was able to get the formulas working in google sheets finally, I think the issue was with how I had expected the program to treat order of operations.

 

[itsme6582]

What did you have that the order of operations was unexpected?

 

[davou]

I had used a bunch of brackets to try and group elements together when it turned out to be not required. Stripping them out and simplifying the formulas worked. To be fair, I might have added one too many brackets or something too. Sheets just spits out an error, but does not tell me where it was.

I got the formulas working now, and can track by brews by brix for initial and final.

 

[Thorrak]

Just in case anyone comes across this thread and is attempting to replicate, here is the formula I used to do this:
=1.0111958-(0.00813003*B4)+(0.01444032*E4)+(0.000523555*B4^2)-(0.00166862*E4^2)-(0.0000125754*B4^3)+(0.0000812663*E4^3)

...where B4 is the original refractometer measurement in brix, and E4 is the final refractometer measurement in brix.

 

[davou]

1.0111958 is water_density times your wort correction factor?

 

[Thorrak]

No unfortunately - I just took the formula as provided in the link you pasted above. No wort correction factor was added in this case.