Kit And Extract Beer Spreadsheet

[Lionman]

What program are you opening it with. Is that Excel?

It only works in Excel for me, I have tried in some other Excel compatible apps (like Google sheets) but the macros etc don't all work.

There ar some icons that are not familiar to me in your screen shot, is it a mac?

Cell C6 on the main tab should contain the following function

=IF(ISBLANK($B6),0,VLOOKUP($B6,KITS!$A$3:$D$150,4,FALSE))

which means it grabs the weight from column D on the Kits tab.

Similar functions are used to calculate EBC, IBU, ABV etc though so if one part isn't working I would be surprised if any of it is.

 

[Rod]

I Would like to download the latest version

can you please give me the link

Rod

 

[ianh]

The latest version I unloaded is 4.1 which can be found in the first post of this thread. I think someone else may have uploaded 4.2 which from memory (not good) may have contained extra Kits/BE/hops.

cheers

Ian

 

[frosty3]

Thanks Ian & Lionman. Cell C6 was missing the formulator in V4.2

 

[Lowlyf]

Any reason why this spreadsheet's macros dont work??? (i.e trying to save the recipe etc)

 

[laxation]

I've found I get completely different IBUs when using Brewers Friend and the Spreadsheet for the same recipes.

Does anyone else find the same thing, or am I doing something wrong somewhere?

 

[ianh]

Are you using the HCF in which case they will be quite different. If not then should be similar.

 

[laxation]

What's HCF?

Edit* just found hop concentration factor. No I haven't touched it.

 

[ianh]

When not using the HCF the Spreadsheet uses the Tinseth caculation for IBU's which is the standard way of calculating bitterness. Suggest you look into it further to find why the differences occur.

 

[laxation]

Does it basically mean if I boil with a lower volume, I get less IBU out of the hops?

Does that mean less flavour as well or just less bitterness (and therefore more flavour?)

With HCF selected in the speadsheet (HCF=1.56), the IBU for a 60min boil is still about half in the spreadsheet vs brewers friend =/
This is with a 7L boil

I've never used either of them for a recipe before, so I don't even know what I'm looking for

 

[ianh]

Yes with a smaller volume you get less IBU's. The HCF was put in because a fair percentage of K & E brewers boil small volumes, it is a different calculation from Tinseth and was included to try and give these brewers a better estimation of their IBU's.
What we recommend is you use a boiling volume of at least 10 litres and don't use the HCF.

 

[Buzzliteyear]

Hi Ian, I did a very simple brew this morning and was 4 points out on O.G. not that it matters to much but how did you work out
the F.G. maybe I can tune it
i used
1.7kg coopers real ale
1kg coopers brewing sugar
1kg coopers brew enhancer 2
26L Brew
I got 1044 O.G.
beer style: America PA

If i adjust my fermentation vol to 28L the O.G. is correct but my IBU and EBC are down about 1-2 point

Regards
Brett

 

[ianh]

Hi Buzzliteyear welcome to the forum.
It could be a simple matter of all components not disolved or mixed properly or your volume is not measured accurately. I would not worry about it off one brew, do a few more and if the problem still exists then investigate.

There is a set amount of sugars in each of the components and the spreadsheet uses this to calculate the OG.
Cheers
Ian

 

[Buzzliteyear]

Hi Ian,
thanks for the reply and great forum also great effort in the spreedsheet mate.....the main thing is we have a good base line to work with
which is Awesome......I did only boil for 5 minutes so like you say may not have been 100% dissolved.....

one other thing how do I change the colour of the up and down buttons ? I can not right click on them so they have no menu...excel 2003

Cheers
Brett

 

[ianh]

Hi Buzzliteyear
THe colour is determined from the ingredients, given the ingredients you listed you will finish with a pale beer, low EBC. You could make it darker by adding say some brown sugar or dark malt. Alternatively you could steep some of the grains listed on the GRAINS worksheet.

cheers
Ian

 

[Buzzliteyear]

Oh I meant the colour of the buttons like in my picture, i dont have the "right click menu"

I can not see them very clear as shown

 

[ianh]

Right I am with you now. Yours appear quite a bit darker than the ones I have which are a light-mid blue. Anyway to change the colours of the spin bars you need select a spin bar then go into the Developer (you may need to add this), then select Design Mode, a list of properties for the spin bar should appear in a new window. Need to change the background colour (3rd down) by clickiing on the down arrow at the rhs and selecting palatte then select the colour. Once you have done one you should be able to copy and paste in the properties window for each spin bar.

cheers
Ian

 

[Buzzliteyear]

Great Stuff....I had 2003 and had no Developer but very lucky and found a newer version on old HD that worked....thanks heaps

Cheers
Brett

 

[monkey brewing]

Is there another program I could use to open this spreadsheet as my pc wont let me open it, I dont have Microsoft word, or is there another software program that has features for kit n kilo?

 

[Thomas Wood]

Is there another program I could use to open this spreadsheet as my pc wont let me open it, I dont have Microsoft word, or is there another software program that has features for kit n kilo?

I believe Macros will only work in Microsoft Excel