I have a tank that I measure daily to see how many gallons are in. I measure
it with a stick and then look at a chart made for the tank to see how many
gallons it equals. I would like to set up excel so that I can enter in the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?

Set up a two-column table somewhere which is similar to your chart,
eg:

0.5 23
1.0 45
1.5 68
2.0 91

and so on. Suppose this occupies G1:H15. Then, if you enter your
measurement in A1, you can have this formula in B1:

=IF(A1=0,0,VLOOKUP(A1,G$1:H$15,1))

to give you the volume. You could make it more accurate by
interpolating between vertical heights, although you do not suggest
that this is what you do currently.

Another way would be to just calculate the volume from the height,
assuming you know the diameter (and thus the cross-sectional area) of
the tank.

Hope this helps.

Pete

On Sep 17, 5:26*pm, Scotty <Scotty @discussions.microsoft.com wrote:
I have a tank that I measure daily to see how many gallons are in. I measure
it with a stick and then look at a chart made for the tank to see how many
gallons it equals. I would like to set up excel so that I can enter in the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?

If the tank has a linear ratio of inches to gallons all you need to do is
multiply the inches of material times the gallons per inch.

If the tank is not like that then you need to create a 2 column table with
the inches in the left column and the gallons in the right column:

...........A..........B
1........0...........0
2........1.........100
3........2.........422
4........3.........888
5........4........1123

There are several formulas you can use to get the result. Here are a couple:

D1 = tank measurement = 3

=VLOOKUP(D1,A1:B5,2,0)

Or

=SUMIF(A1:A5,D1,B1:B5)


--
Biff
Microsoft Excel MVP


"Scotty" <Scotty @discussions.microsoft.com wrote in message
...
I have a tank that I measure daily to see how many gallons are in. I
measure
it with a stick and then look at a chart made for the tank to see how many
gallons it equals. I would like to set up excel so that I can enter in the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?

Scotty,

Use this formula

=(PI()*B1^2*A1)*6.22883288

Where
B1 is the radius of the tank
and
A1 is the dip depth in inches
the answer will be imperial gallons

Mike

"Scotty" wrote:

I have a tank that I measure daily to see how many gallons are in. I measure
it with a stick and then look at a chart made for the tank to see how many
gallons it equals. I would like to set up excel so that I can enter in the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?

A thought,

I've assumed a round tank!! if it's rectangular use

=(L*B*A1)*6.22883288

Where L * B are the 2 sides
A1 remains the same

Mike

"Mike H" wrote:

Scotty,

Use this formula

=(PI()*B1^2*A1)*6.22883288

Where
B1 is the radius of the tank
and
A1 is the dip depth in inches
the answer will be imperial gallons

Mike

"Scotty" wrote:

I have a tank that I measure daily to see how many gallons are in. I measure
it with a stick and then look at a chart made for the tank to see how many
gallons it equals. I would like to set up excel so that I can enter in the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?

Hmmm,

I'll get it right in a minute. The dip must be in feet. i.e 12ft 6in should
be entered as 12.5

Mike

"Mike H" wrote:

Scotty,

Use this formula

=(PI()*B1^2*A1)*6.22883288

Where
B1 is the radius of the tank
and
A1 is the dip depth in inches
the answer will be imperial gallons

Mike

"Scotty" wrote:

I have a tank that I measure daily to see how many gallons are in. I measure
it with a stick and then look at a chart made for the tank to see how many
gallons it equals. I would like to set up excel so that I can enter in the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?

I have not used excel in years......so setting up the table is seeming to be
problematic. The chart I have is very accurate...made by the tank company
example..
inches gallons
0.125 1
0.250 2
0.375 3
0.50 4
....... ......
71.75 2088

563 total increments in all

thanks for the help


"Pete_UK" wrote:

Set up a two-column table somewhere which is similar to your chart,
eg:

0.5 23
1.0 45
1.5 68
2.0 91

and so on. Suppose this occupies G1:H15. Then, if you enter your
measurement in A1, you can have this formula in B1:

=IF(A1=0,0,VLOOKUP(A1,G$1:H$15,1))

to give you the volume. You could make it more accurate by
interpolating between vertical heights, although you do not suggest
that this is what you do currently.

Another way would be to just calculate the volume from the height,
assuming you know the diameter (and thus the cross-sectional area) of
the tank.

Hope this helps.

Pete

On Sep 17, 5:26 pm, Scotty <Scotty @discussions.microsoft.com wrote:
I have a tank that I measure daily to see how many gallons are in. I measure
it with a stick and then look at a chart made for the tank to see how many
gallons it equals. I would like to set up excel so that I can enter in the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?

tank is laying horizontal.... what will that do to the formula?


"Mike H" wrote:

Hmmm,

I'll get it right in a minute. The dip must be in feet. i.e 12ft 6in should
be entered as 12.5

Mike

"Mike H" wrote:

Scotty,

Use this formula

=(PI()*B1^2*A1)*6.22883288

Where
B1 is the radius of the tank
and
A1 is the dip depth in inches
the answer will be imperial gallons

Mike

"Scotty" wrote:

I have a tank that I measure daily to see how many gallons are in. I measure
it with a stick and then look at a chart made for the tank to see how many
gallons it equals. I would like to set up excel so that I can enter in the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?

Nothing as long as the liquid surface is Parallel to the base i.e it's not
like the leaning tower of Pisa.

For a flat tank you would do it manually like this

Length of tank * Breadth of tank * liquid depth in feet * 6.22883288

The last number is how many imperial gallons are in 1Cuft.

To covert this to an excel formula

=6 * 4 * A1 * 6.22883288

All units of measure are in feet and the tank dip is entered in A1

If you prefer inches do this

=((72*48*A1)/1728)*6.22883288

Tank dips are notoriously inaccurate anyway, any inlclination of the
dipstick causes a high reading.

Mike

"Scotty" wrote:

tank is laying horizontal.... what will that do to the formula?


"Mike H" wrote:

Hmmm,

I'll get it right in a minute. The dip must be in feet. i.e 12ft 6in should
be entered as 12.5

Mike

"Mike H" wrote:

Scotty,

Use this formula

=(PI()*B1^2*A1)*6.22883288

Where
B1 is the radius of the tank
and
A1 is the dip depth in inches
the answer will be imperial gallons

Mike

"Scotty" wrote:

I have a tank that I measure daily to see how many gallons are in. I measure
it with a stick and then look at a chart made for the tank to see how many
gallons it equals. I would like to set up excel so that I can enter in the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?

Based on the small sample you posted the ratio is linear: 0.125 inches = 1
gallon

A1 = measurement

=A1/0.125

--
Biff
Microsoft Excel MVP


"Scotty" wrote in message
...
I have not used excel in years......so setting up the table is seeming to
be
problematic. The chart I have is very accurate...made by the tank company
example..
inches gallons
0.125 1
0.250 2
0.375 3
0.50 4
...... ......
71.75 2088

563 total increments in all

thanks for the help


"Pete_UK" wrote:

Set up a two-column table somewhere which is similar to your chart,
eg:

0.5 23
1.0 45
1.5 68
2.0 91

and so on. Suppose this occupies G1:H15. Then, if you enter your
measurement in A1, you can have this formula in B1:

=IF(A1=0,0,VLOOKUP(A1,G$1:H$15,1))

to give you the volume. You could make it more accurate by
interpolating between vertical heights, although you do not suggest
that this is what you do currently.

Another way would be to just calculate the volume from the height,
assuming you know the diameter (and thus the cross-sectional area) of
the tank.

Hope this helps.

Pete

On Sep 17, 5:26 pm, Scotty <Scotty @discussions.microsoft.com wrote:
I have a tank that I measure daily to see how many gallons are in. I
measure
it with a stick and then look at a chart made for the tank to see how
many
gallons it equals. I would like to set up excel so that I can enter in
the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?

Hi Biff,

this doesn't tie in with the last example the OP gave, i.e.:

71.75 2088

Based on answers elsewhere, I imagine it is a cylindrical tank on its
side, so it won't be linear.

I stand by my first post, though it will be tedious to enter 563
increments.

Pete

On Sep 17, 6:50*pm, "T. Valko" wrote:
Based on the small sample you posted the ratio is linear: 0.125 inches = 1
gallon

A1 = measurement

=A1/0.125

--
Biff
Microsoft Excel MVP

"Scotty" wrote in message

...



I have not used excel in years......so setting up the table is seeming to
be
problematic. The chart I have is very accurate...made by the tank company
example..
inches * * * * *gallons
0.125 * * * * * * * * 1
0.250 * * * * * * * * *2
0.375 * * * * * * * * *3
0.50 * * * * * * * * * *4
...... * * * * * * * * ......
71.75 * * * * * * *2088

563 total increments in all

thanks for the help

"Pete_UK" wrote:

Set up a two-column table somewhere which is similar to your chart,
eg:

0.5 * * 23
1.0 * * 45
1.5 * * 68
2.0 * * 91

and so on. Suppose this occupies G1:H15. Then, if you enter your
measurement in A1, you can have this formula in B1:

=IF(A1=0,0,VLOOKUP(A1,G$1:H$15,1))

to give you the volume. You could make it more accurate by
interpolating between vertical heights, although you do not suggest
that this is what you do currently.

Another way would be to just calculate the volume from the height,
assuming you know the diameter (and thus the cross-sectional area) of
the tank.

Hope this helps.

Pete

On Sep 17, 5:26 pm, Scotty <Scotty @discussions.microsoft.com wrote:
I have a tank that I measure daily to see how many gallons are in. I
measure
it with a stick and then look at a chart made for the tank to see how
many
gallons it equals. I would like to set up excel so that I can enter in
the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?- Hide quoted text -

- Show quoted text -

Hi pete_UK

You are right it is a cylindrical tank laying horizontal in the ground.
It will be tedious to enter in all the increments however, I am willing to
do it....just need to know how to set it up. I just can't remember all I
learned in college and I am having one heck of a time getting the table set
up..
How and where do I enter

0.125 1
0.250 2

and so on through
71.750 2088

Thanks for the help you guys will be huge time savers.



"Pete_UK" wrote:

Hi Biff,

this doesn't tie in with the last example the OP gave, i.e.:

71.75 2088

Based on answers elsewhere, I imagine it is a cylindrical tank on its
side, so it won't be linear.

I stand by my first post, though it will be tedious to enter 563
increments.

Pete

On Sep 17, 6:50 pm, "T. Valko" wrote:
Based on the small sample you posted the ratio is linear: 0.125 inches = 1
gallon

A1 = measurement

=A1/0.125

--
Biff
Microsoft Excel MVP

"Scotty" wrote in message

...



I have not used excel in years......so setting up the table is seeming to
be
problematic. The chart I have is very accurate...made by the tank company
example..
inches gallons
0.125 1
0.250 2
0.375 3
0.50 4
...... ......
71.75 2088

563 total increments in all

thanks for the help

"Pete_UK" wrote:

Set up a two-column table somewhere which is similar to your chart,
eg:

0.5 23
1.0 45
1.5 68
2.0 91

and so on. Suppose this occupies G1:H15. Then, if you enter your
measurement in A1, you can have this formula in B1:

=IF(A1=0,0,VLOOKUP(A1,G$1:H$15,1))

to give you the volume. You could make it more accurate by
interpolating between vertical heights, although you do not suggest
that this is what you do currently.

Another way would be to just calculate the volume from the height,
assuming you know the diameter (and thus the cross-sectional area) of
the tank.

Hope this helps.

Pete

On Sep 17, 5:26 pm, Scotty <Scotty @discussions.microsoft.com wrote:
I have a tank that I measure daily to see how many gallons are in. I
measure
it with a stick and then look at a chart made for the tank to see how
many
gallons it equals. I would like to set up excel so that I can enter in
the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?- Hide quoted text -

- Show quoted text -

I didn't test it for 71.752088.

Oh well!

I agree that the lookup table is the way to go.


--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
...
Hi Biff,

this doesn't tie in with the last example the OP gave, i.e.:

71.75 2088

Based on answers elsewhere, I imagine it is a cylindrical tank on its
side, so it won't be linear.

I stand by my first post, though it will be tedious to enter 563
increments.

Pete

On Sep 17, 6:50 pm, "T. Valko" wrote:
Based on the small sample you posted the ratio is linear: 0.125 inches = 1
gallon

A1 = measurement

=A1/0.125

--
Biff
Microsoft Excel MVP

"Scotty" wrote in message

...



I have not used excel in years......so setting up the table is seeming to
be
problematic. The chart I have is very accurate...made by the tank
company
example..
inches gallons
0.125 1
0.250 2
0.375 3
0.50 4
...... ......
71.75 2088

563 total increments in all

thanks for the help

"Pete_UK" wrote:

Set up a two-column table somewhere which is similar to your chart,
eg:

0.5 23
1.0 45
1.5 68
2.0 91

and so on. Suppose this occupies G1:H15. Then, if you enter your
measurement in A1, you can have this formula in B1:

=IF(A1=0,0,VLOOKUP(A1,G$1:H$15,1))

to give you the volume. You could make it more accurate by
interpolating between vertical heights, although you do not suggest
that this is what you do currently.

Another way would be to just calculate the volume from the height,
assuming you know the diameter (and thus the cross-sectional area) of
the tank.

Hope this helps.

Pete

On Sep 17, 5:26 pm, Scotty <Scotty @discussions.microsoft.com wrote:
I have a tank that I measure daily to see how many gallons are in. I
measure
it with a stick and then look at a chart made for the tank to see how
many
gallons it equals. I would like to set up excel so that I can enter
in
the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?- Hide quoted text -

- Show quoted text -

If tank is cylindrical laying horizontally, it is impossible for
measurements to be linear at 1 gallon per .125 inches as your example shows.


Gord Dibben MS Excel MVP

On Wed, 17 Sep 2008 10:07:01 -0700, Scotty
wrote:

I have not used excel in years......so setting up the table is seeming to be
problematic. The chart I have is very accurate...made by the tank company
example..
inches gallons
0.125 1
0.250 2
0.375 3
0.50 4
...... ......
71.75 2088

563 total increments in all

thanks for the help


"Pete_UK" wrote:

Set up a two-column table somewhere which is similar to your chart,
eg:

0.5 23
1.0 45
1.5 68
2.0 91

and so on. Suppose this occupies G1:H15. Then, if you enter your
measurement in A1, you can have this formula in B1:

=IF(A1=0,0,VLOOKUP(A1,G$1:H$15,1))

to give you the volume. You could make it more accurate by
interpolating between vertical heights, although you do not suggest
that this is what you do currently.

Another way would be to just calculate the volume from the height,
assuming you know the diameter (and thus the cross-sectional area) of
the tank.

Hope this helps.

Pete

On Sep 17, 5:26 pm, Scotty <Scotty @discussions.microsoft.com wrote:
I have a tank that I measure daily to see how many gallons are in. I measure
it with a stick and then look at a chart made for the tank to see how many
gallons it equals. I would like to set up excel so that I can enter in the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?

Hi Scotty,

it doesn't really matter where you put the 2-column table. I suggested
in my first post that you put it in columns G and H and the formula I
gave you assumed it occupied G1:H15, not knowing how many entries you
would have in the table.

I don't know what your sheet will look like, but if you do start
putting the table in G1 and H1, then your formula would be:

=IF(A1=0,0,VLOOKUP(A1,G$1:H$563,1))

assuming that you put your depth measurement in A1 (in the same units
that you have in column G). This variation:

=IF(A1=0,0,VLOOKUP(A1,$G:$H,1))

can be used so that it doesn't matter where in columns G and H you
actually put the table.

Hope this helps.

Pete

On Sep 17, 7:15*pm, Scotty wrote:
Hi pete_UK

You are right it is a cylindrical tank laying horizontal in the ground.
It will be tedious to *enter in all the increments however, I am willing to
do it....just need to know how to set it up. I just can't remember all I
learned in college and I am having one heck of a time getting the table set
up..
How and where do I enter

0.125 * * * * 1
0.250 * * * * *2

and so on through
71.750 * * * 2088

Thanks for the help you guys will be huge time savers.



"Pete_UK" wrote:
Hi Biff,

this doesn't tie in with the last example the OP gave, i.e.:

71.75 * * *2088

Based on answers elsewhere, I imagine it is a cylindrical tank on its
side, so it won't be linear.

I stand by my first post, though it will be tedious to enter 563
increments.

Pete

On Sep 17, 6:50 pm, "T. Valko" wrote:
Based on the small sample you posted the ratio is linear: 0.125 inches = 1
gallon

A1 = measurement

=A1/0.125

--
Biff
Microsoft Excel MVP

"Scotty" wrote in message

...

I have not used excel in years......so setting up the table is seeming to
be
problematic. The chart I have is very accurate...made by the tank company
example..
inches * * * * *gallons
0.125 * * * * * * * * 1
0.250 * * * * * * * * *2
0.375 * * * * * * * * *3
0.50 * * * * * * * * * *4
...... * * * * * * * * ......
71.75 * * * * * * *2088

563 total increments in all

thanks for the help

"Pete_UK" wrote:

Set up a two-column table somewhere which is similar to your chart,
eg:

0.5 * * 23
1.0 * * 45
1.5 * * 68
2.0 * * 91

and so on. Suppose this occupies G1:H15. Then, if you enter your
measurement in A1, you can have this formula in B1:

=IF(A1=0,0,VLOOKUP(A1,G$1:H$15,1))

to give you the volume. You could make it more accurate by
interpolating between vertical heights, although you do not suggest
that this is what you do currently.

Another way would be to just calculate the volume from the height,
assuming you know the diameter (and thus the cross-sectional area) of
the tank.

Hope this helps.

Pete

On Sep 17, 5:26 pm, Scotty <Scotty @discussions.microsoft.com wrote:
I have a tank that I measure daily to see how many gallons are in. I
measure
it with a stick and then look at a chart made for the tank to see how
many
gallons it equals. I would like to set up excel so that I can enter in
the
stick measurement and it will give me the gallons in the next column
automatically. Any suggestions on how to do this?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -