Jump to content



Photo
* * * * * 17 votes

Kit And Extract Beer Spreadsheet


  • Please log in to reply
722 replies to this topic

#41 buttersd70

buttersd70

    Beerbelly's bitch :)

  • Banned
  • PipPipPipPipPipPipPipPipPip
  • 4,000 posts
  • Joined 28-November 07

Posted 06 February 2009 - 08:18 PM

Anybody who smokes 60 a day can't be smoking real cigarettes. Capstan Full Strength (Brown Packet) used to be 38 milligrams, a real cigarette.


Aye, week uns. Capstan Blue, or Rothmans Red..... B)

#42 matt carty

matt carty

    Mash Maestro

  • Members
  • PipPipPipPipPipPipPip
  • 201 posts
  • Joined 25-October 08
  • Location:Adelaide

Posted 08 February 2009 - 09:04 AM

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

#43 ianh

ianh

    Beer God

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 644 posts
  • Joined 24-October 08
  • Location:Wynyard Tassie

Posted 08 February 2009 - 09:23 AM

yer i mean the specialties, can i say just copy paste the cell somewhere else to have more or is this not possible

ps. i'm doing a real big dubbel so using lots of grains n bits :icon_drool2:

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

#44 ianh

ianh

    Beer God

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 644 posts
  • Joined 24-October 08
  • Location:Wynyard Tassie

Posted 19 February 2009 - 01:28 PM

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.

Attached File  Kit___Extract_Beer_Designer.xls   780.5KB   446 downloads

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

#45 Chappo

Chappo

    Must remember to logout of AHB on Brew Days

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 6,173 posts
  • Joined 14-January 09
  • Location:Chappo Manor

Posted 19 February 2009 - 01:42 PM

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.

#46 pmolou

pmolou

    Beer God

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 415 posts
  • Joined 17-March 08
  • Location:melbourne

Posted 19 February 2009 - 02:06 PM

this is sooo good, i have no idea how you did it but am so glad you did :beer:

#47 whitegoose

whitegoose

    Beer God

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 427 posts
  • Joined 29-January 09

Posted 19 February 2009 - 02:09 PM

Yeah same again ianh, you efforts are greatly appreciated - I've been using your spreadsheet heaps to invent recipes and tinker with others'

#48 Jonez

Jonez

    Mini Mash

  • Members
  • PipPipPipPipPipPip
  • 189 posts
  • Joined 18-January 09
  • Location:Brisbane

Posted 19 February 2009 - 02:29 PM

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

Edited by Jonez, 19 February 2009 - 03:08 PM.


#49 buttersd70

buttersd70

    Beerbelly's bitch :)

  • Banned
  • PipPipPipPipPipPipPipPipPip
  • 4,000 posts
  • Joined 28-November 07

Posted 19 February 2009 - 03:00 PM

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. ;)

#50 ianh

ianh

    Beer God

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 644 posts
  • Joined 24-October 08
  • Location:Wynyard Tassie

Posted 19 February 2009 - 06:30 PM

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


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

Edited by ianh, 19 February 2009 - 06:40 PM.


#51 ianh

ianh

    Beer God

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 644 posts
  • Joined 24-October 08
  • Location:Wynyard Tassie

Posted 19 February 2009 - 06:43 PM

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. ;)


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

#52 MrDizzy

MrDizzy

    Amateur Brewer

  • Members
  • Pip
  • 4 posts
  • Joined 21-February 09
  • Location:Gold Coast

Posted 26 February 2009 - 02:29 AM

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

#53 ianh

ianh

    Beer God

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 644 posts
  • Joined 24-October 08
  • Location:Wynyard Tassie

Posted 26 February 2009 - 06:00 AM

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


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

#54 Barry R

Barry R

    Mash Maestro

  • Members
  • PipPipPipPipPipPipPip
  • 207 posts
  • Joined 17-August 07
  • Location:Hastings, Victoria

Posted 26 February 2009 - 08:04 AM

Thanks for that, will be fixed in next version.


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

#55 Hefty

Hefty

    Mash Maestro

  • Members
  • PipPipPipPipPipPipPip
  • 248 posts
  • Joined 04-February 08
  • Location:Brisbane

Posted 26 February 2009 - 08:19 AM

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.

#56 ianh

ianh

    Beer God

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 644 posts
  • Joined 24-October 08
  • Location:Wynyard Tassie

Posted 26 February 2009 - 11:05 AM

Hi all

Updated version of the spreadsheet

Attached File  Kit___Extract_Beer_Designer.xls   797KB   194 downloads

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

#57 whitegoose

whitegoose

    Beer God

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 427 posts
  • Joined 29-January 09

Posted 26 February 2009 - 09:53 PM

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?

#58 Sentry459

Sentry459

    Amateur Brewer

  • Members
  • Pip
  • 18 posts
  • Joined 30-January 09

Posted 27 February 2009 - 07:20 AM

Ian great stuff mate. Just found this sheet the other day and it's working a treat.

#59 ianh

ianh

    Beer God

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 644 posts
  • Joined 24-October 08
  • Location:Wynyard Tassie

Posted 27 February 2009 - 09:10 AM

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?


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.


Attached File  Kit___Extract_Beer_Designer.xls   798.5KB   265 downloads

cheers

Ian

#60 Muckey

Muckey

    Where's my Beer?

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 875 posts
  • Joined 18-February 08
  • Location:Near Brisbane

Posted 27 February 2009 - 11:26 AM

Beautiful work ianh.

I can see this being the premier kit resource.