putting together a spreadsheet

i have been waiting for beersmith mobil and thought I would put together a spreadsheet for now. I would like to be able to list my malt amounts by % and have the base malt field change to make up the difference, can anyone help me with this?

I've got a spreadsheet I put together. Feel free to use it, or copy the info from it. http://shantybrewery.blogspot.com/p/my-cheapo-brewsheet.html

It doesn't do exactly what you want, but you can work around it. In the "%" tab, fill in your specialty grains and sugars/adjuncts via percentage. In the % column for your base malt type =1-SUM(B16:C23,B26:C28). This will automatically fill in your base malt percentage.

shanty said:

I've got a spreadsheet I put together. Feel free to use it, or copy the info from it. http://shantybrewery.blogspot.com/p/my-cheapo-brewsheet.html

It doesn't do exactly what you want, but you can work around it. In the "%" tab, fill in your specialty grains and sugars/adjuncts via percentage. In the % column for your base malt type =1-SUM(B16:C23,B26:C28). This will automatically fill in your base malt percentage.

Click to expand...

checked out your link, it looks good but I wasn't working.

SO you want to put recipes in by percentage and have the weights populated for you, right? Should be easy enough.

I also have a spreadsheet, passed-down by a long-time brewer who got it from another, etc. I use it exclusively and it serves me well. Took some times getting it dialed-in for my system though.

One thing everyone should be aware of is potential extract of base malts, though. If you can get the lot info, do the math. Some malts are listed at 37 for instance when recent lots are coming out nearer to 35. Makes a big difference when you use 20lbs. of malt.

I think the issue with what you're asking is that without knowing the total pounds of grain in the bill, you can't come up with how many pounds 80% of the grain bill is, for example. 80% of what? 80% of how many total pounds? You have to start with total pounds or find a way to work off efficiency and OG in order to get the percentage.

I created a spreadsheet based on the info from "Designing Great Beers". It has worked well for me.
You start by entering your target volume and gravity, so it calculates total GU's.
Then enter your efficiency of your system, and percentages.
There's also a hops calculator, so you can get the IBU's right if your LHBS has different AA% than some recipe you may be trying to recreate.
It might seem a bit cryptic if you haven't read the book...but that's a good excuse to read the book!
I tried to upload it here but wouldn't let me.
Try this link:
https://docs.google.com/open?id=0BzneedOZ049Ba0VqTnVyVkpkUkk

killian said:

checked out your link, it looks good but I wasn't working.

Click to expand...

Try clicking on the icon just below "Thanks to my wife's server" not the "Download here", worked for me.

tom

killian said:

checked out your link, it looks good but I wasn't working.

Click to expand...

millstone said:

Try clicking on the icon just below "Thanks to my wife's server" not the "Download here", worked for me.

tom

Click to expand...

Not sure why it wasn't working. Both links go to the same address. Seems to be fine now.

tre9er said:

I think the issue with what you're asking is that without knowing the total pounds of grain in the bill, you can't come up with how many pounds 80% of the grain bill is, for example. 80% of what? 80% of how many total pounds? You have to start with total pounds or find a way to work off efficiency and OG in order to get the percentage.

Click to expand...

I designed my spreadsheet to take care of that: input wort volume, starting gravity, and percentage of each malt. Excel takes care of the rest.

killian said:

i have been waiting for beersmith mobil and thought I would put together a spreadsheet for now. I would like to be able to list my malt amounts by % and have the base malt field change to make up the difference, can anyone help me with this?

Click to expand...

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.

dbrewski said:

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.

Click to expand...

Or, as mentioned, volume, OG, and efficiency. That would break down each malt by it's extract and give a weight.

dbrewski said:

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.

Click to expand...

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.

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.

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.

dbrewski said:

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.

Click to expand...

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.

tre9er said:

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.

Click to expand...

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

dbrewski said:

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

Click to expand...

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.