Rebel Brewer Giveaway!
 Home Brew Forums > Tinseth formula for OpenOffice help?

05-20-2010, 12:42 AM   #1
jasonsargo
Feedback Score: 0 reviews
Recipes

Join Date: Dec 2009
Location: Denver
Posts: 60
Liked 1 Times on 1 Posts

 Tinseth formula for OpenOffice help?

I can't find this answer with a search, and the thread I found just did not give me the exact answer I need...

I am using OpenOffice to piece together a basic spreadsheet to track my brews. I cannot, for the life of me, input the Tinseth formula for hop utilization into a field, even though the functions look the same as Excel. I have re-typed, tried symbols, rearranged parentheses, etc, and still spit up an error. I am at the limit of my understanding of math and Excel/OpenOffice.

Could someone just post a formula to cut and paste into a field for me?

By the way, here is my best effort:

=(1.65*0.000125exp(AVERAGE(H1;H3)))*((1-(2.72exp(-0.04*E24)))/4.14)

where the average of H1 and H3 are my initial and final boil gravities without the one and decimal, and E24 is my boil time field. I have tried just directly putting in 66 for my boil average, and 60 for the time. And the exp is actually displayed as ^ in my fields, but it came out exp when I copied it here.

Any help would be greatly appreciated.

__________________

In Primary: all-grain ESB
In Secondary: 2011 Szechuan and Black Peppercorn Mead
Bottled: 2010 Clover Mead aging for 1,3,5,10 and 20 years
-----------
25+ batches 10+ years ago and back into it with 10G all-grain beers and annual meads. Equipment: DIY cooler mash tun with slotted copper, DIY keggle and keg-HLT over propane, DIY counterflow chiller, Mini-Brew conical fermenter, 4 Party Pigs. 1999 AHA Nationals- 1st Place Herb and Spice Mead "Szechuan Peppercorn Mead".

05-20-2010, 03:34 AM   #2
sicabeer
Feedback Score: 0 reviews
Recipes

Join Date: Mar 2010
Location: New York
Posts: 53
Liked 1 Times on 1 Posts

I just copied pasted your formula into open office.

=(1.65*0.000125exp(AVERAGE(H1;H3)))*((1-(2.72exp(-0.04*E24)))/4.14)

Changing the exp to ^ I do not get an error.

You need values in H1 and H3, or you will get a #DIV/0! error.

__________________

Last edited by sicabeer; 05-20-2010 at 03:39 AM.

05-20-2010, 05:39 AM   #3
jasonsargo
Feedback Score: 0 reviews
Recipes

Join Date: Dec 2009
Location: Denver
Posts: 60
Liked 1 Times on 1 Posts

Thanks so much, sicabeer. The fact that you did not get an error made me dig into it again tonight with some fresh eyes after they glazed over this afternoon. I haven't done much math since high school!

Inserting the "^" in, AGAIN, and not trying to use the function button on the task bar or type in "exp" did the trick, and I didn't get an error. But I did get a 0 result, which didn't make any sense.

I finally realized that my fields for gravity are just the numbers after the decimal, which averaged out to 66, not .066, so I needed to add in the *.001 in the final formula below:

=(1.65*0.000125^(AVERAGE(H1;H3)*0.001))*((1-(2.72^(-0.04*E24)))/4.14)

where AVERAGE(H1;H3) are my initial and final boil gravities, and E24 is my boil time.

My final question to button up my spreadsheet is this: since I averaged the gravities over the course of the boil for the utilization formula, shouldn't I use an average of the volume over the course of the boil for IBU calculation? Some sites don't spell it out, which would imply to just use the initial boil volume, but I'm high and dry in Denver, so I think I lose a lot of volume over the course of a boil.

__________________

In Primary: all-grain ESB
In Secondary: 2011 Szechuan and Black Peppercorn Mead
Bottled: 2010 Clover Mead aging for 1,3,5,10 and 20 years
-----------
25+ batches 10+ years ago and back into it with 10G all-grain beers and annual meads. Equipment: DIY cooler mash tun with slotted copper, DIY keggle and keg-HLT over propane, DIY counterflow chiller, Mini-Brew conical fermenter, 4 Party Pigs. 1999 AHA Nationals- 1st Place Herb and Spice Mead "Szechuan Peppercorn Mead".

05-20-2010, 02:09 PM   #4
jgourd
Feedback Score: 0 reviews
Recipes

Join Date: Feb 2010
Location: Louisiana
Posts: 789
Liked 16 Times on 11 Posts
Likes Given: 2

I also use OO and for each hop addition use the following to estimate the IBUs:

Code:
`=(C23*B23*100)*((1.65*0.000125^(\$B\$4-1))*((1-EXP(-0.04*D23))/4.15))*75/\$H\$27`
where:

C23 is the amount of hops in oz for this addition
B23 is the AA of the hops in % for this addition
B4 is the estimated OG of my brew (after the boil)
D23 is the boil time for that hop addition
H27 is my batch size

The total IBUs is just the sum of each addition's result.

After brewing, I determine the actual IBUs based on that specific session:

Code:
`=(C23*B23*100)*((1.65*0.000125^(\$H\$16-1))*((1-EXP(-0.04*D23))/4.15))*75/(\$D\$8+\$J\$35)`
where:

C23 is the amount of hops in oz for this addition
B23 is the AA of the hops in % for this addition
H16 is the actual OG of my brew (after the boil)
D23 is the boil time for that hop addition
D8 is the actual amount collected in the primary fermenter
J35 is any wort left in the kettle other than pre-calculated losses (essentially extra wort that I couldn't fit into the primary fermenter)

Good luck!
__________________

 Quick Reply Message: Options Quote message in reply?
 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post jescholler Brew Science 5 10-21-2009 01:14 PM MMW Brew Science 4 06-11-2009 09:33 PM mod_critical Brewing Software 0 08-19-2008 07:01 AM LS_Grimmy All Grain & Partial Mash Brewing 4 02-14-2008 12:34 AM BrianP Brewing Software 5 10-02-2007 04:36 PM