Automatic Keg conversion

[gleemonex69]

Good afternoon, everyone. I am in charge of a bar here in deep south texas. I need help in creating an Excel file that will automatically convert the weight of a keg, to how much beer is in the keg (we call this COUNT). In the example below is the chart that I currently use. I would like to put in the weight of the keg, and excel to give me the count. Any help would be greatly appreciated. Thank you in advance.

<TABLE dir=ltr border=1 cellSpacing=1 borderColor=#000000 cellPadding=2 width=202><TBODY><TR><TD height=16 width="33%">

Weight​

​

​

​

​

</TD><TD height=16 width="33%">

Count​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

161​

​

​

​

​

</TD><TD height=16 width="33%">

FULL​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

154.5​

​

​

​

​

</TD><TD height=16 width="33%">

0.95​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

147.9​

​

​

​

​

</TD><TD height=16 width="33%">

0.9​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

141.4​

​

​

​

​

</TD><TD height=16 width="33%">

0.85​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

134.8​

​

​

​

​

</TD><TD height=16 width="33%">

0.8​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

128.3​

​

​

​

​

</TD><TD height=16 width="33%">

0.75​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

121.7​

​

​

​

​

</TD><TD height=16 width="33%">

0.7​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

115.2​

​

​

​

​

</TD><TD height=16 width="33%">

0.65​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

108.6​

​

​

​

​

</TD><TD height=16 width="33%">

0.6​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

102.1​

​

​

​

​

</TD><TD height=16 width="33%">

0.55​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

95.5​

​

​

​

​

</TD><TD height=16 width="33%">

0.5​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

89​

​

​

​

​

</TD><TD height=16 width="33%">

0.45​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

82.4​

​

​

​

​

</TD><TD height=16 width="33%">

0.4​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

75.9​

​

​

​

​

</TD><TD height=16 width="33%">

0.35​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

69.3​

​

​

​

​

</TD><TD height=16 width="33%">

0.3​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

62.8​

​

​

​

​

</TD><TD height=16 width="33%">

0.25​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

56.2​

​

​

​

​

</TD><TD height=16 width="33%">

0.2​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

49.7​

​

​

​

​

</TD><TD height=16 width="33%">

0.15​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

43.1​

​

​

​

​

</TD><TD height=16 width="33%">

0.1​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

36.6​

​

​

​

​

</TD><TD height=16 width="33%">

0.05​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">

30​

​

​

​

​

</TD><TD height=16 width="33%">

EMPTY​

​

​

​

​

</TD><TD height=16 width="33%">
</TD></TR><TR><TD height=16 width="33%">
</TD><TD height=16 width="33%">
</TD><TD height=16 width="33%">
</TD></TR></TBODY></TABLE>

 

[Randall00]

Do you not already have the Count? What value is supposed to appear in the third column?

 

[Smitty]

Welcome to the Board!

Check out LOOKUP.

You can enter a value and return the appropriate value from the table.

HTH,

 

[shg]

You could just use a formula. If A1 contains the weight (30 to 161 pounds),

=(A1-30)/(161-30)

returns percent full. Or

=MROUND((J8-30)/(161-30), 5%)

returns the nearest 5%.

 

[kpark91]

If we solve this problem, do we all get beer?

 

[gleemonex69]

The third column is not necessary. I copied it by mistake. The thing about it is that the keg weight will not always be as the chart, sometimes we round the weight down.