Help Support Aussie Homebrewer by donating:

  1. 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.
    Dismiss Notice

Kit And Extract Beer Spreadsheet

Discussion in 'Kits & Extracts' started by ianh, 3/2/09.

 

  1. Lionman

    Well-Known Member

    Joined:
    8/4/13
    Messages:
    864
    Likes Received:
    307
    Gender:
    Male
    Location:
    Perth NOR
    Posted 26/5/17
    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.
     
  2. Rod

    Well-Known Member

    Joined:
    12/5/03
    Messages:
    702
    Likes Received:
    36
    Home Page:
    Posted 26/5/17
    I Would like to download the latest version

    can you please give me the link

    Rod
     
  3. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    92
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 26/5/17
    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
     
  4. frosty3

    New Member

    Joined:
    16/3/17
    Messages:
    3
    Likes Received:
    0
    Posted 26/5/17
    Thanks Ian & Lionman. Cell C6 was missing the formulator in V4.2
     
  5. Lowlyf

    Well-Known Member

    Joined:
    3/6/15
    Messages:
    270
    Likes Received:
    62
    Gender:
    Male
    Location:
    Brisbane
    Posted 18/6/17
    Any reason why this spreadsheet's macros dont work??? (i.e trying to save the recipe etc)
     
  6. laxation

    Phlegm TB

    Joined:
    24/6/08
    Messages:
    757
    Likes Received:
    372
    Posted 19/6/17
    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?
     
  7. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    92
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 19/6/17
    Are you using the HCF in which case they will be quite different. If not then should be similar.
     
  8. laxation

    Phlegm TB

    Joined:
    24/6/08
    Messages:
    757
    Likes Received:
    372
    Posted 19/6/17
    What's HCF?

    Edit* just found hop concentration factor. No I haven't touched it.
     
    Last edited: 19/6/17
  9. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    92
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 19/6/17
    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.
     
  10. laxation

    Phlegm TB

    Joined:
    24/6/08
    Messages:
    757
    Likes Received:
    372
    Posted 20/6/17
    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
     
  11. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    92
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 20/6/17
    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.
     
    laxation likes this.
  12. Buzzliteyear

    New Member

    Joined:
    2/10/17
    Messages:
    4
    Likes Received:
    1
    Gender:
    Male
    Posted 3/10/17
    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
     
  13. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    92
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 3/10/17
    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
     
  14. Buzzliteyear

    New Member

    Joined:
    2/10/17
    Messages:
    4
    Likes Received:
    1
    Gender:
    Male
    Posted 4/10/17
    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
     
    Brewnicorn likes this.
  15. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    92
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 4/10/17
    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
     
  16. Buzzliteyear

    New Member

    Joined:
    2/10/17
    Messages:
    4
    Likes Received:
    1
    Gender:
    Male
    Posted 4/10/17
    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
     

    Attached Files:

  17. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    92
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 4/10/17
    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
     
  18. Buzzliteyear

    New Member

    Joined:
    2/10/17
    Messages:
    4
    Likes Received:
    1
    Gender:
    Male
    Posted 5/10/17
    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
     
  19. monkey brewing

    Well-Known Member

    Joined:
    20/8/18
    Messages:
    52
    Likes Received:
    5
    Gender:
    Male
    Occupation:
    Warehouse Clerk/Gir clerk
    Location:
    Lake Macquarie
    Posted 22/8/18
    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?
     
  20. Thomas Wood

    Well-Known Member

    Joined:
    5/6/18
    Messages:
    154
    Likes Received:
    63
    Gender:
    Male
    Occupation:
    Front End Developer
    Location:
    Newcastle
    Posted 22/8/18
    I believe Macros will only work in Microsoft Excel :(
     

Share This Page