1 Posted: Sunday, June 18, 2017 9:23 PM

Hi all,

It has come up a few times recently that the IBU numbers given in Ian H's spreadsheet are an estimation of post-fermentation IBUs. While it is true that bitterness drops 10-30% during fermentation, most recipe calculators out there estimate pre-fermentation bitterness. This means that if you are trying to clone an all grain recipe using a kit and Ian's Spreadsheet, you will have problems.

I would like to learn how to change the formula in Ian's Spreadsheet so that it gives pre-fermentation IBUs. I know Lusty has done this, and perhaps there are others too. Can you show us? Inquiring minds would like to know!

Cheers,

Christina.

2 Posted: Sunday, June 18, 2017 11:09 PM

Hi Christina.

Since it's been me picking on your quoted kit IBU numbers of late, I think it's only fitting I help you here.

Open the spreadsheet.
Highlight the “kits” TAB along the bottom of the screen.
You should see the listings for the preset kits in the spreadsheet.
Across the top of the screen you should see the columns labelled, “For 23 litres EBC”, “For 23 litres IBU”, “Weight”, “Can EBC”, “Can IBU”.
To alter the Coopers kit values, highlight the “Can EBC” box & enter the can EBC number listed on the Coopers website for that kit. Highlight the “Can IBU” box & enter the can IBU number listed on the Coopers website for that kit.
On the same row as that kit you have changed the EBC number for, highlight the box under the “For 23 litres EBC” column & with (no arrows & no quotations) type the following formula example with RN being the Row Number… "=E<RN>*1.25/23“
On the same row as that kit you have changed the IBU number for, highlight the box under the ”For 23 litres IBU" column & with (no arrows & no quotations) type the following formula example with RN being the Row Number… "=E<RN>*1.7/23“

Copy & pasting these formulas directly down each column will formulate the kit numbers ”For 23 litres EBC“ & ”For 23 litres IBU“ on the corresponding rows to the EBC & IBU values placed in the ”Can EBC“ & ”Can IBU" columns on the same rows.

Coopers calculate the bitterness of their kits by weight, not volume. The weight of the Coopers kits is 1.7kg, whereas IanH's Spreadsheet is setup to use the volume (1.25L) of the kit to calculate IBU.

Coopers DIY FAQ's - Brewing Info: To Calculate The Bitterness Of The Brew

I hope that is easy enough to follow.

Cheers,

Lusty.

3 Posted: Monday, June 19, 2017 12:30 AM

Beerlust:

Hi Christina.

Since it's been me picking on your quoted kit IBU numbers of late, I think it's only fitting I help you here.

Open the spreadsheet.
Highlight the “kits” TAB along the bottom of the screen.
You should see the listings for the preset kits in the spreadsheet.
Across the top of the screen you should see the columns labelled, “For 23 litres EBC”, “For 23 litres IBU”, “Weight”, “Can EBC”, “Can IBU”.
To alter the Coopers kit values, highlight the “Can EBC” box & enter the can EBC number listed on the Coopers website for that kit. Highlight the “Can IBU” box & enter the can IBU number listed on the Coopers website for that kit.
On the same row as that kit you have changed the EBC number for, highlight the box under the “For 23 litres EBC” column & with (no arrows & no quotations) type the following formula example with RN being the Row Number… "=E<RN>*1.25/23“
On the same row as that kit you have changed the IBU number for, highlight the box under the ”For 23 litres IBU" column & with (no arrows & no quotations) type the following formula example with RN being the Row Number… "=E<RN>*1.7/23“

Copy & pasting these formulas directly down each column will formulate the kit numbers ”For 23 litres EBC“ & ”For 23 litres IBU“ on the corresponding rows to the EBC & IBU values placed in the ”Can EBC“ & ”Can IBU" columns on the same rows.

Coopers calculate the bitterness of their kits by weight, not volume. The weight of the Coopers kits is 1.7kg, whereas IanH's Spreadsheet is setup to use the volume (1.25L) of the kit to calculate IBU.

Coopers DIY FAQ's - Brewing Info: To Calculate The Bitterness Of The Brew

I hope that is easy enough to follow.

Cheers,

Lusty.


Hi Lusty,

Thanks for the info. The can EBC and IBU info are already present and correct. When I try to change the IBU formula as you gave it, that is changing the 1.25 to 1.7, instead of displaying the result, it displays the formula and even the name of the kit disappears. I don't understand why. Any ideas?

Cheers,

Christina.

4 Posted: Monday, June 19, 2017 12:35 AM

ChristinaS1:

Beerlust:

Hi Christina.

Since it's been me picking on your quoted kit IBU numbers of late, I think it's only fitting I help you here.

Open the spreadsheet.
Highlight the “kits” TAB along the bottom of the screen.
You should see the listings for the preset kits in the spreadsheet.
Across the top of the screen you should see the columns labelled, “For 23 litres EBC”, “For 23 litres IBU”, “Weight”, “Can EBC”, “Can IBU”.
To alter the Coopers kit values, highlight the “Can EBC” box & enter the can EBC number listed on the Coopers website for that kit. Highlight the “Can IBU” box & enter the can IBU number listed on the Coopers website for that kit.
On the same row as that kit you have changed the EBC number for, highlight the box under the “For 23 litres EBC” column & with (no arrows & no quotations) type the following formula example with RN being the Row Number… "=E<RN>*1.25/23“
On the same row as that kit you have changed the IBU number for, highlight the box under the ”For 23 litres IBU" column & with (no arrows & no quotations) type the following formula example with RN being the Row Number… "=E<RN>*1.7/23“

Copy & pasting these formulas directly down each column will formulate the kit numbers ”For 23 litres EBC“ & ”For 23 litres IBU“ on the corresponding rows to the EBC & IBU values placed in the ”Can EBC“ & ”Can IBU" columns on the same rows.

Coopers calculate the bitterness of their kits by weight, not volume. The weight of the Coopers kits is 1.7kg, whereas IanH's Spreadsheet is setup to use the volume (1.25L) of the kit to calculate IBU.

Coopers DIY FAQ's - Brewing Info: To Calculate The Bitterness Of The Brew

I hope that is easy enough to follow.

Cheers,

Lusty.


Hi Lusty,

Thanks for the info. The can EBC and IBU info are already present and correct. When I try to change the IBU formula as you gave it, that is changing the 1.25 to 1.7, instead of displaying the result, it displays the formula and even the name of the kit disappears. I don't understand why. Any ideas?

Cheers,

Christina.


Oh, I just figured it out. I have to press “enter.” LOL! Thanks.

Cheers,

Christina.

5 Posted: Monday, June 19, 2017 12:42 AM

ChristinaS1:

…When I try to change the IBU formula as you gave it, that is changing the 1.25 to 1.7, instead of displaying the result, it displays the formula and even the name of the kit disappears. I don't understand why. Any ideas?

Have you included the equals sign “=” in the formula? (i.e. =F23*1.7/23)

Lusty.

6 Posted: Monday, June 19, 2017 12:43 AM

ChristinaS1:

Oh, I just figured it out. I have to press “enter.” LOL! Thanks.

Haha!

So it is working OK now?

7 Posted: Monday, June 19, 2017 1:14 AM

Beerlust:

ChristinaS1:

Oh, I just figured it out. I have to press “enter.” LOL! Thanks.

Haha!

So it is working OK now?


Yes, it is. Now I have to rewrite all my recipes. It is making a big difference in the graph, where it shows comparison to style guidelines.

Just playing with it now. If I enter the Half Ruby Porter kit and the recommended 1kg of DME, the IBUs are too high for the Brown Porter style, and in terms of gravity, far too low for the Robust Porter style. I checked a couple of others (ie English Bitter) and the same is true: too bitter. Am I missing something? On the other hand, maybe it makes sense. I do find the English Bitter kit more bitter than the ones I sampled in England. I know it is popular on this forum, but I haven't used it in a long time. When I want an English Bitter I use an APA or OS Lager kit as a base.

Cheers,

Christina.

8 Posted: Monday, June 19, 2017 7:49 PM

Very interesting discussion! Perhaps this explains why my Brew J IPA recipe has come out too bitter? I will retrieve my recipe and put it on here. I'll also plug it into some.other calculators to compare. My recipe is based on the Brew A IPA can with 5min additions, 0min additions, quickly chilled in sink and then added to the FV with the Brew A IPA can

Wouldn't have thought those late additions could add THAT much bitterness

9 Posted: Monday, June 19, 2017 8:30 PM

So here is my Brew J IPA recipe:

1 x 1.7kg Brew A IPA can
Partial mash of 3kg ale malt, 250g Carapils and 150g Light Crystal

OG 1.055

Centennial 15 @ 5min
Cluster 20 @ 5min
Centennial 15 @ 0min
Chinook 20 @ 0min
Chinook 46 dry hopped

The original formula to calculate IBUs of the kit multiplies IBU by 0.8. So on the version of IanH that I have IBU for the can is 49.1 and my 5min hop additions add another 6.67 IBU.

BUT if we are calculating using Coopers formula and not scrubbing off 20% due to fermentation, the IBU for the can is 61.3.

Certainly tastes more bitter, and is out of balance with the malt body and sweetness.

10 Posted: Monday, June 19, 2017 8:31 PM

joolbag:

Very interesting discussion! Perhaps this explains why my Brew J IPA recipe has come out too bitter? I will retrieve my recipe and put it on here. I'll also plug it into some.other calculators to compare. My recipe is based on the Brew A IPA can with 5min additions, 0min additions, quickly chilled in sink and then added to the FV with the Brew A IPA can

Wouldn't have thought those late additions could add THAT much bitterness


You are right Joolbag. I just checked and the Brew A IPA kit has 61.3IBUs, according to the Ian's H's spreadsheet, with Lusty's recommended modifications. That is really bitter. The style guidelines say 40-60IBUs for an American IPA.

Cheers,

Christina.

11 Posted: Monday, June 19, 2017 9:10 PM

ChristinaS1:

You are right Joolbag. I just checked and the Brew A IPA kit has 61.3IBUs, according to the Ian's H's spreadsheet, with Lusty's recommended modifications. That is really bitter. The style guidelines say 40-60IBUs for an American IPA.

Cheers,

Christina.

Hmmm, I am very surprised to hear that. When I brewed it a few batches back (without using any sort of software to tweak my recipe ) it lacked the firm bitterness I expect from an American IPA.

Last edited by porschemad911 (Monday, June 19, 2017 9:10 PM)