Working on my own brewing spreadsheet/software Need Input

[Bigarcherynut]

I have been brewing for a few years and the last few years have gone BIAB with my home built E-Kettle. After struggling to find the best software have been working with 3 programs. Bru'n Water, Priceless Brewing and BeerSmith 2. I've tried them all.

I find myself all over the place when entering my data so I recently created an Excel Spreadsheet that I can enter my brewing data and notes as I brew. This has made it easier to get the data I need to track how my build works and how I'm doing with the whole process. As I'm understanding more about grain, potential, water chemistry, my system, water volume and such, I have added more and more to my spreadsheet. Water volume had been a struggle because multiple software programs gave me different amounts and became confusing on what to enter because of doing BIAB and I do not sparge. My spreadsheet is becoming a working program that works directly with my build.

Where I'm at now is making cells to automatically calculate efficiencies. What I would like to know is which ones are really necessary to have and are there any straight forward formulas for each. Not looking to get in the market for competing with everyone with software, just looking to have one that works for me and my system.

Any help would be appreciated.

 

[aeviaanah]

Check this out-
http://www.braukaiser.com/wiki/index.php?title=Understanding_Efficiency

 

[doug293cz]

Check this out-
http://www.braukaiser.com/wiki/index.php?title=Understanding_Efficiency

One thing to watch out for with braukaiser's formulas is that many of them only work correctly with metric units.

If you want to reverse engineer a spreadsheet to get formulas for conversion efficiency, lauter efficiency, and mash efficiency, you can find one here.

Brew on

 

[Bigarcherynut]

One thing to watch out for with braukaiser's formulas is that many of them only work correctly with metric units.

If you want to reverse engineer a spreadsheet to get formulas for conversion efficiency, lauter efficiency, and mash efficiency, you can find one here.

Brew on

Thanks much for the spreadsheet. I'll work my way through it today and see what you are doing. I'm pretty handy with Excel so I thought I'd attempt tp create a sheet that matches my brewing system.

I worked with Priceless brewing a couple of years ago. I had just started and with extremely hard well water installed a RO system and took time off from brewing to get a good handle on all aspects of brewing before starting up again a few months ago. I know you have had some input with Priceless Brewing and see that the program has had several updates moving slightly away of just BIAB. I played with the newest version and if I'm entering the correct data from my last couple of batches ended up with these efficiencies.

Measured Conversion Eff. 90.5% & 94%
Measured Lauter Eff. 88% & 88.8%
Measured Mash Eff. 79.6% & 83.5%
Measured Brewhouse 82.1% & 83.1%

Not sure how these numbers are but at least they are similar.

Will most likely have some questions for you.

Thanks again.

Bill

 

[pricelessbrewing]

Hello again,

So if you're looking for the *correct* method, then dougs spreadsheet (using braukaisers formulas) are the right way. However they're also a bit convoluted to follow.

Here's a link to another google sheet with some simplified formulas using gravity and WORT volume, what I'm calling the dilution model using the common approach of total gravity points = wort volume * wort gravity. It's pretty simple to follow and only required 1 use of plato-sg conversion. It's not quite as accurate, but is quite close. Tab 1 is an input field, and a comparison between brauksier/doug293cz method, mine, and brewersfriend (which has an error in the conversion efficiency formula).

https://docs.google.com/spreadsheets/d/1s3loaIKyS9tvwolLzqbe5HrEfL7vDvJIJZEo9gPkefA/edit?usp=sharing

 

[Bigarcherynut]

Hello again,

So if you're looking for the *correct* method, then dougs spreadsheet (using braukaisers formulas) are the right way. However they're also a bit convoluted to follow.

Here's a link to another google sheet with some simplified formulas using gravity and WORT volume, what I'm calling the dilution model using the common approach of total gravity points = wort volume * wort gravity. It's pretty simple to follow and only required 1 use of plato-sg conversion. It's not quite as accurate, but is quite close. Tab 1 is an input field, and a comparison between brauksier/doug293cz method, mine, and brewersfriend (which has an error in the conversion efficiency formula).

https://docs.google.com/spreadsheets/d/1s3loaIKyS9tvwolLzqbe5HrEfL7vDvJIJZEo9gPkefA/edit?usp=sharing

Thanks for the spreadsheet and like Doug's will give it a look.

I spent a good part of the day going through Doug's sheet. Wow, just about like looking at a 4000 point PLC control logic program. I needed a couple of colds ones when I got done. Got a good idea what's going on and now I can see why many like using your program, entering a little data and getting the results. I saw a few calculations that I may add to mine but also see that I'm on the right track with what I've done so far.

Seeing I'm only doing only BIAB and no sparge I'm keeping it simple. I would just like to enter my data once and at the end of the brew session have my 4 efficiencies you have in your program. If I find I'm not willing to put the time in to this I'm glad I can use your program. I've just had problems hitting the correct volumes, strike temps and such from other programs. I'm not sure if my build has something to do with this but it pushed me to start working on my own program.

Could you look at my last post and tell me what you think of my last 2 sets of numbers for efficiencies. I'm pretty sure I got all the correct data into your sheet. I made a squeeze board so I've got my grain absorption down to .046 gal./pound. My kettle is a 20 gallon oval which I think spreads my grain in a thinner layer making my mash easy to work and requires a lower strike temp. Still a work in progress but getting better each time I brew. I can taste it in my the finished product.

Thanks so much for your input and help.

 

[Kent88]

Haven't done much with it myself, but I've heard good things about BrewTarget.

Not sure if you're able to code more than spreadsheets, but if so it is open source, you could start tweaking it to your own liking.

 

[pricelessbrewing]

Could you look at my last post and tell me what you think of my last 2 sets of numbers for efficiencies.

The numbers look good. Conversion is in a typical range at 90-94%. The first set is a bit uncertain though, as mash eff % is lower than brewhouse. Brewhouse should always be lower than mash, as the only difference is kettle losses. Most likely your volume measurement was off slightly.

 

[Bigarcherynut]

If you want to reverse engineer a spreadsheet to get formulas for conversion efficiency, lauter efficiency, and mash efficiency, you can find one here.

Brew on [/QUOTE]

Doug, I've gone through your spreadsheet and have a good idea what's going on with it. Took a bit of chasing but got it. One thing is I don't see your conversion efficiency calculations. Am I missing something?

Thanks much.

 

[doug293cz]

Yeah, I don't explicitly calculate conversion efficiency. The way to get at conversion efficiency is to use the "Goal Seek" capabilities of the spreadsheet program. The steps are as follows:

1. Select the "Goal Seek" function
2. For the Formula Cell, select "K48"
3. For the Target Value, enter your mash wort/first runnings SG
4. For Variable Cell, select "B16" which is the Conversion Efficiency cell
5. Execute the goal seek.

Let me know if you have any other questions.

Brew on

 

[Bigarcherynut]

Yeah, I don't explicitly calculate conversion efficiency. The way to get at conversion efficiency is to use the "Goal Seek" capabilities of the spreadsheet program. The steps are as follows:

1. Select the "Goal Seek" function
2. For the Formula Cell, select "K48"
3. For the Target Value, enter your mash wort/first runnings SG
4. For Variable Cell, select "B16" which is the Conversion Efficiency cell
5. Execute the goal seek.

Let me know if you have any other questions.

Brew on

Doug,

I saved the formula into Excel. In the goal seek function in Excel the 3 input sections are, Set cell, To Value and By changing cell. I've read the help section and I'm not sure which one of the 3 items you gave me go into which input. Any help would be appreciated.

 

[doug293cz]

Doug,

I saved the formula into Excel. In the goal seek function in Excel the 3 input sections are, Set cell = Formula Cell, To Value = Target Value and By changing cell = Variable Cell. I've read the help section and I'm not sure which one of the 3 items you gave me go into which input. Any help would be appreciated.

I work in LibreOffice rather than MS Office, and the terms are a little different for Goal Seek (see equivalents above), but things work the same.

Brew on

 

[Bigarcherynut]

I work in LibreOffice rather than MS Office, and the terms are a little different for Goal Seek (see equivalents above), but things work the same.

Got it. Would have helped to remove the number in B16 so I could have seen Seek was working.

I see you use inputs I didn't know would have been in the program. For the time being I'm going to keep mine simple and use Priceless for calculating my efficiencies. I'm playing with yours on another sheet to see what I come up with. Leaving out some of the metric conversions.

Thanks much for your help.

 

[Big Monk]

I have been brewing for a few years and the last few years have gone BIAB with my home built E-Kettle. After struggling to find the best software have been working with 3 programs. Bru'n Water, Priceless Brewing and BeerSmith 2. I've tried them all.

I find myself all over the place when entering my data so I recently created an Excel Spreadsheet that I can enter my brewing data and notes as I brew. This has made it easier to get the data I need to track how my build works and how I'm doing with the whole process. As I'm understanding more about grain, potential, water chemistry, my system, water volume and such, I have added more and more to my spreadsheet. Water volume had been a struggle because multiple software programs gave me different amounts and became confusing on what to enter because of doing BIAB and I do not sparge. My spreadsheet is becoming a working program that works directly with my build.

Where I'm at now is making cells to automatically calculate efficiencies. What I would like to know is which ones are really necessary to have and are there any straight forward formulas for each. Not looking to get in the market for competing with everyone with software, just looking to have one that works for me and my system.

Any help would be appreciated.

We are no-sparge brewers for the most part (at LOB.com, i.e. our admins, myself, and Die Beerery) so we use a modified version of Kai's equations to predict Pre-Boil Gravity. I made a quick little calculator for you to check out:

http://www.********************/wp-content/uploads/2018/04/No-Sparge-Gravity-and-Efficiency.xlsx

It should be "Other" Office program friendly. It takes a less analytical and more empirical approach to predicting No-Sparge (i.e. First Wort) gravity.

Feel free to change it, poke at it, incorporate it, etc. It is a proven sheet and we use a form of it for every no-sparge brewday with very good accuracy. Obviously Volume entry and accurately entering conversion efficiency is key. If you do not know the extract potential of the grain used, just enter 76.64 (80% DBFG and 4% moisture to be consistent with Kai's assumptions). I have almost no MLT deadspace so I did not add that in there but you can easily add it. Just make sure it goes into the calculation for strike volume if you do.

 

[Bigarcherynut]

We are no-sparge brewers for the most part (at LOB.com, i.e. our admins, myself, and Die Beerery) so we use a modified version of Kai's equations to predict Pre-Boil Gravity. I made a quick little calculator for you to check out:

http://www.********************/wp-content/uploads/2018/04/No-Sparge-Gravity-and-Efficiency.xlsx

It should be "Other" Office program friendly. It takes a less analytical and more empirical approach to predicting No-Sparge (i.e. First Wort) gravity.

Feel free to change it, poke at it, incorporate it, etc. It is a proven sheet and we use a form of it for every no-sparge brewday with very good accuracy. Obviously Volume entry and accurately entering conversion efficiency is key. If you do not know the extract potential of the grain used, just enter 76.64 (80% DBFG and 4% moisture to be consistent with Kai's assumptions). I have almost no MLT deadspace so I did not add that in there but you can easily add it. Just make sure it goes into the calculation for strike volume if you do.

Thanks much. Finally just got some time to check it out. Is there a way to change it from metric to imperial? would like to run some of my numbers in the program to see how it compares.

When I done playing with the different programs should be able to finalize one just for my system

Thanks again.

 

[Big Monk]

Thanks much. Finally just got some time to check it out. Is there a way to change it from metric to imperial? would like to run some of my numbers in the program to see how it compares.

When I done playing with the different programs should be able to finalize one just for my system

Thanks again.

There is a conversion calculator in the second tab.