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.

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.

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.

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


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:


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!