Kit And Extract Beer Spreadsheet

[SnakeRider]

Just checked hydrometer with tap water. reads 0.098 tap water may be about 27C though

 

[damoninja]

Did you give it a really good stir / mix through? Maybe you had a more concentrated bit at the bottom?

 

[SnakeRider]

Sure did, it was fully disolved, and my readings were after I purged 1/2 cup to clear any sterile water trapped in the tap.

From the comments it seems that my reading process is the problem.... thats 2 readings that are abnormally high :-/

Not sure what I did.... try and try again

 

[davedoran]

Does seem odd.
Don't think you can be making a mess of the reading. Bob it in the test tube and read where the liquid level crosses the scale.

Does the beer taste ok?
This being the case give it a day or two and take another reading. Hopefully it will have settled to a normal level by then.

 

[SnakeRider]

Tasted really good just before I pitched. I was confident this will be my best brew yet.

When she stops bubbling so consistently I will pull another reading.

Cheers!

 

[beerbear]

awesome work on this Im pullin some awesome brews with the help of this bad boy!!!!! :beerbang:

 

[DU99]

Ianh has done the hard yards and the sheet is a credit to him has been a guidance to alot of brewers starting up.

 

[pat86]

Hey IanH (or others) - I'm actually looking at the BIAB spreadsheet but the question should remain the same and this thread gets a lot more attention.

I am building a recipe that is smack bang for ABV - even on the high end for the style, but the OG and FG are coming up LOW. I don't want to reduce the volume (bringing the OG & FG into the green) because I will end up with a beer >6% ABV and I am trying to make a session beer.

So my question is: what does it mean/ matter to me and my beer if the spreadsheet is telling me I am going to have a lower OG and FG for the style? The ABV is correct, so am I missing something here?

Cheers,

 

[damoninja]

So my question is: what does it mean/ matter to me and my beer if the spreadsheet is telling me I am going to have a lower OG and FG for the style? The ABV is correct, so am I missing something here?

Ultimately, nothing other than it not meeting the "average guidelines" for the beer style.

What's the recipe / style?

 

[carniebrew]

I agree, all that is telling you is that Ian has plugged the AABC styles into the spreadsheet for you to use as a guide when creating recipes. No harm at all if the beer you're planning is not exactly to style....as long as you've checked to make sure it looks right, then go for it.

 

[pat86]

OK I hoped that was the case, but wanted to make sure that it wasn't going to be something along the lines of balance problems or bad efficiency, etc.

Style is an Alt:
20 L
3.8kg Munich 1
0.15 kg CaraMunich 1
0.05 kg Carafa 2
Spalt 40g @60
Spalt 26g @30

IBU 36.6
OG 1044 (Low)
FG 1008 (Low)
5.1% ABV Bottled

The sugar isn't out by much and in the scheme of things I'm sure I will have bigger problems on my first attempt anyway!

 

[damoninja]

OK I hoped that was the case, but wanted to make sure that it wasn't going to be something along the lines of balance problems or bad efficiency, etc.

Style is an Alt:
20 L
3.8kg Munich 1
0.15 kg CaraMunich 1
0.05 kg Carafa 2
Spalt 40g @60
Spalt 26g @30

IBU 36.6
OG 1044 (Low)
FG 1008 (Low)
5.1% ABV Bottled

The sugar isn't out by much and in the scheme of things I'm sure I will have bigger problems on my first attempt anyway!

Are you using the kit / extract sheet or the all grain / BIAB sheet?

And I'm sure if you're paying close attention to such details, you'll be fine with your first attempts!

 

[pat86]

Yeah this is in the BIAB spreadsheet, would be my first AG though and cheers for the help!

 

[Arch82]

First of all, Ian you're a legend!

When i first saw the spreadsheet i thought "WOW someone likes beer and excel way too much!", now i don't know what I'd do without it, so thank you!

Apologies if this has been covered, i did a quick search and couldn't find the answer.

Does anyone know why different "beer styles" raise and lower the HCF and in turn my recipe's IBU?
It obviously only happen's when HCF is selected and makes quite a big difference in some cases.

Is this a glitch? If not, should i just select a "well balanced" style to get a true indication if the recipe i've put together is Malty, Balanced or Hoppy...

 

[Arch82]

I'm pretty useless with excel, but just noticed K5 is a part of the HCF formula. K5 is the IBU mid value for each beer style, so that's why the HCF and recipe IBU is fluctuating.

Just need someone to explain why... i'm sure it's very obvious and i will look silly.

 

[Rod]

Had a few queries lately re the FG values and also steeping grain efficiency. So please find version 4.1

Kit & Extract Beer Designer V4.1.xls

Made a correction to the FG formula when using LME and DME.

Changed the formula for calculating the OG when using specialty grains. Now uses an adjustable efficiency factor located on the Grains worksheet.

Cheers
Ian

can I assume this will work with open Office

 

[carniebrew]

Apologies if this has been covered, i did a quick search and couldn't find the answer.

Does anyone know why different "beer styles" raise and lower the HCF and in turn my recipe's IBU?
It obviously only happen's when HCF is selected and makes quite a big difference in some cases.

Is this a glitch? If not, should i just select a "well balanced" style to get a true indication if the recipe i've put together is Malty, Balanced or Hoppy...

The reason for why the formula uses K5 is because Garetz' "Hop Concentration Factor" formula uses the "Desired IBU" in its calculations. Here's Garetz' formula, which Ian has worked into his spreadsheet:

HCF = 1+ ((Final Vol / Boil Vol) * Desired IBU) / 260

Ian is treating the mid point of the beer style's IBU as desired IBU. I hope that makes sense!

---------------------
Actually I've just spotted a different issue with that HCF formula...it tries to minus out any IBU coming from a kit, I guess because it's assuming you're not boiling pre-hopped kit LME. But it minuses out the sum of F5 & F6, which is actually the header and first cell for the kit IBU. It should be F6 and F7. So the formula should read:

=IF(K24="YES",((C3/I27*(K5)-SUM(F6:F7))/260)+1,1)

It'd be pretty rare to hit that glitch...you'd have to be doing a "toucan" kit brew, as well as boiling some LME or DME with hops, in order to want to turn HCF on.

 

[Arch82]

Hey Carnie, cheers for your response mate.

It still doesn't make sense to me why K5/mid point IBU of the beer style is worked into the formula.

I thought the Hop Concentration or Hop Utilization would come down to the boil volume and the gravity of the boil?

For example:

3 liter boil at SG of 1.040
20g 9%AA Armarillo @ 30min
20g 9%AA Armarillo @ 20min
20g 9%AA Armarillo @ 10min

Say this gives me an IBU of 16 in real life each and every time as long as i keep the volume, SG, Hop amount, AA% and timing the same.

In the spreadsheet if i punch in the above hop schedule with "Cream Ale" selected the IBU is 18.1 but when i select "Imperial IPA" the IBU drops to 7.7.

I'm either having a mental blank and not grasping the concept OR i'm over thinking it and not using the spreadsheet (in particular the balance graph) as it was intended..

 

[carniebrew]

Basically, Garetz' formula is taking into account the IBU you're trying to achieve. So choosing a 'tiny' 3l boil when trying to make an Imperial IPA sets the HCF to 2.03. This is because you're trying to extract up to 80 IBU out of hops only boiling in 3 litres of wort, so you'd need a truck-load more hops. So what I guess Garetz is saying is the amount of alpha acids extracted from hops is not linear in small boil volumes such as 3 litres. For example, to generalise, 30 grams of hops in 3l might get you 20 IBU, but 60 grams of hops in 3l won't get you 40, it might only get say 30.

In a cream ale, you're only trying to achieve 17.5 IBU, so the HCF formula adjusts to 1.22 for 3 litres, as you won't have to pile in anywhere near as many hops.

My understanding (and I could be completely off here), is that the formula (and therefore Ian's spreadsheet) assumes your 60 grams of Amarillo in a 3l boil will only get you 7.7 IBU in an Imperial IPA because you must also have a truckload of other hops in the boil in order to achieve your 80 IBU. This is why I assume the spreadsheet takes away any kit IBU's you have, because they wouldn't be in the boil.

So really, the IBU you're getting from the hops in your boil will only make sense once you've put in all the hops it takes to get you to your desired IBU. I doesn't make a lot of sense when you've only put in enough hops in the schedule to get you a few IBU.

I hope that makes sense? All that being said, I wouldn't trust Garetz formula one little bit to accurately tell me how much hops I need to get 80 IBU in a beer when I'm only doing a 3l boil. I don't reckon anyone could! There's a lot of conjecture about Garetz formula on the web...and as many of us have agreed previously on this forum, you're probably best off doing 8+ litre boils and leaving HCF off altogether.

 

[ianh]

The spreadsheet was originally developed to give me a better understanding of formulas and how changing the parameters affected the brew. The HCF was included because some K & E brewers were only boiling hops in small volumes and the Garetz formula was the only one to incorporate a volume factor. All grain brewers, do full volume boils and use the Tinseth formula.

As carniebrew said you are better off doing 8+ litre boils, I reckon 10+ and not use the HCF.