Wine gallons and proof gallons by weight calculation?

[goldfishhh]

Gosh. I don't quite know where to start other than asking here. Does anyone have an excel sheet which can calculate wine gallons and proof gallons by weight? For example, 35 pounds at 90% of proof (100%) equals 4.5 wine gallons or 4.0 proof gallons. The inputs would be weight and pct of proof and the outputs would be wine gallons and proof gallons.

Help Computer!

 

[HalfAce]

How do you arrive at 35 lbs. equalling 4.5 Wine Gals. and/or 4.0 Proof Gals.?

(What would 1 pound at 100% proof be? and 1 pound at 90 Proof?)

I'm not the best one here for formulas but if I see a pattern in the calculations then I can usually (ok... sometimes ) come up with something.

 

[Leith Ross]

Hello goldfishhh,

The easiest way would be to transcribe the following table:
27 CFR 30.62 - Table 2, showing wine gallons and proof gallons by weight.

Read more: http://cfr.vlex.com/vid/62-showing-wine-gallons-weight-19673186##ixzz0tcI03BAJ

Sincerely,
Leith Ross
​

 

[goldfishhh]

Well, the ATF has a chart which is an awful scan of a scan that I can use. Looking at the chart:

100% of proof for 100 pounds is 12.9 wine gallons and 12.9 proof gallons.
150% of proof for 100 pounds is 13.7 wine gallons and 20.5 proof gallons
200% of proof for 100 pounds is 15.1 wine gallons and 30.3 proof gallons


http://www.ttb.gov/foia/Gauging_Manual_Tables/Table_2.pdf

How do you arrive at 35 lbs. equalling 4.5 Wine Gals. and/or 4.0 Proof Gals.?

(What would 1 pound at 100% proof be? and 1 pound at 90 Proof?)

I'm not the best one here for formulas but if I see a pattern in the calculations then I can usually (ok... sometimes ) come up with something.

 

[HalfAce]

Wow. I'm not seeing anything there to work with that jumps out at me.
The first thing that comes to mind (and I'm reasonably sure it isn't the most efficient solution) would be to maybe make a data table of all those values and use a couple VLOOKUP formulas to get your results, but that looks like a LOT of manual entries to get the data table.

The good part is you should only have to do it once and I think it would work.

Other than that, we'll need someone a lot better at formulas than I am.
(and again, the good part is that shouldn't be too hard to find. )

(Aladin? Fairwinds? ...anyone???)

 

[goldfishhh]

I have to laugh and cry. Why oh why does the govt provide such a horrible resource - c'mon. A scan of a scan? Give me the tables in Excel or .TXT!

The other approach I was thinking of is because water and alcohol interact very differently at different proofs, I'm going to pick on a few proofs and then look at each weight and see if there is a ratio that jumps out. Then compare proof to proof to see what else jumps out. I'm hoping that by looking at one proof a pattern will develop.

D-

Wow. I'm not seeing anything there to work with that jumps out at me.
The first thing that comes to mind (and I'm reasonably sure it isn't the most efficient solution) would be to maybe make a data table of all those values and use a couple VLOOKUP formulas to get your results, but that looks like a LOT of manual entries to get the data table.

The good part is you should only have to do it once and I think it would work.

Other than that, we'll need someone a lot better at formulas than I am.
(and again, the good part is that shouldn't be too hard to find. )

(Aladin? Fairwinds? ...anyone???)

 

[goldfishhh]

Clearly, there is a relationship between proof and weight as it applies to Wine Gallons and Proof Gallons:

#/WG = WG ratio

I took 10 or so samples and then averaged all of the WG ratios. Taking the averaged ratio, I divided that into the # and amazingly enough, the numbers matched (rounded to the nearest 10th). Big question now is, how to apply this ratio concept to each proof (I've got 150 of them and don't quite want to do this all by hand)

100 proof						
100						
#	WG	PG	WG Ratio	PG Ratio	Calcd WG	Calcd PG
35.5	4.6	4.6	7.717391304	7.717391304	4.6	4.6
38	4.9	4.9	7.755102041	7.755102041	4.9	4.9
40.5	5.2	5.2	7.788461538	7.788461538	5.2	5.2
43	5.5	5.5	7.818181818	7.818181818	5.5	5.5
50	6.4	6.4	7.8125	7.8125	6.4	6.4
53	6.8	6.8	7.794117647	7.794117647	6.8	6.8
			7.780959058	7.780959058		
# to calc	Actual WG	Actual PG	 		Calcd WG	Calcd PG
50	6.4	6.4			6.4	6.4
75	9.6	9.6			9.6	9.6
100	12.9	12.9			12.9	12.9
125	16.1	16.1	 		16.1	16.1
150	19.3	19.3			19.3	19.3

I have to laugh and cry. Why oh why does the govt provide such a horrible resource - c'mon. A scan of a scan? Give me the tables in Excel or .TXT!

The other approach I was thinking of is because water and alcohol interact very differently at different proofs, I'm going to pick on a few proofs and then look at each weight and see if there is a ratio that jumps out. Then compare proof to proof to see what else jumps out. I'm hoping that by looking at one proof a pattern will develop.

D-

 

[HalfAce]

Is there any possible chance you can contact the ATF or somebody and get a copy of your data table in areal excel format? (I realize we're talking about the government here and therefore the more sense it makes the less likely they are to do it) but could that be something you can try?

Other than that, yes I see the ratio you mentioned but (if I'm reading your post right) how are the wine gallons & the proof gallons ending up the same?

 

[goldfishhh]

Govt - they say to use the PDF. Super!

Why is WG and PG the same. Well, the strange thing about 100 proof is it magically works out to be the exact same measurement for WG and PG. However as the proof increases, so does the ratios for WG.

Here is a snapshot from 121proof:

121 proof			 			
121						
	WG	PG	WG Ratio	PG Ratio	Calcd WG	Calcd PG
143	18.8	22.8	7.606382979	6.271929825	18.8	22.8
151	19.9	24	7.587939698	6.291666667	19.9	24.0
259	34.1	41.3	7.595307918	6.271186441	34.1	41.2
259.5	34.2	41.3	7.587719298	6.283292978	34.2	41.3
348	45.8	55.4	7.598253275	6.281588448	45.8	55.4
355	46.7	56.5	7.601713062	6.283185841	46.7	56.5
			7.596219372	6.280475033		
	Actual WG	Actual PG	 		Calcd WG	Calcd PG
50	6.6	8			6.6	8.0
75	9.9	11.9			9.9	11.9

and 156 proof

156 proof			 			
156						

233	32.2	50.2	7.236024845	4.641434263	32.2	50.2
256	35.4	55.2	7.231638418	4.637681159	35.3	55.2
360	49.7	77.6	7.243460765	4.639175258	49.7	77.6
43	5.9	9.3	7.288135593	4.623655914	5.9	9.3
73	10.1	15.7	7.227722772	4.649681529	10.1	15.7
138	19.1	29.7	7.22513089	4.646464646	19.1	29.7
			7.24201888	4.639682128		

50	6.9	10.8			6.9	10.8
75	10.4	16.2			10.4	16.2
100	13.8	21.6			13.8	21.6
125					17.3	26.9
150					20.7	32.3

Is there any possible chance you can contact the ATF or somebody and get a copy of your data table in areal excel format? (I realize we're talking about the government here and therefore the more sense it makes the less likely they are to do it) but could that be something you can try?

Other than that, yes I see the ratio you mentioned but (if I'm reading your post right) how are the wine gallons & the proof gallons ending up the same?