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. buttersd70

    Beerbelly's bitch :)

    Joined:
    27/11/07
    Messages:
    3,550
    Likes Received:
    8
    Gender:
    Male
    Posted 6/2/09
    Aye, week uns. Capstan Blue, or Rothmans Red..... B)
     
  2. mattcarty

    Well-Known Member

    Joined:
    25/10/08
    Messages:
    194
    Likes Received:
    3
    Gender:
    Male
    Home Page:
    Posted 7/2/09
    wow, what an awesome spreadsheet, ian mate thanks for all the hard work on this one buddy, looks like a lot of time and effort has gone into it.

    cant wait to take it for a test spin after i get my newbie mind around all those numbers and acronyms :D

    Cheers
    Carty
     
  3. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    96
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 7/2/09
    Sorry had a look at the spreadsheet and somehow the drop downlist from the 3rd grain cell disappeared. Just copy down cell B20 to B21 and that should give you three. Fix in next version.

    cheers

    Ian
     
  4. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    96
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 19/2/09
    Hi

    I have been doing a bit more work on the spreadsheet and I think we now have a workable version. Will wait for others to comment.

    View attachment Kit___Extract_Beer_Designer.xls

    There is now a choice of method for calculating the hop bitterness plus have made the boil time increments 5 minutes.

    Sorted out the Brewday worksheet so can be easily altered and it is printable.

    I have added a few more kits to the list and there are lots of comments associated with the beer styles and the grains.

    On the Main worksheet I have enlarged the graph, hidden some of the calculations and protected the worksheet so its harder to delete anything by mistake.

    The Notes have been updated and should be read again.

    cheers

    Ian
     
  5. chappo1970

    Must remember to logout of AHB on Brew Days

    Joined:
    14/1/09
    Messages:
    5,903
    Likes Received:
    14
    Gender:
    Male
    Home Page:
    Posted 19/2/09
    Wow Ian you have been a busy boy. Like the tabs now, neat layout. Brew day is awesome too. I like!

    I'm going to have a play and get back to you. Thanks for the hard work.
     
  6. pmolou

    Well-Known Member

    Joined:
    17/3/08
    Messages:
    408
    Likes Received:
    1
    Gender:
    Male
    Home Page:
    Posted 19/2/09
    this is sooo good, i have no idea how you did it but am so glad you did :beer:
     
  7. whitegoose

    Well-Known Member

    Joined:
    29/1/09
    Messages:
    424
    Likes Received:
    22
    Gender:
    Male
    Location:
    Mount Lawley, WA
    Posted 19/2/09
    Yeah same again ianh, you efforts are greatly appreciated - I've been using your spreadsheet heaps to invent recipes and tinker with others'
     
  8. Jonez

    Well-Known Member

    Joined:
    18/1/09
    Messages:
    182
    Likes Received:
    0
    Gender:
    Male
    Posted 19/2/09
    Great work, good research. It has bugs but most I have seen you can fix by including some error handling code. (for example not allowing negative values on the quantity fields, etc)

    If it is OK to give you this advice, I think you should protect all the formulas and leave the users have access to modify other information. (An unintentional keystroke could delete the code)


    Other than that it seems pretty complete.


    EDIT: removed unrelated quote
     
  9. buttersd70

    Beerbelly's bitch :)

    Joined:
    27/11/07
    Messages:
    3,550
    Likes Received:
    8
    Gender:
    Male
    Posted 19/2/09
    good on ya, ian. Give me a couple of days to really 'wring its kneck', and put it through it's paces, and will feedback to you then. ;)
     
  10. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    96
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 19/2/09
    Hi Jonez

    I agree it still has bugs but hopefully they are being eliminated, I have updated the code so as not to allow negative values and the volumes must be a minimum of 1 litre. In the latest version the Main worksheet is protected and I have given info how to change things.

    If you read the notes it tells you how to change things.

    It is basically still a work in progress but I think it has reached a stage where it can be used confidently with caution.

    cheers

    Ian
     
  11. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    96
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 19/2/09
    Thanks buttersd70, look forward to it. As I keep saying I am a Novice brewer and need the input of experts like yourself.

    cheers

    Ian
     
  12. MrDizzy

    New Member

    Joined:
    21/2/09
    Messages:
    4
    Likes Received:
    0
    Gender:
    Male
    Home Page:
    Posted 25/2/09
    Awsome!!!

    I noticed one bug, though:
    The toohey's kits are in the list at the bottom, but don't appear in thr dropdown list
     
  13. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    96
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 25/2/09
    Thanks for that, will be fixed in next version.

    To fix in your copy

    Go Tools menu, Protection, Unprotect worksheet

    Select cell B7 then the Data Menu and Validation, brings up a Validation Box. Change the 139 value in the source window to 150.

    Repeat for cell B8 then protect the worksheet.

    cheers

    Ian
     
  14. itguy1953

    Well-Known Member

    Joined:
    17/8/07
    Messages:
    207
    Likes Received:
    10
    Gender:
    Male
    Home Page:
    Posted 25/2/09
    Hi Ian

    While you are updating the ss, would it be possible to include the Australian beer styles. There are standards for Australian Ale (Pale and Dark), Lager (standard, light and premium), Wheat and Aussie/Foreign Stout.

    Barry
     
  15. Hefty

    Well-Known Member

    Joined:
    4/2/08
    Messages:
    257
    Likes Received:
    3
    Gender:
    Male
    Home Page:
    Posted 25/2/09
    I'm loving this spreadsheet! I've already come up with my next four or five brews.
    One thing I did notice (I've fixed it on my copy) some of your drop down list data at the bottom of the "main" sheet doesn't match the data from the sheet it references. There were a couple but the only example I can remember of the top of my head is that on the main sheet the drop down list entry "Stout Extra" doesn't match the beer style sheet because it is "Stout Foreign Extra". This causes all the OG and FG values to come up as #NA.
    Apart from that, awesome!

    Cheers!
    Jono.
     
  16. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    96
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 26/2/09
    Hi all

    Updated version of the spreadsheet

    View attachment Kit___Extract_Beer_Designer.xls

    I have included Bulk Priming as an option, the CO2 values for the styles I got from Beersmith, however some are quite different from other data on the web e.g. German wheat beers and Mild.

    I have fixed the dropdown lists for the kits and styles and all lists allow for additional items to be added. So if you want to say add additional styles, include the data on the Styles worksheet and add the extra styles to the list at the bottom of the Main worksheet.

    I also have fixed an error in the calculation of the total colour.

    cheers

    Ian
     
  17. whitegoose

    Well-Known Member

    Joined:
    29/1/09
    Messages:
    424
    Likes Received:
    22
    Gender:
    Male
    Location:
    Mount Lawley, WA
    Posted 26/2/09
    Thanks again ianh... this is my bible now.
    But.... There seems to be something funny going on the the EBC calculation - the previous version gave me an EBC of about 10 but this one is giving me only 3.5 - seems to be ignoring the EBC from the kit and only taking into account the LDME and the CaraPils...

    Anyone else getting this?
     
  18. Sentry459

    Member

    Joined:
    30/1/09
    Messages:
    18
    Likes Received:
    0
    Home Page:
    Posted 26/2/09
    Ian great stuff mate. Just found this sheet the other day and it's working a treat.
     
  19. ianh

    Well-Known Member

    Joined:
    24/10/08
    Messages:
    687
    Likes Received:
    96
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Wynyard Tassie
    Posted 26/2/09
    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 Kit___Extract_Beer_Designer.xls

    cheers

    Ian
     
  20. muckey

    Where's my Beer?

    Joined:
    18/2/08
    Messages:
    860
    Likes Received:
    2
    Gender:
    Male
    Home Page:
    Posted 27/2/09
    Beautiful work ianh.

    I can see this being the premier kit resource.
     

Share This Page