IPB

Welcome Guest ( Log In | Register )

6 Pages V  < 1 2 3 4 5 > »   
Reply to this topicStart new topic
> Mead/Melomel Calculator Spreadsheet, and log/graph tool
Matt the Mead Ma...
post Dec 2 2006, 12:26 PM
Post #31


Brewmaster
*****

Group: Members
Posts: 45,736
Joined: 2-May 04
From: Pendleton, OR
Member No.: 2,119



QUOTE(MtnBrewer @ Dec 2 2006, 11:28 AM) *
I noticed the other day there is a bug in this spreadsheet. Specifically the BYO version of the Brix-to-SG calculator is always significantly lower than the others. The bug was in the source article so it's not your fault jjarmoc. (IMG:style_emoticons/brewboard/smile.gif) Here is the correct formula:

=1.000898+0.003859118*H2+0.00001370735*H2*H2+0.00000003742517*H2*H2*H2

That formula should go in cell H4.

Now it agrees exactly with ProMash and is within a couple of points of the others.



Thanks for finding the error! For me, BYO is cell H3 on my spreadsheet but I'm not sure if that's due to my previous edits to the spreadsheet.
Go to the top of the page
 
+Quote Post
MtnBrewer
post Dec 2 2006, 02:14 PM
Post #32


Brewmaster
*****

Group: banned
Posts: 9,637
Joined: 26-July 03
Member No.: 937



You're right, it is H3, not H4. I'll edit the original post. I had the formula wrong too. The formula I posted was the one for Primetab (which is also why I had the cell wrong). The one in my post is now correct.
Go to the top of the page
 
+Quote Post
Matt the Mead Ma...
post Jan 18 2007, 03:00 PM
Post #33


Brewmaster
*****

Group: Members
Posts: 45,736
Joined: 2-May 04
From: Pendleton, OR
Member No.: 2,119



I've put together a beta version of a spreadsheet to estimate the impact of fruit additions in a melomel. Specifically, what it calculates is the extent to which fruit lowers the ABV while contributing fermentables. This spreadsheet is a modification of jjarmoc's Mead_calc.xls spreadsheet.

I just threw it together during lunch today and I wouldn't be surprised if there are some errors in my calculations. Please feel free to try it out let me know if you find anything that needs changing!
Attached File(s)
Attached File  Melomel_calc.xls ( 26.5K ) Number of downloads: 1346
 
Go to the top of the page
 
+Quote Post
armagh
post Jan 18 2007, 03:24 PM
Post #34


Brewer
****

Group: Members
Posts: 444
Joined: 4-January 06
Member No.: 4,965



One question: What is the basis for the assumption that 15 lbs of fruit will contribute an estimated 1.5 gal of liquid?
Go to the top of the page
 
+Quote Post
hiddendragonet
post Jan 18 2007, 03:38 PM
Post #35


Brewmaster
*****

Group: Members
Posts: 1,987
Joined: 29-December 04
From: Madison, WI
Member No.: 3,234



Excellent! That's a great idea, Matt.

I have a question about the estimated SG after fruit addition. How did you come by the assumption of adding 1 point per pound in 5 gallons? Ok, wait, I think I just answered my own question. You are adding both points and volume and calculating based on that. Yes! This may not be precisely accurate, but by adding volume in there, you are accounting for the variable it seems most people neglect. Let's compare your calculator to my guesstimate from the other thread just for kicks.

Assume we have 5 gallons of 1.025 gravity mead that is mostly fermented, and to it we are adding 15 pounds of fruit with an estimated liquid volume of 1.5 gallons.

Your calculator gives me this-

SG when the must was racked to Secondary: 1.025
Volume of the must when racked to Secondary: 5.0
Number of pounds of fruit in the Secondary Fermenter: 15.0
Estimated Liquid Contribution of the Fruit: 1.5
Estimated Volume of must after fruit addition 6.5
Estimated SG After Fruit Addition: 1.040

Ok, we're figuring this a little differently from each other.

Let's assume that the fruit juice has a fairly high (for fruit) gravity of 1.050 rather than using 1 point per pound.

My math goes like this, and it could very well be WRONG:

SG = (SG1 * V1 + SG2 * V2) / (V1 + V2)

SG = (25 * 5 + 50 * 1.5) / (5 + 1.5)

SG = (125 + 75)/6.5

SG = ~1.031


So if my assumption on the SG of the fruit juice is close, then it only added .4 points per pound, and 1.5 gallons in volume.

I just had to talk myself through all that. Does it make sense? Now that I have a refractometer, I should be able to do some better calculations next time, including the SG of the fruit.



QUOTE(armagh @ Jan 18 2007, 02:24 PM) *
One question: What is the basis for the assumption that 15 lbs of fruit will contribute an estimated 1.5 gal of liquid?

You'd definitely have to make some kind of judgement call on the volume addition, and it would depend on the fruit too. Most likely it would be less than that for 15 pounds, but a gallon might not be too far off.
Go to the top of the page
 
+Quote Post
Matt the Mead Ma...
post Jan 18 2007, 04:39 PM
Post #36


Brewmaster
*****

Group: Members
Posts: 45,736
Joined: 2-May 04
From: Pendleton, OR
Member No.: 2,119



QUOTE(armagh @ Jan 18 2007, 03:24 PM) *
One question: What is the basis for the assumption that 15 lbs of fruit will contribute an estimated 1.5 gal of liquid?

That assumption was based on a recent melomel I made with about 20 lbs of peaches and it gained about 2 gallons of volume. As you seem to be alluding, water content will be a huge variable. For example, peaches will impart more water than bananas or strawberries. That number is also based on my personal techniques, which does not include pressing or squeezing the lees to get any additional volume out of it. For that reason, you can adjust the calculation as you see fit.

Melomels have a huge number of variables - not only between fruits but also considering the qualities and characteristics of the fruit you end up with. There will never be a crystal-ball type of calculator. You'll have to adjust for tartness and tannins and whatnot. It's really an art. I just wanted to put together something that will give people a rough guideline.
Go to the top of the page
 
+Quote Post
Matt the Mead Ma...
post Jan 18 2007, 04:52 PM
Post #37


Brewmaster
*****

Group: Members
Posts: 45,736
Joined: 2-May 04
From: Pendleton, OR
Member No.: 2,119



QUOTE(hiddendragonet @ Jan 18 2007, 03:38 PM) *
I have a question about the estimated SG after fruit addition. How did you come by the assumption of adding 1 point per pound in 5 gallons? Ok, wait, I think I just answered my own question. You are adding both points and volume and calculating based on that. Yes! This may not be precisely accurate, but by adding volume in there, you are accounting for the variable it seems most people neglect. Let's compare your calculator to my guesstimate from the other thread just for kicks.

Yeah - it's a tough one to quantify and I wrestled a bit with how to do it. I had initially done it based on the amount of juice imparted and assumed the juice was 1.050. I came up with a lower number much closer to yours and I may go back to that formula. I could've sworn I read something by Ken Schramm where he used the 1 gravity point per pound of fruit rule but now I can't find the quote - so maybe that's another reason to go back to the other calculation.

Thanks for the input! (IMG:style_emoticons/brewboard/smile.gif)
Go to the top of the page
 
+Quote Post
hiddendragonet
post Jan 18 2007, 05:02 PM
Post #38


Brewmaster
*****

Group: Members
Posts: 1,987
Joined: 29-December 04
From: Madison, WI
Member No.: 3,234



QUOTE(Matt the Mead Maker @ Jan 18 2007, 03:52 PM) *
Yeah - it's a tough one to quantify and I wrestled a bit with how to do it. I had initially done it based on the amount of juice imparted and assumed the juice was 1.050. I came up with a lower number much closer to yours and I may go back to that formula. I could've sworn I read something by Ken Schramm where he used the 1 gravity point per pound of fruit rule but now I can't find the quote - so maybe that's another reason to go back to the other calculation.

Thanks for the input! (IMG:style_emoticons/brewboard/smile.gif)

Yeah... I think you might be right. If I recall, the book basically leaves out volume considerations of fruit and just talks about sugar additions. More experience with this aspect may have led Ken to his rumored return to "fruit in primary" techniques.

Not only that but 1.050 is probably on the high end! I doubt that many berries and stone fruits get that high even- only the sweetest would. Think apple cider - usually about 1.045ish and that's about as sweet as most fruits go. Grapes being the exception- one of the few fruits that can exceed 1.075.

Anyway, nice work!
Go to the top of the page
 
+Quote Post
Matt the Mead Ma...
post Jan 18 2007, 05:23 PM
Post #39


Brewmaster
*****

Group: Members
Posts: 45,736
Joined: 2-May 04
From: Pendleton, OR
Member No.: 2,119



QUOTE(hiddendragonet @ Jan 18 2007, 05:02 PM) *
Yeah... I think you might be right. If I recall, the book basically leaves out volume considerations of fruit and just talks about sugar additions. More experience with this aspect may have led Ken to his rumored return to "fruit in primary" techniques.

OK - you've convinced me. (IMG:style_emoticons/brewboard/wink.gif) I've uploaded a revised version of the spreadsheet.

QUOTE(hiddendragonet @ Jan 18 2007, 05:02 PM) *
Anyway, nice work!

Thanks - and thanks for the input, too!

EDIT: There was also a problem with the ABV calculation for the original must. I've updated that, now too. Looks like we're at version 3.0 (IMG:style_emoticons/brewboard/smile.gif)
Go to the top of the page
 
+Quote Post
hiddendragonet
post Jan 18 2007, 05:31 PM
Post #40


Brewmaster
*****

Group: Members
Posts: 1,987
Joined: 29-December 04
From: Madison, WI
Member No.: 3,234



Ahhh, love it!

It would be reasonably easy to determine the actual gravity of the fruit's juice with a refractometer, or by pressing out enough juice to take a hydro reading. One more variable eliminated.

Sometime I want to try pressing the juice and using that in primary, and reserving & freezing the pulp for use in secondary.
Go to the top of the page
 
+Quote Post
armagh
post Jan 19 2007, 08:11 AM
Post #41


Brewer
****

Group: Members
Posts: 444
Joined: 4-January 06
Member No.: 4,965



As you seem to be alluding, water content will be a huge variable.

No way around that. But this is a constructive step forward.
Go to the top of the page
 
+Quote Post
MtnBrewer
post Jan 19 2007, 03:33 PM
Post #42


Brewmaster
*****

Group: banned
Posts: 9,637
Joined: 26-July 03
Member No.: 937



Great job, Matt!

One comment: this spreadsheet has the same bug in the Brix -> SG converter that jjarmoc's original had. The bug is actually in the BYO article from which the conversion came. Here's the correct formula for cell H3:

=1.000019+(0.003865613*(H2)+0.00001296425*(H2*H2)+0.00000005701128*(H2*H2*H2))
Go to the top of the page
 
+Quote Post
Matt the Mead Ma...
post Jan 19 2007, 04:08 PM
Post #43


Brewmaster
*****

Group: Members
Posts: 45,736
Joined: 2-May 04
From: Pendleton, OR
Member No.: 2,119



Good catch, MtnBrewer! I've updated the spreadsheet accordingly.
Go to the top of the page
 
+Quote Post
Matt the Mead Ma...
post Feb 7 2007, 12:19 AM
Post #44


Brewmaster
*****

Group: Members
Posts: 45,736
Joined: 2-May 04
From: Pendleton, OR
Member No.: 2,119



I've put together a new version of the spreadsheet (see the first post in this thread for the download). Features of the new version:
1) The spreadsheet will now allow you to simultaneously use up to 4 different types of fruit and will calculate the sugar and water contributions of each fruit as well as their impact of on the must gravity and % ABV.
2) I've loaded the fruit sugar and moisture content of about 26 different fruits which you can now choose from drop-down menus.

As always, have fun and let me know if you find anything that needs fixing. (IMG:style_emoticons/brewboard/wink.gif)
Go to the top of the page
 
+Quote Post
jjarmoc
post Apr 9 2007, 06:24 PM
Post #45


Brewer
****

Group: Members
Posts: 462
Joined: 23-December 03
From: Hanover Park, IL (Chicago suburbs)
Member No.: 1,493



QUOTE(Matt the Mead Maker @ Feb 7 2007, 01:19 AM) *
I've put together a new version of the spreadsheet (see the first post in this thread for the download). Features of the new version:
1) The spreadsheet will now allow you to simultaneously use up to 4 different types of fruit and will calculate the sugar and water contributions of each fruit as well as their impact of on the must gravity and % ABV.
2) I've loaded the fruit sugar and moisture content of about 26 different fruits which you can now choose from drop-down menus.

As always, have fun and let me know if you find anything that needs fixing. (IMG:style_emoticons/brewboard/wink.gif)



Awesome additions Matt! I'm glad to see someone adding on to the sheet... I've been away from brewing for a while, and haven't done a mead in months so I'm not perusing the forums all that often. I saw their was lots of activity in this thread, and was really happy to see some additions! Having not done a melomel (yet) I'm not really familiar with your equations, but they'll definitely be useful!

Anyway, thanks for carrying the torch.
Go to the top of the page
 
+Quote Post

6 Pages V  < 1 2 3 4 5 > » 
Reply to this topicStart new topic
3 User(s) are reading this topic (3 Guests and 0 Anonymous Users)
0 Members:

 



RSS Lo-Fi Version Time is now: 19th August 2018 - 11:56 PM