conversion chart

[Karen]

I have a tank containing product. The amount of product has to be measured
every day in inches and converted to gallons according to the paper
conversion chart. I would like to enter the conversion chart in excel so
that if I enter the inch reading in cell a1, the number of gallons
automatically displays in cell b1.

example of paper chart:
1.0 inch = 10 gallons
1.5 inch = 24 gallons
2.0 inch = 37 gallons
2.5 inch = 53 gallons

I am only a novice at excel and can not figure out what formula to use to do
this.

 

[Bob I]

use VLOOKUP, see Excel Help on it for Examples.

 

[Dave O]

Based on your example of the paper chart, the conversion of inches to
gallons is not linear, suggesting the tank is spherical or perhaps
drum-shaped but lying on its side.

There's a couple ways to do this. The most accurate way would be to
determine the physical dimensions of the interior of the tank, and
develop a formula to convert depth measurement to volume. You may have
an Engineering department that could help you with this. The advantage
to this method is accuracy: if someone enters 1.25 inches (which is
not on your chart) then the exact volume of fluid can still be
reported.

A less exact way to do it is to recreate the paper conversion chart in
Excel and use a formula such as VLOOKUP to return a number of gallons
based on user input. This is do-able but would require you to prevent
users from entering 1.25, for instance.

Dave O
Eschew obfuscation

 

[Bernard Liengme]

In A1 type: Inches; in B1 type Gallons
In A (under A1) enter the inches (1, 1.5, .......)
In B (under B1( enter the gallons (10, 24, .....0
Let's say the last row in 20
In C1 type today's inch reading (let's say it is 3)
In D1 use the formula =VLOOKUP(C1,A2:B20) and it will return (display in
cell) the gallons you
would have looked up in the table

We could get fancy and do a polynomial fit to the data fro a more accurate
answer. But tell us if the first method works for you.
best wishes

 

[Dave O]

What is the shape and max capacity of the tank?

Dave O
Eschew obfuscation