Quantcast

Kit And Extract Beer Spreadsheet

Aussie Home Brewer

Help Support Aussie Home Brewer:

Sam Smith

New Member
Joined
4/10/10
Messages
3
Reaction score
0
Hi Guys

I am a newbie here but as part of my home brew learning process I thought I would develop a spreadsheet to enable me to design beers using Kits & Extracts. I find most of the software available is complicated and not really suitable. So it has certainly been an interesting experience and I am sure other guys have done this before me.

As I look around the forum I feel there is a need for such software as most software seems to cater for all grain brewers.

So I present it here, whilst I don't recommend people use it as their only piece of software. Hopefully in the future that may not be the case, I would appreciate comments on what is required and what is wrong especially on conversion factors.

So please tell me what's wrong with it, whether the factors need to be changed, what you would like to see and we will see what we can do.

If you download it, please read the Notes before running the program to tell you how to set it up and comment on any problems you find.

Thanks

Ian

The latest revision is located here

And here is the latest version as well.
View attachment 52561
 

freebart

Member
Joined
24/9/18
Messages
5
Reaction score
0
Location
Japan
I took a look at it. It looks really nice. The one thing that doesn't work for me is the way it handles different batch sizes. Even when I enter a ferment volume less than 23 liters, it doesn't make the adjustments. I wind up getting odd figures like a 9-liter batch of lager, no added hops, with a 65-point IBU.

Otherwise it's really nice, especially since I only use Morgans kits at this point.
 

ianh

Well-Known Member
Joined
24/10/08
Messages
690
Reaction score
99
Location
Wynyard Tassie
Given the IBU's of the can you should expect something like that. If you enter a ferment volume of 23 litres and the enter a recipe, you should be able to see the values change as you change the ferment volume.
 

Yuz

Well-Known Member
Joined
4/8/17
Messages
244
Reaction score
75
Location
Melb SE
Given the IBU's of the can you should expect something like that. If you enter a ferment volume of 23 litres and the enter a recipe, you should be able to see the values change as you change the ferment volume.
Also - make sure Excel has Automatic Workbook Calculations enabled in "Formulas" option.
If it's set to Manual, it won't update itself.
 

freebart

Member
Joined
24/9/18
Messages
5
Reaction score
0
Location
Japan
Thanks. Calculations are set to automatic I'm still having problems, though.

I've got the following:
Ferment volume: 9 litres
Kit: Morgans BML, 0.6kg
LME: Morgans Unhopped Extra Pale, 0.6kg
no hops entered

And I get an IBU of 63.9. That just can't be right, can it? The IBU field doesn't change as I change the amounts of kit and LME. Is it solely dependent on the ferment volume field?
 

Stewart Duffy

New Member
Joined
24/10/18
Messages
1
Reaction score
2
Location
Auckland
Hi all. Love the spreadsheet, some awesome work has been put into this. I have a couple of questions about adding to he Kits worksheet.

I understand that the IBU's for the coopers kits follow the following formula:

Product bitterness x 1.7 / Brew volume = Brew Bitterness before fermentation. So following this example, and the example on the coopers FAQ page, we can do this:

Mexican Cerveza up to a volume of 23 litres: 270 x 1.7 / 23 = 20 (rounded from 19.95) IBU (International Bitterness Units). Ok cool. But the spreadsheet lists the IBU as for that kit at 14.7. I am guessing this is to account for loss after fermentation which the coopers site says can be 10-30%. All good.

So what is the the formula used to calculate the IBU for 23 litres? Is it Product bitterness x kit weight / Brew volume = Brew Bitterness - [some & in here for loss]? What % is used for loss? Am I missing a bit here?

Likewise I would like to know how the Black Rock kits were calculated. I am guessing the were calculated in the reverse way than the coopers because both the new Black Rock (& old site) list the IBU for the 23 litres before loss. What % was subtracted from these before calculating the IBU for the can?

I would very like to update this spreadsheet to add all the current Black Rock range, some of the new Coopers kits and the Mangrove jacks, but I am just missing some key numbers here, that I haven't been able to place.

Any advice would be awesome!

Chur
 

freebart

Member
Joined
24/9/18
Messages
5
Reaction score
0
Location
Japan
Yay, finally got it to work. There was an extra permission that appeared at the top (in a yellow bar) that I hadn't noticed before. Works well now, thanks!
 

Indian Giver

Well-Known Member
Joined
22/8/18
Messages
45
Reaction score
14
Location
Pine Rivers South Town
I would love it if it went partials as well, but that's being picky. It was great.

Noticed on little thing. I wanted to calculate a 2min hop addition and it shows as 0 IBU from that addition if I do (on 150g of hops). I know that adequate isomerisation will occur before I cool below 85 degrees, so just wondering about this.
 

Jamesrs59

New Member
Joined
26/9/19
Messages
1
Reaction score
0
Location
Sunshine Coast
Hey all, reviving an old thread, been using this sheet and it’s great, so thank you OP.

being the lazy fella I am, does anyone have a sheet filled with a bunch of premade recipes they’re willing to share?
 

Journeyman

Active Member
Joined
25/10/19
Messages
34
Reaction score
6
Location
Murray Bridge
@ianh
I run Linux Mint and use LibreOffice, a version (or replacement) for OpenOffice. The spreadsheet works partially - e.g. the 'New Recipe button clears the front page but the other buttons give a VBA error. See attached.
Apparently OpenOffice users don't get even the level of functionality I get in LO.
Any way to resolve this without me learning VB programming? :D
 

Attachments

Journeyman

Active Member
Joined
25/10/19
Messages
34
Reaction score
6
Location
Murray Bridge
Use a different operating system?
Now THAT raises an interesting point - am I addicted enough at this early stage to go back to Windoze? Even as a dual boot Billy's garbage is a pain in the arse - and I supported users using it for 20+ years.
Maybe I could VM it and just open it for the SS?
 

koshari

Well-Known Member
Joined
4/5/17
Messages
527
Reaction score
180
even just having a VM you could boot. i dunno that the VB programming is going to help because thats the functionality generally missing in OO or LO. someone would have to try and port the advanced functions in perl or some other native linux script.
for the record we generally use linux mint mate and i run a dual boot laptop and have a few windows VMs with XP , win7 and win 10 that i boot through virtualbox for some legacy aps i require for my work.
 

ukulele01

New Member
Joined
26/1/14
Messages
1
Reaction score
0
Question for Ian H: been using your spreadsheet for years, it is great. The only thing lacking is that it does not calculate IBU from FO additions, for partial mashes. Any chance that could be added? Thanks for all of your hard work. Cheers.
 

Latest posts

Top