putting together a spreadsheet - Page 2 - Home Brew Forums

 Home Brew Forums > putting together a spreadsheet

10-18-2012, 04:39 PM   #11
tre9er

Recipes

Jan 2012
Lincoln, NE
Posts: 4,371
Liked 242 Times on 197 Posts

Quote:
 Originally Posted by dbrewski Are you asking for Excel help? You mean you want to adjust the % of malt, and have the "pounds" of malt update automatically? Shouldn't be too hard. You will need one other part of the equation - a total weight. So you'd have to enter in "11 pounds" for the grain bill (5 gallon batch) and then if you entered 80% base malt, it would be fairly simple to spit out 8.8 lbs base malt. If I am understanding you correctly. I built my own spreadsheet and I enjoyed doing it. How to Brew and Designing Great Beers were amazingly helpful.
Or, as mentioned, volume, OG, and efficiency. That would break down each malt by it's extract and give a weight.
__________________
_________________________________
Skal!
Den Faaborg Bryggeri

Quote:
 Originally Posted by davekippen Open log Fermenting and gas-can secondary?? I am planning my next brew right now!!

10-18-2012, 04:45 PM   #12
killian
Recipes

Apr 2007
western/central new york
Posts: 1,506
Liked 14 Times on 14 Posts

Quote:
 Originally Posted by dbrewski Are you asking for Excel help? You mean you want to adjust the % of malt, and have the "pounds" of malt update automatically? Shouldn't be too hard. You will need one other part of the equation - a total weight. So you'd have to enter in "11 pounds" for the grain bill (5 gallon batch) and then if you entered 80% base malt, it would be fairly simple to spit out 8.8 lbs base malt. If I am understanding you correctly. I built my own spreadsheet and I enjoyed doing it. How to Brew and Designing Great Beers were amazingly helpful.
I have always done my water calculations by hand and I thought I would build a sheet for that. I just got an iPad and have been using the numbers app. I used how to brew for my other equations.

I will probably use this sheet mostly for liquor to grist and water volumes but I work on my recipes some times by thinking of the malt profile I'm looking for and plugging in malts from specialty malt / adjuncts to base malt.

it seems like it would be a simple equation like 5% + 5% + 5% + x% = 100% I just can't figure out how to build it.
__________________
damn I gotta brew something

10-18-2012, 04:54 PM   #13
dbrewski

Recipes

Oct 2011
Indeterminate
Posts: 1,011
Liked 52 Times on 46 Posts

It is simpler to build a spreadsheet from the bottom up, rather than the top down IMO. That means entering in pounds of each type of grain, and having the percent of grain update based on the weight of that grain vs. total grain weight. If you want the percent number higher, then add more grain until it gets where you want. As others will no doubt jump in to say, it gets more complicated from there if you also want to determine a recipe OG/FG.

10-18-2012, 05:22 PM   #14
BBL_Brewer

Recipes

Feb 2011
Kokomo, IN
Posts: 3,702
Liked 440 Times on 303 Posts

It really depends on how complicated you want the sheet to be. Trying to incorporate percentages into all the other math would get kind of hairy. I think the best approach would be to make this function independant of other calculations like gravity. Just make it to where you have cells under each grain amount (or on top whereever you want them) and then cacluate the percentage of each. So, once you enter all your grain amounts, you'll have a cell that sums up the total grain. Then for each cell linked to the individual grain amounts, you'll have this equation. =A1/B1 where A1 = grain amount and B1 = total grain. Format to percentage and then you'll see what the percentage of each is after you enter your inital recipe. Then you can go back and make adjustments until you get what you're looking for. You could do it the other way and have it automatically generate your grain amounts, but then the software would not only have to adjust the percentage of each grain but also the total grain amount so that you hit your OG. This would require some VBA coding, but could be done.
__________________
Slots Down Brewery
Stick with the plan....not the sparge.

Never Ending Liquid Yeast - How to Farm Yeast and Freeze it.

10-18-2012, 05:42 PM   #15
tre9er

Recipes

Jan 2012
Lincoln, NE
Posts: 4,371
Liked 242 Times on 197 Posts

Quote:
 Originally Posted by dbrewski It is simpler to build a spreadsheet from the bottom up, rather than the top down IMO. That means entering in pounds of each type of grain, and having the percent of grain update based on the weight of that grain vs. total grain weight. If you want the percent number higher, then add more grain until it gets where you want. As others will no doubt jump in to say, it gets more complicated from there if you also want to determine a recipe OG/FG.
I can totally see where the OP is coming from though. Sometimes I want to clone someone's recipe but my batch size, efficiency, etc. are different. Now I'm doing math in my head trying to figure out "If he used 9lbs. of 2-row for 5 gallons, and I'm brewing 9 gallons, cross-multiply, then divide"

If I had what the OP is talking about, I'd just use the percentages (usually specified in recipes, especially from BeerSmith) and the OG, my batch size and the poundage would update.
__________________
_________________________________
Skal!
Den Faaborg Bryggeri

Quote:
 Originally Posted by davekippen Open log Fermenting and gas-can secondary?? I am planning my next brew right now!!

10-18-2012, 07:02 PM   #16
dbrewski

Recipes

Oct 2011
Indeterminate
Posts: 1,011
Liked 52 Times on 46 Posts

Quote:
 Originally Posted by tre9er I can totally see where the OP is coming from though. Sometimes I want to clone someone's recipe but my batch size, efficiency, etc. are different. Now I'm doing math in my head trying to figure out "If he used 9lbs. of 2-row for 5 gallons, and I'm brewing 9 gallons, cross-multiply, then divide" If I had what the OP is talking about, I'd just use the percentages (usually specified in recipes, especially from BeerSmith) and the OG, my batch size and the poundage would update.
Yep, I hear you. I do this mainly for my mash and sparge volumes, which starts with the batch size, takes the grain bill weight and spits out qts/lb and total gallons for each (I batch sparge).

10-18-2012, 07:04 PM   #17
tre9er

Recipes

Jan 2012
Lincoln, NE
Posts: 4,371
Liked 242 Times on 197 Posts

Quote:
 Originally Posted by dbrewski Yep, I hear you. I do this mainly for my mash and sparge volumes, which starts with the batch size, takes the grain bill weight and spits out qts/lb and total gallons for each (I batch sparge).
Oh yeah, my spreadsheet does that, too. It's actually like BeerSmith in a way...it does everything, I mean everything. I can't take credit for it since it was passed on to me, but it's thorough.
__________________
_________________________________
Skal!
Den Faaborg Bryggeri

Quote:
 Originally Posted by davekippen Open log Fermenting and gas-can secondary?? I am planning my next brew right now!!