• We have implemented the ability to gift someone a Supporting Membership now! When you access the Upgrade page there is now a 'Gift' button. Once you click that you can enter a username to gift an account Upgrade to. Great way to help support this forum plus give some kudos to anyone who has helped you.

Kit And Extract Beer Spreadsheet

Aussie Home Brewer

Help Support Aussie Home Brewer:

whitegoose

Well-Known Member
Joined
29/1/09
Messages
424
Reaction score
22
Location
Mount Lawley, WA
Sorry about that I was trying to sort others out and forgot about the kits. Then had to put my maths hat on to fix the kits.

Hopefully all now working correctly.


View attachment 25001

cheers

Ian
Thanks for the quick response!

It's still giving me a different EBC to the spreadsheet 2 versions ago, but much closer... My beer that used to come out about 10 EBC is now 7.9 EBC - I take it you have tweaked the calculations?
 

ford-ute

Well-Known Member
Joined
9/4/08
Messages
56
Reaction score
0
thunmb up mate i have notice that the beer still does not change colour but the rest is good
 

ianh

Well-Known Member
Joined
24/10/08
Messages
689
Reaction score
99
Location
Wynyard Tassie
Thanks for the quick response!

It's still giving me a different EBC to the spreadsheet 2 versions ago, but much closer... My beer that used to come out about 10 EBC is now 7.9 EBC - I take it you have tweaked the calculations?
The original calculations did not calculate the total EBC correctly, hopefully they do now and will be lower than the previous values. There could still be an issue with the values from Coopers Kits. Coopers specify can contents rather than what the can will produce in say 23 litres. I have taken a value between what Coopers say and what others say.

Still have not got a copy of Excel 2007, so the beers colours won't change till I can have a look at it.

I think in general though we are getting close.

cheers

Ian
 

unterberg

Well-Known Member
Joined
29/5/08
Messages
280
Reaction score
2
Thanks mate. I think its great!

Put down a Doppelbock 2 weeks ago and according to the spreadsheet my EBC was way to high (I didnt care really) but after putting things in this one its actually all within the style. I think the EBC nearly went halves from 45 to 23.
 

ianh

Well-Known Member
Joined
24/10/08
Messages
689
Reaction score
99
Location
Wynyard Tassie
Thanks mate. I think its great!

Put down a Doppelbock 2 weeks ago and according to the spreadsheet my EBC was way to high (I didnt care really) but after putting things in this one its actually all within the style. I think the EBC nearly went halves from 45 to 23.
Thanks I thought it more important to sort out the hops/bitterness first before the colour.

cheers

Ian
 

smollocks

Well-Known Member
Joined
1/2/09
Messages
134
Reaction score
0
Thanks for this Ian, I've found it very educational. My only suggestion is to consider version numbers and maybe even a brief changelog on the notes page.
 

syd_03

Well-Known Member
Joined
30/3/07
Messages
375
Reaction score
0
G'day Ian,

The speadsheet is a great Idea and you have done a great job.

On the issues of Coopers kits, they explain how to get the values on their FAQ page. Basically it is the number times the can weight divided by the volume.

Eg. IPA is 710 IBU which is times 1.7 then divided by 23L is 52.478 IBU (your value was 30.9 which is 710/23) same goes for colour values.

They then go on to say that fermantation will lower these valuse by 10 to 30 percent so the final IBU in the brew due to the kit may be between 36.735 to 47.230 IBU for this example.

Not really sure how you calculate how much is lost due to fermentation I would say that the entire brew would have the loss i.e. the Kit IBU of 52.478 added to the IBU from hop addition then reduced by somewhere between 10 to 30 percent?
Similar to EBC from kit plus all additions then reduced?

Sorry if this is confusing, I have confused myself, just starting to look at all these factors for the first time.
 

ianh

Well-Known Member
Joined
24/10/08
Messages
689
Reaction score
99
Location
Wynyard Tassie
G'day Ian,

The speadsheet is a great Idea and you have done a great job.

On the issues of Coopers kits, they explain how to get the values on their FAQ page. Basically it is the number times the can weight divided by the volume.

Eg. IPA is 710 IBU which is times 1.7 then divided by 23L is 52.478 IBU (your value was 30.9 which is 710/23) same goes for colour values.
G'day syd_03

Yes I was aware of that, but a number of more experienced brewers (not hard just bottled brew 20) say that, especially for the bitterness, that a value of Can IBU / Volume is more realistic and that the Coopers calcs still overstate the bitterness. So I went with the experience.

Like you learning and trying to get my head round all the numbers.

cheers

Ian
 

Leigh

Made at Home
Joined
17/10/07
Messages
1,180
Reaction score
1
Crikey, how have I missed this thread!

Awesome tool Ian...will have to start playing with this.

Thanks heaps!
 

Leigh

Made at Home
Joined
17/10/07
Messages
1,180
Reaction score
1
OK, so I used this spreadsheet last night when I put on my latest Golden Ale extract with partial mash...adjusted the grain and hop schedules to get a "better balance"...will report back on how it goes.
 

chriscapetrib

Member
Joined
12/8/07
Messages
6
Reaction score
0
Wow Ian

I have only just found this thread..... Great work! Heaps of info. Easy to understand and use. Gotta love Excel!!


:icon_cheers:
Chris
 

ianh

Well-Known Member
Joined
24/10/08
Messages
689
Reaction score
99
Location
Wynyard Tassie
Hi

Just updated the spreadsheet called the new version 1.1

View attachment Kit___Extract_Beer_Designer_V1.1.xls


Changes since previous version

Changed the efficiency factor for steeping grains from 50% to 60%.

The colour for liquid malts was incorrectly calculated. This has been changes so it matches the manufacturers data of xxx EBC in 23 litres for the can, except for Coopers which specify the can contents.

The colour of the beer in the glass now changes. (I got a copy of Excel 2007, made the changes but it also seems to work ok in older versions of Excel)

cheers

Ian
 

mrwest

Member
Joined
8/3/09
Messages
5
Reaction score
0
Thanks ianh, this is quite useful even for a beginner like me who's only doing K&B brews. Even though I'm not making use of all the features, basic stuff like estimating alcohol content and what gravity readings to expect is very handy :)
 

Gwyn

Member
Joined
1/4/09
Messages
13
Reaction score
0
Cheers ianh, I'm a big fan of Excel and thats a great spreadsheet! Just joined up and am hoping that there is heaps more stuff as useful as this. :D
 

newbud

Member
Joined
2/3/09
Messages
10
Reaction score
0
Ditto, thankyou for this one. Really good and I was thinking of doing two cans now I can try and give it a more calculated shot

cheers,
:D
 

DKS

Well-Known Member
Joined
23/3/08
Messages
1,018
Reaction score
4
Hi

Just updated the spreadsheet called the new version 1.1


Top stuff ianh just used your spreadsheet for a pale this week end. I like it because you dont have to be that technically advanced down the HB path to get it working for you.
Could you or others give a link or info on BU:GU and BV (grouped with attenuation on the speadsheet.) What they are and what they mean in relation to each other. What target numbers to go for if thats applicable etc. Probably in wiki but havent found yet .
Thanks for your efforts. :icon_cheers:
Daz
 

Rodolphe01

Well-Known Member
Joined
26/9/08
Messages
503
Reaction score
23
This spreadsheet is great, I was doing kits and bits without ever really knowing what IBU and EBC I will get. I've just started using this, great work.
 

AlexL

Well-Known Member
Joined
5/2/09
Messages
46
Reaction score
1
Another big thank you from me. Very useful spreadsheet. It's a good way of keeping track of of old recipes too
 

ianh

Well-Known Member
Joined
24/10/08
Messages
689
Reaction score
99
Location
Wynyard Tassie
Top stuff ianh just used your spreadsheet for a pale this week end. I like it because you dont have to be that technically advanced down the HB path to get it working for you.
Could you or others give a link or info on BU:GU and BV (grouped with attenuation on the speadsheet.) What they are and what they mean in relation to each other. What target numbers to go for if thats applicable etc. Probably in wiki but havent found yet .
Thanks for your efforts. :icon_cheers:
Daz
Just google BU:GU will give you heaps of sites and the BV Link

The values given for the BJCP types are calculated using the midpoint or average values.
 

ianh

Well-Known Member
Joined
24/10/08
Messages
689
Reaction score
99
Location
Wynyard Tassie
Another big thank you from me. Very useful spreadsheet. It's a good way of keeping track of of old recipes too
If you read the notes it tells you how to copy the BREWDAY worksheet into a Cell Comment in Excel that way you can either use it as part of your Excel Brewlog or create a spreadsheet of recipes, all you need is the recipe name then the Brewday Worksheet inserted as a Comment.

The Excel Brewlog I use has one brew per line and then all the details inserted as Comments.

cheers

Ian
 
2

Latest posts

Top