Kit And Extract Beer Spreadsheet

[whitegoose]

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]

thunmb up mate i have notice that the beer still does not change colour but the rest is good

 

[ianh]

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]

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]

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]

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]

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]

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]

Crikey, how have I missed this thread!

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

Thanks heaps!

 

[Leigh]

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]

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]

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]

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]

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.

 

[newbud]

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,

 

[DKS]

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]

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]

Another big thank you from me. Very useful spreadsheet. It's a good way of keeping track of of old recipes too

 

[ianh]

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]

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