# Biab Beer Designer Spreadsheet



## ianh

Hi

As some of you may be aware I have moved on from Kits & Extracts to BIAB, so in the process of doing so I developed a spreadsheet. 

I specifically use a 40 litre Crown urn for brewing but the spreadsheet gives the option to chose a crown urn or a pot.

The spreadsheet is still in it's development stage but I feel it can certainly be a guide to BIAB brewers.

Please read the notes worksheet before using.

The original version 1.0 was posted on BIABrewer and changes since include

Changed the IBU calculation so it includes the fermenter volume plus the trub.

Changed the dropdown lists so The ingredients are on their worksheets.

Changed how FG is calculated, Adjuncts now have a fermentabilty factor.

Changed how the brewhouse efficiency is calculated.

Added a water temp factor for those who use hot water to fill their urn or pot.

The spreadsheet is unprotected so be careful what you delete but you can change what you will, for example on the BREWS worksheet if you use a hydrometer only, just hide the Refractometer columns.

Comments,criticisms & suggestions appreciated.

cheers

Ian
View attachment BIAB_Beer_Designer_v1.3.xls


----------



## bignath

IanH, i'm not a biab'er, but seriously mate, do you sleep at all? The amount of work you put in to these spreadsheets of yours is incredible. I used the K&K sheet when i was kits and it was awesome.

If it was anything to go by, i am sure the other BIAB'ers out there will love your work!


----------



## ianh

Big Nath said:


> IanH, i'm not a biab'er, but seriously mate, do you sleep at all? The amount of work you put in to these spreadsheets of yours is incredible. I used the K&K sheet when i was kits and it was awesome.
> 
> If it was anything to go by, i am sure the other BIAB'ers out there will love your work!



Thanks, one of the advantages of being retired (old Bastard).


----------



## sama

ianh said:


> Thanks, one of the advantages of being retired (old Bastard).


 good luck to ya mate :icon_cheers:


----------



## Hinji

Looks the goods mate. I've only been using Brewmate recently, but this looks like it's in much more detail.

Cheers! :icon_cheers:


----------



## kelbygreen

looks great will try it out for sure the K&E one was great this one looks good to


----------



## ianh

Found an error on the BIAB worksheet the Start of Boil Volume calc is incorrect.

On the BIAB worksheet Copy cell M15 to cell M13 should fix the problem.

cheers

Ian


----------



## Bretto77

Save yourself some trouble and get Beersmith2. Brad has included full support for BIAB as well as heaps of other features that I'm guessing would take you ages to develop yourself. Considering the support Brad gives to acknowledge Aussie brewers and this forum in particular, we should all get in behind him and support his excelent software product. 

Brett



ianh said:


> Hi
> 
> As some of you may be aware I have moved on from Kits & Extracts to BIAB, so in the process of doing so I developed a spreadsheet.
> 
> I specifically use a 40 litre Crown urn for brewing but the spreadsheet gives the option to chose a crown urn or a pot.
> 
> The spreadsheet is still in it's development stage but I feel it can certainly be a guide to BIAB brewers.
> 
> Please read the notes worksheet before using.
> 
> The original version 1.0 was posted on BIABrewer and changes since include
> 
> Changed the IBU calculation so it includes the fermenter volume plus the trub.
> 
> Changed the dropdown lists so The ingredients are on their worksheets.
> 
> Changed how FG is calculated, Adjuncts now have a fermentabilty factor.
> 
> Changed how the brewhouse efficiency is calculated.
> 
> Added a water temp factor for those who use hot water to fill their urn or pot.
> 
> The spreadsheet is unprotected so be careful what you delete but you can change what you will, for example on the BREWS worksheet if you use a hydrometer only, just hide the Refractometer columns.
> 
> Comments,criticisms & suggestions appreciated.
> 
> cheers
> 
> Ian


----------



## vykuza

There's room for more than one project Bretto. I can't believe how rude your reply is after all the effort ianh has put in to this.

Ianh - thanks for putting in the yards for making this spreadsheet for BIAB brewers.


----------



## keifer33

I agree what a useless comment. The more projects on the go the better. Its only going to benefit the community by having a diverse selection of programs.

Good work on the spreadsheet ianh will be handy to have at work to fiddle around with.


----------



## Nick JD

It's easier to learn how to brew beer than to learn how to use Beersmith.


----------



## Lord Raja Goomba I

Nick JD said:


> It's easier to learn how to brew beer than to learn how to use Beersmith.



+1


----------



## ianh

Bretto77 said:


> Save yourself some trouble and get Beersmith2. Brad has included full support for BIAB as well as heaps of other features that I'm guessing would take you ages to develop yourself. Considering the support Brad gives to acknowledge Aussie brewers and this forum in particular, we should all get in behind him and support his excelent software product.
> 
> Brett



It's no trouble Brett, I enjoy doing it and can adapt it the way I want plus I have it tweaked for my Crown urn. Whilst I have not played with Beersmith 2 the previous version left a lot to be desired, put a kilo of sugar in a recipe then try a kilo af lactose, look at the FG, not sure whether this has been fixed in version 2.

cheers

Ian


----------



## aaronpetersen

Ian, you deserve a medal or a Knighthood or something. All rise for Sir Ian :icon_cheers:


----------



## joshuahardie

Very impressive work mate.

Well Done


----------



## dago001

Well done IanH on getting this up and going. Your other spreadsheet was great, and having seen this in the development stage, I know this will be as good as the K & K version. It's good that you have the time on your hands to be able to do this. I hope the garden hasn't suffered too much.
Cheers
LagerBomb


----------



## Bang

thanks ianh for adding this to the site this will come in handy


----------



## Lodan

More man love for you ianh :wub: 
The K&E spreadsheet helped me greatly, look forward to following the development of the new spreadsheet as i move into BIAB


----------



## Tanga

Yoink!

Thanks. I'm not BIAB yet but might play around with this to see how it works. It's great to see some software that works with Linux.


----------



## Silo Ted

Excellent initiative, will take a closer look when I am not on my netbook. Had a quick squizz, and maybe I culd offer a suggestion? For many people the BIAB concept is great for brewing in small spaces and with less gear. I suspect a lot of BIAB'ers also no-chill, so how about a tick box option for NC? 

My pet peeve with software is that I cant have the recipe on file looking the same as the process (ie my 45 addition is 60, my in-cube is 15 etc). 

Just a thought, but regardless it's great to see people doing things for the community. If it's as good as what people say about your kits worksheet, then BIAB brewers will hve an excellent tool t their disposal, for free.


----------



## ianh

Silo Ted said:


> Excellent initiative, will take a closer look when I am not on my netbook. Had a quick squizz, and maybe I culd offer a suggestion? For many people the BIAB concept is great for brewing in small spaces and with less gear. I suspect a lot of BIAB'ers also no-chill, so how about a tick box option for NC?
> 
> My pet peeve with software is that I cant have the recipe on file looking the same as the process (ie my 45 addition is 60, my in-cube is 15 etc).
> 
> Just a thought, but regardless it's great to see people doing things for the community. If it's as good as what people say about your kits worksheet, then BIAB brewers will hve an excellent tool t their disposal, for free.



Thanks

Had a look at doing this, some people only change the flavour and aroma hop times rather than all, could add an option that would add say 15 minutes to all calcs on selecting NC.

I do BIAB with NC but partly get around the problem by using hop bags which I remove at end of boil, then add any aroma hops to the fermenter.

cheers

Ian


----------



## Tanga

+1 to a no chill option. That'd be great. I can't look atm because I'm on my phone. Can someone tell me if we can do smaller batches? My urn is only 20L.

EDIT: Oops, sorry Ian. Didn't see your last post. This is so great. I can't wait to get home and check it out.


----------



## bus680

I am moving to BIAB with an urn soon. This looks great. I cant wait to see the finished product.


----------



## Shed101

Love this too. Wish I could get the ultimate functionality running it on a Mac ... so may have to install BootCamp or somesuch workaround.

I'm an utterly sad bastard and reckon it would be useful to have a pricing column too ... shouldn't be too difficult :icon_vomit:


----------



## going down a hill

Great work Ian. Your KandE version really helped me get my head around how the brewing process worked. Then you go and make a BIAB version with me recently converting to BIAB. Your a good man, thanks.


----------



## no-grey nomad

Hi Ian, this thing is awesome.

Just a quick question... How do you save recepies? I have a mac at home and use the work computer as least I can so macros are a bit beyond me. So if you could, a little walk through would be very much appreciated for the windows based PC at work.

Cheers mate, top job.


----------



## ianh

no-grey nomad said:


> Hi Ian, this thing is awesome.
> 
> Just a quick question... How do you save recepies? I have a mac at home and use the work computer as least I can so macros are a bit beyond me. So if you could, a little walk through would be very much appreciated for the windows based PC at work.
> 
> Cheers mate, top job.



Sorry for delay in replying just back from 4 days bushwalking.

Some people have got a project libary error when trying to save recipes, not sure what error you get. Not sure about the Mac but this may help for the work computer.


If you open the spreadsheet. Hit ALT & F11 should bring up visual basic.

In the visual basic Window go to the Tools Menu and select References I have ticked

Visual Basic for Applications
MS Excel 12.0 Object Library
OLE Automation
MS Office 12.0 Object Library
MS Forms 2.0 Object Library
MS Word 12.0 Object Library

I am using Excel 2007 so if using a different version will get a number other than 12.0, hope that fixes the problem.

Hopefully will have updated vesion shortly, time is the problem.

cheers

Ian


----------



## Amber Fluid

ianh said:


> Found an error on the BIAB worksheet the Start of Boil Volume calc is incorrect.
> 
> On the BIAB worksheet Copy cell M15 to cell M13 should fix the problem.
> 
> cheers
> 
> Ian



Is this still required to be done with the current download?
Cheers


----------



## Lord Raja Goomba I

Ian, have you thought of converting this to ODS format, given both excel is capable of using it, and it's an open standard (ISO) and fully compatible with my programs and operating systems?

Well done.

Goomba


----------



## ianh

Amber Fluid said:


> Is this still required to be done with the current download?
> Cheers



Presume that was for the previous version, attached is the lastest version that I been promising for awhile. Fixed some calculations, added a factor for ibu's when no chilling (though I No Chill I remove the hop bags at the end of boil so I don't use the factor) also added a factor which changes the FG based on mash temperature. Changed the Hop worksheet so now everything is in the comments. Can't think what else.

View attachment BIAB_Beer_Designer1.2.xls


cheers

Ian


----------



## ianh

Lord Raja Goomba I said:


> Ian, have you thought of converting this to ODS format, given both excel is capable of using it, and it's an open standard (ISO) and fully compatible with my programs and operating systems?
> 
> Well done.
> 
> Goomba



Thanks. No I had not thought of doing this. I tend to find that the there are problems running the macros whenever you try to put the spreadsheet in another format. For example take the Make Recipe macro, one of the things it does is to copy the Brewday worksheet and paste it as a comment on the Brews worksheet. To do this (and it's the only way I found) the macro opens MS Word in the background copies the info to Word then copies it from MS Word into the comment on the Brews Worksheet, the macro then closes MS Word.

One of the reasons the spreadsheet is unprotected is so other people may use it in whatever applications they want.

cheers

Ian


----------



## Amber Fluid

Thanks for the new version.

Great work mate!!


----------



## Muscovy_333

Great spreadsheet. 
Hats off for the efforts.
This is how you take ownwership of the art!


----------



## datamike

ianh said:


> Thanks. No I had not thought of doing this. I tend to find that the there are problems running the macros whenever you try to put the spreadsheet in another format. For example take the Make Recipe macro, one of the things it does is to copy the Brewday worksheet and paste it as a comment on the Brews worksheet. To do this (and it's the only way I found) the macro opens MS Word in the background copies the info to Word then copies it from MS Word into the comment on the Brews Worksheet, the macro then closes MS Word.
> 
> One of the reasons the spreadsheet is unprotected is so other people may use it in whatever applications they want.
> 
> cheers
> 
> Ian



Yes, macros rarely work from app to app. But designing in .ods would allow anyone to use it, instead of just those who purchase Microsoft Office. 

OpenOffice is free for everyone - just download and go.

It's very popular here in the US. Maybe not so much in Australia...

Anyway, it looks great!

Michael


----------



## Lord Raja Goomba I

datamike said:


> Yes, macros rarely work from app to app. But designing in .ods would allow anyone to use it, instead of just those who purchase Microsoft Office.
> 
> OpenOffice is free for everyone - just download and go.
> 
> It's very popular here in the US. Maybe not so much in Australia...
> 
> Anyway, it looks great!
> 
> Michael



Popular here too. I run it on my windows 7 computer at work, my linux machines at home and can recommend it to anyone that owns a mac (rather than Office for Mac or that stupid Mac program Numbers, which isn't compatible with anything, unless you "export" to excel and it never renders right). It allows me to share documents with anyone - it works in office, but if someone doesn't have office (or a legal copy of it), I recommend them to d/l open office.

That's why I asked the question - as the macros don't all work. I can do what I need to do manually, but it's a shame to have all those macros not working across all platforms.

Good job, nonetheless.

Goomba


----------



## nala

ianh said:


> Presume that was for the previous version, attached is the lastest version that I been promising for awhile. Fixed some calculations, added a factor for ibu's when no chilling (though I No Chill I remove the hop bags at the end of boil so I don't use the factor) also added a factor which changes the FG based on mash temperature. Changed the Hop worksheet so now everything is in the comments. Can't think what else.
> 
> View attachment 49314
> 
> 
> cheers
> 
> Ian



Ian, I have just downloaded the latest version.
On the main page -- Row H5, I have a note in Red which says ADJUST THE VOLUMES OF CO2.
Should this be there ?
Love the programme and the new version. Well done.


----------



## Shifter

Looks like I'll have to get Open Office as my Mac won't allow it to work very well at all. Never the less, huge amount of work for which we thanks kindly.


----------



## ianh

nala said:


> Ian, I have just downloaded the latest version.
> On the main page -- Row H5, I have a note in Red which says ADJUST THE VOLUMES OF CO2.
> Should this be there ?
> Love the programme and the new version. Well done.
> View attachment 49324



Hi Nala

Thanks. Just means the CO2 volumes cell M18 are not within the style range CO2 range.

cheers

Ian


----------



## ianh

Shifter said:


> Looks like I'll have to get Open Office as my Mac won't allow it to work very well at all. Never the less, huge amount of work for which we thanks kindly.



Sorry Shifter but I understand the macros will not run in OO.

cheers

Ian


----------



## Lord Raja Goomba I

It doesn't work - the macros that is - all that well, but the sheet itself, drop downs, etc work great.

Goomba


----------



## Shifter

Just checked my Crown Urn which has a concealed element against the spreadsheet numbers. My Urn diameter is exactly the same as the number quoted in the spreadsheet.

The numbers I get are: 10ltrs = 115mm, 20ltrs = 225mm, 30ltrs = 335mm. Measurements taken with tap water and a steel tape against the Urn seam.

This corresponds with 1ltr giving an average of 11.3mm. The spreadsheet gives 11m with an empty initial volume cell. 

Spreadsheet values are 10 ltrs = 123mm, 20ltrs = 235mm, 30ltrs = 347mm

Question is does your Urn have a concealed element and is there a difference in volume between the two which could account for the disparity? Or, maybe I cocked up my volume measurements.

I downloaded Open Office or at least Libre Office which is apparently the same thing re-visited.


----------



## ianh

Shifter said:


> Just checked my Crown Urn which has a concealed element against the spreadsheet numbers. My Urn diameter is exactly the same as the number quoted in the spreadsheet.
> 
> The numbers I get are: 10ltrs = 115mm, 20ltrs = 225mm, 30ltrs = 335mm. Measurements taken with tap water and a steel tape against the Urn seam.
> 
> This corresponds with 1ltr giving an average of 11.3mm. The spreadsheet gives 11m with an empty initial volume cell.
> 
> Spreadsheet values are 10 ltrs = 123mm, 20ltrs = 235mm, 30ltrs = 347mm
> 
> Question is does your Urn have a concealed element and is there a difference in volume between the two which could account for the disparity? Or, maybe I cocked up my volume measurements.
> 
> I downloaded Open Office or at least Libre Office which is apparently the same thing re-visited.


Hi Shifter

My urn is not perfectly round, diameter varies from 334mm to 340mm. I got the numbers by adding 6 litres of water by weight to cover the concealed element and measured this using a steel rule against the side of the urn at 78mm. Using the formula the concealed element accounts for about 10-11mm, I used 11mm in the calculation. Then each additional litre is based on the formula =1/(PI()*0.1675^2) where the .1675 is half the 337mm diameter. 

I would suggest how you are measuring the 10 litres in volume is where the error occurs. If you take 10 litres based on 11.3mm per litre thats 113mm which only leaves 2mm for the effect of the concealed element, if you go to 20 litres thats about 225mm nothing for the volume of the concealed element.

Yes OO does not handle the macros, I tried and saved as an .ods file, this wiped the macros and the dropdown lists.

cheers

Ian


----------



## Shifter

Ian,

Cause you are right. The concealed element is slightly domed and not parallel with the bottom of the Urn. Having just checked my Urn is not round either, so your measurements would appear to be correct. I'll have to re mark my urn. 

Forgive me I'm only an engineer.

Thanks for your feedback and the spreadsheet, which although the macros don't work, is still worthwhile.

Cheers,

Graham.


----------



## BobtheBrewer

ianh said:


> Hi Shifter
> 
> My urn is not perfectly round, diameter varies from 334mm to 340mm. I got the numbers by adding 6 litres of water by weight to cover the concealed element and measured this using a steel rule against the side of the urn at 78mm. Using the formula the concealed element accounts for about 10-11mm, I used 11mm in the calculation. Then each additional litre is based on the formula =1/(PI()*0.1675^2) where the .1675 is half the 337mm diameter.
> 
> I would suggest how you are measuring the 10 litres in volume is where the error occurs. If you take 10 litres based on 11.3mm per litre thats 113mm which only leaves 2mm for the effect of the concealed element, if you go to 20 litres thats about 225mm nothing for the volume of the concealed element.
> 
> Yes OO does not handle the macros, I tried and saved as an .ods file, this wiped the macros and the dropdown lists.
> 
> cheers
> 
> Ian




+1
At last I seem to have gotten something right!


----------



## TroyNZ

ianh said:


> Sorry for delay in replying just back from 4 days bushwalking.
> 
> Some people have got a project libary error when trying to save recipes, not sure what error you get. Not sure about the Mac but this may help for the work computer.
> 
> 
> If you open the spreadsheet. Hit ALT & F11 should bring up visual basic.
> 
> In the visual basic Window go to the Tools Menu and select References I have ticked
> 
> Visual Basic for Applications
> MS Excel 12.0 Object Library
> OLE Automation
> MS Office 12.0 Object Library
> MS Forms 2.0 Object Library
> MS Word 12.0 Object Library
> 
> I am using Excel 2007 so if using a different version will get a number other than 12.0, hope that fixes the problem.
> 
> Hopefully will have updated vesion shortly, time is the problem.
> 
> cheers
> 
> Ian


Ian,
Firstly, thanks for the effort you have put into this spread sheet, I used your Kit spreadsheet and am now moving to BIAB.
I have opened the visual basic library and found that the MS Word 12.0 Object Library is not ticked but, when I ticked it and clicked "OK", I was told that it was not loaded (or something like that) I tried to load it but couldn't. now when I go back to the visual basic library there is now a line that says "MISSING: MS Word 12.0 Object Library" 
I have tried copying the sheet but I just get "ERROR IN LOADING DLL".
Any Ideas??
Troy


----------



## Shed101

Hi Ian. Using your excellent tool for the second time today (my second ever BIAB).

Can you tell me what the difference between cells K16 and I25 in the BIAB sheet are?

They are both labelled as 'End of boil wort SG' and one even specifies to measure before adding adjuncts, yet I have quite different figures ... there's surely a simple explanation, but I must be a lighter shade of simple!


----------



## ianh

Shed101 said:


> Hi Ian. Using your excellent tool for the second time today (my second ever BIAB).
> 
> Can you tell me what the difference between cells K16 and I25 in the BIAB sheet are?
> 
> They are both labelled as 'End of boil wort SG' and one even specifies to measure before adding adjuncts, yet I have quite different figures ... there's surely a simple explanation, but I must be a lighter shade of simple!


Hi Shed101

Thanks. Cell K16 is the predicted End of Boil SG (yellow cell is a calculation) from all the information that has been entered on the spreadsheet, I25 requires an input (light blue cell,changed by user), insert your actual End of Boil SG here and it calculates the actual End of Boil efficiency. As we are interested in the Mash efficiency of the grains, the sample should be taken before any adjuncts are added at the end of the boil as that's the way the calculation was made to work.

The efficiency used in the recipe design, cell C7 is set at 75% if after a few brews, your average efficiency is different then you should change this value. I found my first brew had the lowest efficiency, then it settled down around 75%. You record the efficiency for each brew and enter in column K on the BREWS worksheet

Hope that makes sense.

cheers

Ian


----------



## Shifter

Ian,

I have been having huge problems with water volumes. I have measured my Urn again and find the diameter as you do 337mm average. When putting 6 ltrs of water in the bottom of the urn I get 70mm of water. Measured with a steel rule against the seam of the urn with tap water. Should I change any of the formula to adjust for the 70 mm instead of 78mm??


----------



## Shed101

ianh said:


> As we are interested in the Mash efficiency of the grains, the sample should be taken before any adjuncts are added at the end of the boil as that's the way the calculation was made to work.
> 
> The efficiency used in the recipe design, cell C7 is set at 75% if after a few brews, your average efficiency is different then you should change this value. I found my first brew had the lowest efficiency, then it settled down around 75%. You record the efficiency for each brew and enter in column K on the BREWS worksheet
> 
> Hope that makes sense.
> 
> cheers
> 
> Ian



Thanks Ian - definitely makes sense. As i'm starting out i've knocked efficiency back to 70% ... although post brew I see I actually hit 74%, I guess that's my benchmark for my next brew!

However because my (funky old fashoned) recipe required the adjuncts post-mash but in the boil my measured SG at end of boil throws things out and teh calc. says I got 83%. Never mind, now you've explained it .

Not that i would want to cause you any extra work ... but if on the MAIN sheet it was possible to include adjuncts in the boil or as 'proper' adjuncts ...


----------



## ianh

Shifter said:


> Ian,
> 
> I have been having huge problems with water volumes. I have measured my Urn again and find the diameter as you do 337mm average. When putting 6 ltrs of water in the bottom of the urn I get 70mm of water. Measured with a steel rule against the seam of the urn with tap water. Should I change any of the formula to adjust for the 70 mm instead of 78mm??



Hi Shifter

A formula like =IF($E$4="Crown Urn",K5/(PI()*($G$4/2000)^2)+11,K5/(PI()*($G$4/2000)^2)) is in cells K6 and K8 on the BIAB worksheet, change the 11 to 3 in both cells so the formula becomes =IF($E$4="Crown Urn",K5/(PI()*($G$4/2000)^2)+3,K5/(PI()*($G$4/2000)^2)). A similar but different formula is in cells M13, M15, E24 and E25 on the BIAB worksheet again change the number 11 to 3 and see how you go.

Hopefully may catch up at the Beer Festival next Saturday.

cheers

Ian


----------



## Shifter

Ian,
Thanks for that. I'll give it a try and revert. I am also going to put the spreadsheet on a Windows XP computer. I love Macs but they do have limitations for this type of thing.
Probably be at the Beer Feast on Friday as got plenty to do on Saturday.

Have a god one.


----------



## Shifter

Ian,

Spreadsheet loaded on to a Window XP laptop. Oh what joy to see it all work as designed. Excellent bit of software. A credit to you mate!


----------



## ianh

Shifter said:


> Ian,
> 
> Spreadsheet loaded on to a Window XP laptop. Oh what joy to see it all work as designed. Excellent bit of software. A credit to you mate!



Thanks Shifter, glad you can now see and access all the spreadsheet functions.

cheers

Ian


----------



## Pim Muter

Hi Ianh
Ive been using your speadsheet for a while, works great much easier to use than the real software.
I have two minor points you might consider if possible, can the BIAB page be saved with each recipe? as i use defferent mash temps and get different efficeincy for different recipies at the moment im manually changing them each time but it gets confusing.
And secondly drop down talbes on the inventory page would speed things up a lot. im no excel wizard so cant figure how to do myself.

Anyway love your work and may the beer flow freely.

Thanks

Pim Muter


----------



## robbo5253

Hey Pim,
to get the inventory to drop down, select Cell B12 in the main screen (or any blank grain option). Select copy from the top menu.
Then go to the inventory sheet and select the first cell below grains, select paste.
Then move your mouse to the bottom right hand corner of the cell to get the small cross, then simply drag down so you have enough fields.
Repeat for Hops(B22), Adjuncts (B16) & Yeast (J18).
With the yeast, once you have pasted it into the inventory sheet, select the blank one before dragging it down, this just saves doing each one manually.

Cheers

Robbo


----------



## Pim Muter

Thanks Robby 
As easy as that ay
Any brilliant ideas on saving getting the sheet to save biab page, yeast used and carbonation levels???

Would be very helpful

Ciao

Pim


----------



## ianh

Hi Pim Muter

Sorry for not replying, been away for a few days.

The yeast and mash temperature are already stored as part of the Recipe. To add other things to the Recipe would require a bit of rewriting.

What you could do is where you have made and saved a recipe is to edit the comments on the BREWS worksheet associated that brew. That way you could retain and retrieve any information about the brew.

cheers

Ian


----------



## Pim Muter

Hi Ian 
Ive got another question if you dont mind, would it be possible to write in a section for mash ratios ie. litres per kilo? would be real handy as i use different ratios for a lot of my brews at the moment im calculating mash separately then using your sheet.

Any ideas would be appreciated 

Best regards

Pim Muter


----------



## ianh

Hi All

Been saying I'll upload the latest version for awhile, so here it is

View attachment BIAB_Beer_Designer_v1.3.xls


Changes since V1.2

Fixed a calculation error on the BIAB worksheet.

Fixed the macro error for subtracting hops from the inventory.

Added FWH to hop boil times, can be used with chill or no chill.


Cheers
Ian


----------



## Oakers

Thanks Ian....much appreciated. I've just cut and paste across recipes from my v1.2. seems to work fine. Something that other users should note is that you have tweaked the settings in the BIAB set-up sheet. If users are using these as a default or have tweaked for their own circumstances then they will need to copy their old settings from v1.2. In a future version maybe you could add a return to default settings button.  I wonder if any other users would like to share recipes that they have entered. In particular I am thinking of some of the well-known and loved recipes from the AHB database?

Again, thanks for your work.

Cheers,
Oakers.


----------



## ianh

Oakers said:


> Thanks Ian....much appreciated. I've just cut and paste across recipes from my v1.2. seems to work fine. Something that other users should note is that you have tweaked the settings in the BIAB set-up sheet. If users are using these as a default or have tweaked for their own circumstances then they will need to copy their old settings from v1.2. In a future version maybe you could add a return to default settings button.  I wonder if any other users would like to share recipes that they have entered. In particular I am thinking of some of the well-known and loved recipes from the AHB database?
> 
> Again, thanks for your work.
> 
> Cheers,
> Oakers.



Hi Oakers

Thanks. Yes I changed the strike factor from 0.30 to 0.32 and the absorption factor from 0.50 to 0.48 to adjust things for my own set up. People will need to determine the factors on the BIAB worksheet for their own set ups. All the blue cells are for inputs.

Best way is to average the results across a number of brews (minimum 5) to determine the factors.

cheers

Ian


----------



## hellbent

A work of art Ianh and congratulations on your effort. 

I have just one problem here, you say adjustments can be made with anything on the blue lines, well it happens that I cant make any adjustments to the fermenter volume, grain weight, or for that matter anything on the blue lines. I have tried with Open Office and trial MS Excell for no result, any help or ideas?


----------



## AndrewQLD

After a few requests I've pinned this topic at the top of the all grain and Partials forum, as always thanks Ian for all your work.

Cheers
Andrew


----------



## keifer33

hellbent said:


> A work of art Ianh and congratulations on your effort.
> 
> I have just one problem here, you say adjustments can be made with anything on the blue lines, well it happens that I cant make any adjustments to the fermenter volume, grain weight, or for that matter anything on the blue lines. I have tried with Open Office and trial MS Excell for no result, any help or ideas?



You need to enable the macros for it to run. Might be a yellow line with 'Enable Macros'. If not you can go into the trust centre and enable permanently.


----------



## ianh

OK Crusty has found an error, when saving a new recipe with No Chill it does not include the No Chill (I only use chill so didn't find it).

Two ways to fix, easy way is to go to the recipes and put an N in the cell above the yeast. Need to do that for each new No Chill Recipe, existing recipes are OK.

Permanent fix requires changing the saverecipe macro, to bring up macros hold down the ALT key and hit F11, then Tools Macros and select saverecipe macro

Near the end of the saverecipe macro should find these lines

'transfer recipe data
z = 22
Do
If z > 11 Then
ActiveCell.Offset(z, 0) = sname(z): ActiveCell.Offset(z, 1) = swt(z): ActiveCell.Offset(z, 2) = stime(z)
ElseIf z > 2 And z < 11 Then

the If z > 11 Then needs to be changed to If z > 9 Then

I think that should fix the problem


----------



## Crusty

ianh said:


> OK Crusty has found an error, when saving a new recipe with No Chill it does not include the No Chill (I only use chill so didn't find it).
> 
> Two ways to fix, easy way is to go to the recipes and put an N in the cell above the yeast. Need to do that for each new No Chill Recipe, existing recipes are OK.
> 
> Permanent fix requires changing the saverecipe macro, to bring up macros hold down the ALT key and hit F11, then Tools Macros and select saverecipe macro
> 
> Near the end of the saverecipe macro should find these lines
> 
> 'transfer recipe data
> z = 22
> Do
> If z > 11 Then
> ActiveCell.Offset(z, 0) = sname(z): ActiveCell.Offset(z, 1) = swt(z): ActiveCell.Offset(z, 2) = stime(z)
> ElseIf z > 2 And z < 11 Then
> 
> the If z > 11 Then needs to be changed to If z > 9 Then
> 
> I think that should fix the problem



Followed the permanent fix guideline.
I changed the if z > 11 then to if z > 9 then
Problem solved & recipes are now saving in the option you choose, chill or no chill.
Great work Ian.


----------



## stux

ianh said:


> Hi All
> 
> Been saying I'll upload the latest version for awhile, so here it is
> 
> View attachment 59643
> 
> 
> Changes since V1.2
> 
> Fixed a calculation error on the BIAB worksheet.
> 
> Fixed the macro error for subtracting hops from the inventory.
> 
> Added FWH to hop boil times, can be used with chill or no chill.
> 
> 
> Cheers
> Ian



Hehe, the 2012 release 

Happy New Year


----------



## bigbanko

How do you add inventory.

When selecting the inventory button and select Add Inventory I get runtime error 94 - Invalid use of null.


----------



## parktho

How did you come up with the factor 0.994837 to convert from points per pound per gallon into metric?


----------



## ianh

parktho said:


> How did you come up with the factor 0.994837 to convert from points per pound per gallon into metric?


Hi parktho

Sorry I don't remember and I can't find a website, if I google the value of the factor it comes up with a website which requires a sign in. It was originally used in my Kit & Extract Spreadsheet for speciality grains.


----------



## taztiger

Hi Folks

Can someone tell me what i have to do when transferring recipes between versions. I have seen the big red warning telling me not to add anything straight into the recipes tab, so where do i cut and paste to?


----------



## ianh

taztiger said:


> Hi Folks
> 
> Can someone tell me what i have to do when transferring recipes between versions. I have seen the big red warning telling me not to add anything straight into the recipes tab, so where do i cut and paste to?


It should be Ok, just make sure the first lot of individual recipes go down to row 23. The warning was put there because extra lines were added to the recipes between some older versions.


----------



## taztiger

ianh said:


> It should be Ok, just make sure the first lot of individual recipes go down to row 23. The warning was put there because extra lines were added to the recipes between some older versions.



Ok will try that.

Thanks again Ian.


----------



## Drew

I'm doing my first AG in a long time, and this spreadsheet looks really fun useful 

Does "Approx Mash Volume" include the water added from Sparging?


----------



## ianh

Drew said:


> I'm doing my first AG in a long time, and this spreadsheet looks really fun useful
> 
> Does "Approx Mash Volume" include the water added from Sparging?


The spreadsheet is intended for BIAB thus does not include sparging.


----------



## joel_cabban

hey guys sorry to dig up an old thread, but i'm getting my brew gear out of storage, and looking at getting into the BIAB this time, is this program (or perhaps an update) still available?

cheers
Joel


----------



## Lord Raja Goomba I

This is still available, and beermate (runs best on Windblows) is also available, free, does BIAB and no-chill. Both are great.

Goomba


----------



## Mainiac

Ianh

I love the KK sheet it tought me a lot about brewing kits.
I am just starting my BIAB build and look forward to using this sheet.


Thanks again Ianh


----------



## troyedwards

I am using Beersmith 2, and I find it ok for transferring recipoies from brewbuilder on the HB store I use.

I don't think I fully use BS2 to its full extent as I don't really give a toss about percentage efficiency and water loss and every other factor than can be recorded. 

I do BIAB and so I guess what are the advantages of this spreadsheet over the BS2 program as Iam for all things simple and easy, and if it turns out the xls file is easier to use then I will make the switch?


----------



## Cynic13

Hi Ianh
How do I go about adding Pale 2-row to the Grains tab?


----------



## ianh

Cynic13 said:


> Hi Ianh
> How do I go about adding Pale 2-row to the Grains tab?


Hi Cynic

Welcome to the forum.

You just need the EBC and potential values for Pale 2 Row grain then just insert a row in the Grains worksheet and the name and values..
Can do that for any ingredients.


----------



## OzPaleAle

A bit of BIAB on Heston tonight, blessed by a priest no less.


----------



## jotaigna

I saw that ozpale. When Heston popped the fermenter lid and just scooped with a glass to taste, I cringed hard!


----------



## Barbarossa

Has anyone had any luck with this spreadsheet on a mac or openoffice? I've tried on both and can't get it to work at all. For example, when changing the beer style in the drop down menu nothing changes in the grains cells, or when changing the fermenter volume nothing else changes.


----------



## meathead

Great software, thanks for providing it
Is it possible to scale recipes up with it?
Sorry if that's been answered


----------



## ianh

Not sure how you want to scale up but if you select pot rather than urn you can basically put in any size for your initial volume.

Sorry for delay replying but I'm O/S on holiday ATM.


----------



## solipsist

Hey Ianh. I really appreciate the work you've put into this spreadsheet. Is there any way to add more lines for hops? I'm fine with reading/editing macros if you give me the basic gist of where to look.

Cheers.


----------



## ianh

Thanks. To add more hop lines

On Main worksheet copy down the last hop row - hop columns A to H for the number you want to add.
You will then need to alter IBU formula in cell K14
You then need to add the Spin Buttons to the new D & F columns (Developer - Insert - Spin buttons), not sure whether you number each spin button or its done autmatically.
You then need to add macros for each spin button. Developer - Macros - sheet1 should find hop button macros for weight and time, just need to copy and change cell references and spin button number.
On the Brewday worksheet you will need to insert and some more lines for hops.

You need need to edit the PasteComment macro find line Sheets("brewday").Range("b1:i50").Copy and change the i50 value to incorporate the additional hop rows.

You need to edit the makerecipe macro. This where most changes need to be made.
You will need to add some more sname variable values for your extra hop rows and then you need to alter the section underneath after the comment
' because you can have the same hops in any of the 8 slots, need total for each one
you need to total the weight of each hop so the changes will depend on the number of rows you added, gets more complicated the more rows you add.
In the inventory bit of the macro there are two loops For x = 2 to 22 you will need to change the 22 value in each ( 22 + No of extra rows)
Then there is a line near the end of the macro ActiveCell.Offset(0, 3).Value = need to add the extra sname variables created. This just totals the hops to add to the Brew worksheet.

I think that's it, good luck.


----------



## poggor

Hi Ian - great spreadsheet- I am running it on a mac and it all seems to be fine except the "make recipe" function. when i click it says it's removing the ingredients from the inventory but then i get an error message "runtime error 9 subscript out of range" and nothing is added to the brews sheet. 

seems to be this line it is objecting to:

sheets("brewday").Range("b1:i50").Copy

i know nothing about visual basic
any ideas?

cheers!

g


----------



## ianh

Hi poggor

Thanks. At that point in the macro it calls MS word in the background to copy the Brewday sheet info onto the Brew sheet as a comment. So you need both Excel and Word on the computer to get it to run properly.

That's the only way I could find of copying part of a worksheet to a comment.

If you go to near the end of the makerecipe macro and put a ' in front on the Call PasteComment line it should by pass that bit.

cheers

Ian


----------



## poggor

ok that worked to disable the comment
so what is meant to get pasted into the brews sheet when you do the make recipe macro? im only getting the first few columns filled?

g


----------



## ianh

poggor said:


> ok that worked to disable the comment
> so what is meant to get pasted into the brews sheet when you do the make recipe macro? im only getting the first few columns filled?
> 
> g


What normally happens is the macro takes the information from the Brewday worksheet and pastes it as a comment in cell C on the Brews worksheet. Giving you a record of every brew you do.

The only way I found of doing this was to copy the info, paste into MS Word (which the macro opens in background) then copy and paste from Word back into Excel as a comment.

Ian


----------



## poggor

Thanks ian, like i said, the spreadsheet is amazing- i wish i had your excel skills! It' a shame the macro kept crashing for me with the out of range error. Still, I can just fill in those cells in the "brews" sheet manually right?


cheers

g


----------



## matt s

Hi Ian, 

Is there anyway to add extra room for multiple hop additions to the spreadsheet? I am doing a large and varied IPA and need more room for different hop additions. 

Cheers, 

Matt


----------



## nvs-brews

ianh: thanks for this..
i prob dont use it to all its capability BUT its the best ive found..

Got me my 1st BIAB recipe, had more of a boil of than expected so gravity was higher and volume was down but yeah.. cant wait to keg it!

Cheers to you!
:beer:


----------



## ianh

Hi Matt

Two ways alter spreadsheet as per post 88 (could send solipsist a pm see if he's done it) or if you have a number of hops that are added at the same time you could add new hops to the Hop worksheet that are a combination of Hops, just have to work out a weighted average AA%.

Cheers

Ian


----------



## ianh

Congrats on the first BIAB nvs-brews. If its too high in OG could always add some water.


----------



## nvs-brews

1 thing i forgot to check with yeast amount, i did pitch more but 12hrs later so i may have strained the yeast a bit...
maybe that could be something you include?

all good ianh, i may end up with a 7.2% (depending on FG) instead of a 5.8% but either way it would suit the style (APA)


----------



## nvs-brews

Regardless still very appreciative 
champion!


----------



## Glomp

Hi Ian,

Have only joined this forum over the last week or two. If you would like help in sexing this up I am pretty good at excel. Not as sex in excel but making it very easy to navigate etc.


----------



## tomdavis

Sexcel?



Glomp said:


> Hi Ian,
> 
> Have only joined this forum over the last week or two. If you would like help in sexing this up I am pretty good at excel. Not as sex in excel but making it very easy to navigate etc.


----------



## solipsist

Glomp said:


> Hi Ian,
> 
> Have only joined this forum over the last week or two. If you would like help in sexing this up I am pretty good at excel. Not as sex in excel but making it very easy to navigate etc.


Sounds like some good intentions - but my experiences tell me that it can be very difficult to sex up a spreadsheet without losing key functionality or changing the way people have gotten used to using it, particularly if you are not the original designer with knowledge of how the spreadsheet evolved. Having said that, open sourcing and progress are also good!


----------



## ianh

Hi Glomp

Welcome to the forum. Feel free its not protected.

I am away for 2 weeks.

cheers

Ian

edit one thing I have been asked for is extra hop values.


----------



## mattyg8

After visiting grape and grain brew in a bag demo im looking forward to using this spread sheet...loved using you kit one.

Seen as I have a 40L crown urn concealed element its safe to say I should be able to leave everything in biab setup page the same as yours?


----------



## ianh

mattyg8 said:


> After visiting grape and grain brew in a bag demo im looking forward to using this spread sheet...loved using you kit one.
> 
> Seen as I have a 40L crown urn concealed element its safe to say I should be able to leave everything in biab setup page the same as yours?


 Hi mattyg8

Yes you should be able to do initially, but you should look to working out values for your set up. I am still making slight adjustments to my values after 150 brews.


----------



## spires

Hi Ian
I am new to the forum and BIAB but have used your kits and bits spreadsheet many times. I have the same set up you use but no chill into some old 17 litre fresh wort cubes. My question is how do I account for 4 litres of top up water I add to my fermenter to give 21 litres. Can I calculate ingredient additions using 21 litre ferment volume and then change to 17 litre to get water volumes and temperatures.


----------



## ianh

Hi spires and welcome to the forum. I use a 40L crown urn, if you do then wondering why you are using 17 litre cubes rather than a 20 litre (which hold about 24litre), seems a lot of mucking about to me.

On the spreadsheet use 21 litres for the recipe then change to 17 litres for the volumes and temps. You will need to take an SG of the 17litres for efficiency calculation then another SG for your 21litres for the fermentation.
Should work, your IBU will be slightly lower because of the higher wort SG.


----------



## spires

Thanks Ian
I plan on getting some 20 litre cubes but had some old fresh wort cubes around and thought I could put them to use. Do you know which 20 litre cube is the best for hot wort storage?


----------



## ianh

I use the blue Willow ones http://www.supercheapauto.com.au/online-store/products/Willow-Water-Carry-Can-Blue-20-Litre.aspx?pid=13453#Recommendations

There are a number of threads on AHB just search for no chill cubes.


----------



## spires

Thank you for your help - will pick one up on the weekend.
One final question (?) - what do you set your ferment volume at for these cubes and is it easy to expel air if the cube isnt full.


----------



## ianh

I have my volume to fermenter set at 23 litres and trub losses set at 3.0 litres (I take out 1.5 litres for a yeast starter) which gives me about 24.5 litres at end of boil to go into cube. Takes about 24litres to fill the cube. I then leave about a litre in the cube when I transfer to the fermenter, I have the cube on an angle and just drain it, leaves about a litre.

I rack to secondary and cold condition this gives me 22 litres of packaged beer (19 litre keg plus 2 x 1.5 litre bottles).


----------



## stux

Easy to expel all air from these cubes too, just put jerry against wall, put towel on knee, tilt cube back so cap is horizontal, lean into cube with knee and undo cap a bit. Redo cap when liquid comes out.


----------



## ridge runner

@ what point in the brew day do u take the 1.5L out for the starter? Pre boil ,mash ,post boil ?


----------



## ianh

gillie said:


> @ what point in the brew day do u take the 1.5L out for the starter? Pre boil ,mash ,post boil ?


Post Boil, I put some of the wort into the cube then 500ml in a flask for the starter and another 1L into a container then continue to fill the cube. After the yeast starter has been fermenting for 24 hours the 1L has been added and fermented for a further 72 hours the starter is then stored at 1C till needed.


----------



## ridge runner

Cheers ianh


----------



## Dae Tripper

Ianh, have you thought about making a 3V version? The kit and biab ones have been super helpful but 3ving with the biab software is getting a bit difficult. I tried the Brewers Friend one and it can't comprehend a -5min nochill hop addition, super lame.


----------



## Mat B

Hi ianh. Great spreadsheet!

I'm using a 'keggle' made from a stainless steel keg as my brew vessel. Shout I be selecting 'pot' as my vessel on the BIAB tab, then adding my specific diameter?

Cheers,

Mat


----------



## ianh

Mat B said:


> Hi ianh. Great spreadsheet!
> 
> I'm using a 'keggle' made from a stainless steel keg as my brew vessel. Shout I be selecting 'pot' as my vessel on the BIAB tab, then adding my specific diameter?
> 
> Cheers,
> 
> Mat


Thanks Mat B. Because a keggle does not have straight sides you would have to work out the height for volume. But if you brew similar beers then could measure the diameter at that water level and use that as a pot diameter. Not sure how it will work but you give it a try.


----------



## Mat B

That's a good idea! I'll do just that. Cheers!


----------



## Waratah67

Hi Ian, My spreadsheet won't allow me to save new recipes. I get an error: "can't find project or library". appreciate any advice


----------



## ianh

Waratah67 said:


> Hi Ian, My spreadsheet won't allow me to save new recipes. I get an error: "can't find project or library". appreciate any advice


Hi, see post 27 of this thread, hope that helps.

Cheers


----------



## Waratah67

Thanks Ian. It did,

The last one on the list read "MISSING-Microsoft Word. 16".

I unchecked it. It seems to save again ok.


----------



## ianh

The spreadsheet uses MS Word in the background to copy the information on the Brewday worksheet onto the Brews worksheet as a comment, just in case you are wondering why you need MS Word. It was the only way I could find of doing it.


----------



## beef

as someone new to allgrain that completely blows my mind how any of that works, would the calculations work on the 35l robobrew? again sorry if this has already been covered


----------



## Mat B

beef said:


> as someone new to allgrain that completely blows my mind how any of that works, would the calculations work on the 35l robobrew? again sorry if this has already been covered


Yep. All you need to do it enter your robobrew's parameters on the BIAB tab and you should be good to go. I think you'd need to set the type to crown urn in cell E4. Robobrew is just a fancy urn as far as I can see. 

Once you've done that you can enter your recipe details and volume on the first tab and try and get it close to a style on the chart on the right. Select your style from cell H3.


----------



## ianh

Just be aware that selecting Crown urn includes a factor for the volume of the raised concealed element and this is probably different for the Robobrew.


----------



## beef

Thanks guys ill have a look over the next couple of days


----------



## beef

ianh said:


> Just be aware that selecting Crown urn includes a factor for the volume of the raised concealed element and this is probably different for the Robobrew.


ok so i have had a play and all seemed to be pretty accurate to what i got on brew day, how do i go about saving the recipe? it says something about macros? if i save it will it do anything to the program?


----------



## ianh

Just hit the "Save Recipe" button on the Main worksheet and it should save to the Recipes worksheet. If you have added stuff to the Inventory worksheet and hit the "Make Recipe" button it should put the info on the Brews worksheet.

Note I think that the macros only work with Excel, not with Libre or Open Office.


----------



## elvey

Hey Mate. A brew buddy got me onto the spreadsheet. Great work, thanks a lot. I'm writing a web based recipe calculator and wonder if you'd mind me using your ingredient lists? Also wondering what formula you've used for the hop calculations? I'm using Tinseth (http://realbeer.com/hops/research.html) and was thinking I'd just add some time for the chube but wondered if you'd found anything better? 

Source is up at https://github.com/melvey/biab-calculator/tree/master/src if you want to have a play


----------



## ianh

Hi elvey

Welcome to the forum.That's what I used for the hop calculations plus a time factor for No Chill. Feel free to use what bits you want, that's why it is not protected.

Cheers

Ian


----------



## Stouter

Ian seriously, bugger Bob Dylan, I reckon you should be the recipient of a Noble Prize, or a bloody knighthood or something such at least.
Your selfless contribution and willingness to help people is a combination which is a rare trait in today's society.
Surely you're aware of how much time and energy you've saved brewers!? From someone with very bad math, measurements skills, and computer skills, thank you.

Sir Ian  Respect.


----------



## mattyg8

Started using the no chill to play around with the IBU's what value would you put in for flame out without whirlpool or with whirlpool? Also cube hop?


----------



## Mat B

I’m going to do a brew over the next week that will feature home grown hops heavily. What’s the rule for adding these to the recipe builder? I’m going to dry my flowers for storage. I know there’s a pellet / flower field. Do I select flowers only when using wet hops and leave it as pellet if I’m using dry hops?

Cheers!


----------



## ianh

If I remember right the difference between pellets and flowers is in the IBU calculation, pellets are 110% flowers 100%. I assume moisture is a large part of the difference. So I would go flowers for wet and pellets for dry.


----------



## Mat B

Thanks mate!


----------



## dago001

Good to see you're still plugging away at this. I have just started brewing again after a shortish period of inactivity. We must catch up for a beer soon.
Cheers
David


----------



## dave_hl

Making good use of the spreed sheet - thanks very much for designing it! I was initially trying to find a really basic spreed sheet which just logged beers I had brewed but this is next level!

I am wondering if it is an easy edit for me to make the Hop schedule longer when creating a recipe. The APA (averagely perfect American) IPA I'm entering has a list about 14 long for hop additions!

I can see that to get the total IBU it's a simple SUM function that I can extend, but when I click and drag the hop list to make it longer, the buttons don't work anymore, and the huge IF statement to calculate the IBU for that hop addition also doesn't work.

Cheers.


----------



## ianh

You should be able to extend the Hop schedule, you need to copy columns A to H (H is where the calcs are done) down to the rows required.

Need to change the Sum formula in Cell K14.

The buttons won't work as each one requires a separate small macro, but you can enter weights and times manually.

I think that should work. Another possible option is if you are adding a number of hops at a particular time you can add a new hop to the Hop page that is a combination of the hop IBUs.

Cheers


----------



## dave_hl

Ah, great stuff. Yes I didn't copy all the way from A to H. Thanks for the tech support! I love that this is fully open source - being able to ad grains and hops.

Thanks
Dave


----------



## Drover's dog

Sheer genius...


----------



## Beannoir

Ian, 

I would just like to say thanks for your hard work, knocked over 4 brews now and spot on for my urn hasn't missed a beat. 

Huge thanks you

Ben


----------



## opercularia

Thank-you *so* much Ian, I have been using this spreadsheet for many years now and it has helped me produce many satisfying beers. Unfortunately, mostly due to my own stupidity, it has produced many duds. It is time to delete all of the duds, but I am having problems. As I delete recipes, I am losing information on the hops for other recipes.

The first picture shows you what has happened after a deletion. The yellow boxes show unaffected recipes. The red boxes show what is missing in some recipes after a recipe has been deleted. The blue box shows missing information that is shown in a capture taken before a deletion (shown in red in picture 2).

It appears that the information is being deleted as the page is being compacted (recipe moved to fill right-hand column from left-hand column below). It appears to affect all of the recipes in the right hand column. Each deletion corrupts more recipes.

Has anybody else had this problem, or has my copy become corrupt?


----------



## Hangover68

Is anyone using the spreadsheet in openoffice instead of Excel ?


----------



## ianh

Hi opercularia I am not at home atm, should be back in a few days and will look at the problem.

Hangover68 the spreadsheet cannot be used in openoffice as the macros will not run.

cheers
Ian


----------



## Hangover68

ianh said:


> Hi opercularia I am not at home atm, should be back in a few days and will look at the problem.
> 
> Hangover68 the spreadsheet cannot be used in openoffice as the macros will not run.
> 
> cheers
> Ian



Thanks, i assumed that would be the case.


----------



## ianh

Hi opercularia. Had a chance to look at the recipe deletion problem.The recipes are grouped in sets of 10, it appears not to move recipe 11 to 10 correctly.
To fix the problem you need to get access to the "deleterecipe" macro how to do this depends on the Excel version.
You need to edit the deleterecipe macro, near the end of the macro you will see the following line

Range(ActiveCell.Offset(24, -36), ActiveCell.Offset(39, -34)).Cut Range(ActiveCell, ActiveCell.Offset(23, 2))

The 39 value needs to be changed to 46

Range(ActiveCell.Offset(24, -36), ActiveCell.Offset(46, -34)).Cut Range(ActiveCell, ActiveCell.Offset(23, 2))

then save the spreadsheet.
Hope that fixes trhe problem.
Cheers
Ian


----------



## opercularia

ianh said:


> Hi opercularia. Had a chance to look at the recipe deletion problem.The recipes are grouped in sets of 10, it appears not to move recipe 11 to 10 correctly.
> To fix the problem you need to get access to the "deleterecipe" macro how to do this depends on the Excel version.
> You need to edit the deleterecipe macro, near the end of the macro you will see the following line
> 
> Range(ActiveCell.Offset(24, -36), ActiveCell.Offset(39, -34)).Cut Range(ActiveCell, ActiveCell.Offset(23, 2))
> 
> The 39 value needs to be changed to 46
> 
> Range(ActiveCell.Offset(24, -36), ActiveCell.Offset(46, -34)).Cut Range(ActiveCell, ActiveCell.Offset(23, 2))
> 
> then save the spreadsheet.
> Hope that fixes trhe problem.
> Cheers
> Ian



Thanks for your quick reply Ian. I think it did the trick. I only tried one deletion and it worked correctly. Now to repair my recipes and finish deleting the duds.

Thanks again, you're a champ!


----------



## TONY VAN DER ZANDEN

ianh said:


> Hi opercularia I am not at home atm, should be back in a few days and will look at the problem.
> 
> Hangover68 the spreadsheet cannot be used in openoffice as the macros will not run.
> 
> cheers
> Ian


G'day Ian,
Would love to try your spread sheet, I was sent a link by a member on this site, but the link went to a error page.
Could you please send me a more current link so I have a crack.
Many thanks,

Tony


----------



## ianh

Hi Tony
The link in the first post in this thread is the current one.
Cheers
Ian


----------



## NicolasZA

Raising this thread from the dead.. Not sure if anybody is still using the spreadsheet, but I need some advice here.
If I change the ferment volume, should the grain weight not change?


----------



## ianh

No the initial gravity will change.


----------



## NicolasZA

Thanks for your answer ianh. Second question then. Is there a way to modify the batch size with the spreadsheet? My current recipe is for 20 liters, I want to change the batch to 10 liters. Is this something the spreadsheet can handle?


----------



## ianh

No you cannot just change the batch size saved in recipes. When you save recipe it just saves the value thats in the Ferment Volume cell.


----------



## NicolasZA

ianh said:


> No you cannot just change the batch size saved in recipes. When you save recipe it just saves the value thats in the Ferment Volume cell.


Thanks ianh!


----------



## mattoo

Does anyone use brewmate ? It’s all I have used and found it excellent (and was a free download)


----------



## gezzanet

I’m still using Ian’s spreadsheet. Tweaked it for my biab setup and it’s very accurate and repeatable. Thanks Ian


----------



## Hangover68

Since working from home i have now a full office suite thanks to work on my pc so opened the spreadsheet to have a look, i was thinking earlier today that some kind of inventory spreadsheet would be good and there it is right on the main page.
Excellent work and now i have all my on hand ingredients listed so i can keep track of it, i even made some additions to the yeasts and hops.

Anyone using this on an android device, would be handy to use on my tablet, i know there is Excel for Android but would prefer a free option.


----------



## NicolasZA

Thanks again for the spread sheet! Just finished my third brew and its starting to make sense now. Everything worked out exactly as planned.


----------



## Hangover68

Just started my 2nd brew and i've noticed the 2 things that dont seem correct (could be me) , the mash temp and time stays the same regardless of the style and the strike water temp isn't high enough ? I may just need to do some tweaks but otherwise i love it, makes it easy to repeat a recipe.


----------



## ianh

Hi. The mash temp and time are only manually set. The strike temp can be increased by increasing the value in the Factor for grain absorbtion cell.


----------



## Hangover68

ianh said:


> Hi. The mash temp and time are only manually set. The strike temp can be increased by increasing the value in the Factor for grain absorbtion cell.



Thanks Ian, i knew it would be something i did or needed to adjust.


----------



## NicolasZA

Hi Ian

Just read through the whole thread to make sure I haven't missed anything. On my BREWS sheet, I have no formula in the T and U columns. Can you let me know what it should be? I have downloaded the spreadsheet a couple of times. Still blank.


----------



## ianh

=IF(AND(ISNUMBER(R2),ISNUMBER(S2)),IF(ISNUMBER(AC2),(R2-S2)/7.45,(R2-S2)/7.45+0.4),"") in Cell T2
=IF(AND(ISNUMBER(R2),ISNUMBER(S2)),100*(R2-S2)/R2,"") in Cell U2
These Cells are copied down the Worksheet
Only get values if using hydrometer ie inputs in Cols R and S

Cheers

Ian


----------



## NicolasZA

Thank you very much!


----------



## Hendog

Yep, appreciate your hard work


----------



## Hangover68

Anyone know if its possible to merge 2 copies of the spreadsheet, i have a copy on my PC and a cop on a flash drive and would like to merge them.


----------



## ianh

All I can suggest is to Google "compare two excel files" and follow the Microsoft answer.


----------



## Hangover68

ianh said:


> All I can suggest is to Google "compare two excel files" and follow the Microsoft answer.


I did a search yesterday and the results were mostly for simple single tab worksheets, we have an excel guru at work so i might ask her when i'm next in the office.


----------



## ScoAussie

Hangover68 said:


> I did a search yesterday and the results were mostly for simple single tab worksheets, we have an excel guru at work so i might ask her when i'm next in the office.



You are looking to import and append into the excel sheet.








Appending Data from Excel • Genstat Knowledge Base


Sometimes data for separate categories or trials will have been entered on separate pages within a workbook. If the sheets are identically structured (same column names, same column types of factor/text, etc.) then these can be combined into a single spreadsheet. Even if columns have been named...



genstat.kb.vsni.co.uk





Hope this helps...


----------



## Hangover68

ScoAussie said:


> You are looking to import and append into the excel sheet.
> 
> 
> 
> 
> 
> 
> 
> 
> Appending Data from Excel • Genstat Knowledge Base
> 
> 
> Sometimes data for separate categories or trials will have been entered on separate pages within a workbook. If the sheets are identically structured (same column names, same column types of factor/text, etc.) then these can be combined into a single spreadsheet. Even if columns have been named...
> 
> 
> 
> genstat.kb.vsni.co.uk
> 
> 
> 
> 
> 
> Hope this helps...



When i looked at both side by side it was just easier to to copy the data across as there wasn't much difference.


----------

