jemagnussen

asked on

# Visualize Volumen in a Tank

Hi all,

I have a small spreadsheet where I would like to make a visualisation of the content of 2 fuel tanks. The formula for calculating the content is already there, but how do I visualise the actual content of the tank in the two illustrations of the tanks.

Best regards

Jesper

Fuel-in-tank-1.xls

I have a small spreadsheet where I would like to make a visualisation of the content of 2 fuel tanks. The formula for calculating the content is already there, but how do I visualise the actual content of the tank in the two illustrations of the tanks.

Best regards

Jesper

Fuel-in-tank-1.xls

ASKER

Hi Patric,

That was a solution, but our tanks are laying down and the folks we work with here must have all visualised so it is very close to reality....

I once made a similar spreadsheet where I used something with .gif files and some VBA but have completely forgotten how to do this. Maybe the question belong somewhere else...?

Best regards

Jesper

That was a solution, but our tanks are laying down and the folks we work with here must have all visualised so it is very close to reality....

I once made a similar spreadsheet where I used something with .gif files and some VBA but have completely forgotten how to do this. Maybe the question belong somewhere else...?

Best regards

Jesper

Depends on how complex you want this to become. Simplest way I can see would be to arrange the graphics of the tanks to match an exact number of spreadsheet rows, and use formulas and conditional formatting.

So if you divided the tank into 10 cells, you might shade the bottom cell if the level in the tank was at least 20 % of the radius, or 25 cm.

Put a formula in the bottom cell

=if( niveau > radius/5,1,0 )

Put a formula in the cell above:

=if( niveau > radius/5 * 2 ,1,0 )

and so on .

Then use conditional formatting on these cells to colour the ones whose value is 1 and to hide the ones whose value is 0.

Note - this also depends whether you want the graphics to match the way that the cylindrical tanks actually fill up with fuel ( so you would be drawing a picture based on the measured LEVEL ) or if you want it to show quarter of the tank coloured when it is quarter full of fuel (so your calculation would need to be be based on the calculated VOLUME compared to the maximum volume)

So if you divided the tank into 10 cells, you might shade the bottom cell if the level in the tank was at least 20 % of the radius, or 25 cm.

Put a formula in the bottom cell

=if( niveau > radius/5,1,0 )

Put a formula in the cell above:

=if( niveau > radius/5 * 2 ,1,0 )

and so on .

Then use conditional formatting on these cells to colour the ones whose value is 1 and to hide the ones whose value is 0.

Note - this also depends whether you want the graphics to match the way that the cylindrical tanks actually fill up with fuel ( so you would be drawing a picture based on the measured LEVEL ) or if you want it to show quarter of the tank coloured when it is quarter full of fuel (so your calculation would need to be be based on the calculated VOLUME compared to the maximum volume)

Jesper,

>That was a solution, but our tanks are laying down and the folks we work with here must have all visualised so it is very close to reality....

OK - see attached file for new solution.

Patrick

Copy-of-Fuel-in-tank-02.xls

>That was a solution, but our tanks are laying down and the folks we work with here must have all visualised so it is very close to reality....

OK - see attached file for new solution.

Patrick

Copy-of-Fuel-in-tank-02.xls

This macro shows one way/

```
Sub FuelLevel()
Dim sh As Shape
Dim sh1 As Shape
Dim w As Single
For Each sh In Shapes
If sh.Type = msoTextBox Then
If sh.TextFrame.Characters.Text = "FUEL TANK 1" Then
If w <= sh.Width Then
w = Cells(4, 6) * sh.Width / (Cells(3, 4).Value * Cells(4, 4).Value * Cells(5, 4).Value)
Set sh1 = Shapes.AddShape(msoShapeRectangle, sh.Left, sh.Top, w, sh.Height)
sh.Fill.BackColor.RGB = RGB(125, 124, 125)
Else
MsgBox "Tank 1 is overfull"
End If
End If
End If
Next sh
End Sub
```

Oops. Cross-posted. This works with your original workbook.

ASKER

Hi Patric,

Your solution is a bit rough, I would like to have something a bit more neat. I was previously using some small gif files taht somehow worked with VBA or a macro to fill in the color to show the level of diesel in the tanks, but are struggeling to remember how I did it.... my old brain is getting tired... :O)

Graham,

Placed your macro in the sheet, but it did not work...?

Best regards

Jesper

Your solution is a bit rough, I would like to have something a bit more neat. I was previously using some small gif files taht somehow worked with VBA or a macro to fill in the color to show the level of diesel in the tanks, but are struggeling to remember how I did it.... my old brain is getting tired... :O)

Graham,

Placed your macro in the sheet, but it did not work...?

Best regards

Jesper

Jesper,

>Your solution is a bit rough, I would like to have something a bit more neat.

Well thanks for the flattery. Just what are you wanting? In profile a cylidrical tank lying on its side is a rectangle - so that should be good enough to represent the tanks - unless your guys get confused by what day of the week it is!

Patrick

>Your solution is a bit rough, I would like to have something a bit more neat.

Well thanks for the flattery. Just what are you wanting? In profile a cylidrical tank lying on its side is a rectangle - so that should be good enough to represent the tanks - unless your guys get confused by what day of the week it is!

Patrick

ASKER

What I'm looking for is the same type of grafics that is used for visualising various values on instruments like a raingauge or windspeed indicator etc...

Brgds

Jesper

Brgds

Jesper

Jesper,

>What I'm looking for is the same type of grafics that is used for visualising various values on instruments like a raingauge or windspeed indicator etc...

Are you sure you don't also want it to sing the national anthem?

Speedometer charts are hard work to create - so much so that Peltier gave up giving instructions for them on his website.

Patrick

>What I'm looking for is the same type of grafics that is used for visualising various values on instruments like a raingauge or windspeed indicator etc...

Are you sure you don't also want it to sing the national anthem?

Speedometer charts are hard work to create - so much so that Peltier gave up giving instructions for them on his website.

Patrick

ASKER

Hi Patric,

Sorry - was not trying to being rude or trying to offend you in any way, my english is not always the best. Will try to refrase the answer a bit.

The solution you made is working, no doubt about that, but what I am looking for is a way to visualise the level in the tanks with .gif files, reason being that I have done this before (many years ago) and that was a very neat and functional way of showing many types af graphical illustrations. In many applications you see t.ex. the needle in a speedometer or the mercury in a termometer illustarted like this, and I would really like to get that same way incoorporated into my little spreadsheet...

Trust me, the folks around here IS confused with what day of the week it is.... :O)

Best regards

Jesper

Sorry - was not trying to being rude or trying to offend you in any way, my english is not always the best. Will try to refrase the answer a bit.

The solution you made is working, no doubt about that, but what I am looking for is a way to visualise the level in the tanks with .gif files, reason being that I have done this before (many years ago) and that was a very neat and functional way of showing many types af graphical illustrations. In many applications you see t.ex. the needle in a speedometer or the mercury in a termometer illustarted like this, and I would really like to get that same way incoorporated into my little spreadsheet...

Trust me, the folks around here IS confused with what day of the week it is.... :O)

Best regards

Jesper

Jester,

Perhaps the attached file does what's wanted - screenshot below.

Patrick

Copy-of-Fuel-in-tank-03.xls

MWSnap-018-2011-02-16--12-45-41.jpg

Perhaps the attached file does what's wanted - screenshot below.

Patrick

Copy-of-Fuel-in-tank-03.xls

MWSnap-018-2011-02-16--12-45-41.jpg

patrickab

- your proposed solution is a lot neater than mine ... :-)

but the calculation in your sheet assumes that the tank is standing on one of the circular ends and the 'level' measurement is being done vertically with a maximum value of 3.8m ( the cylinder's length).

The way I read the requirement, the tank is lying on its side, the maximum measured depth would then be twice the radius, and the length of the cylinder is irrelevent in displaying how far up the liquid goes. (It also means that a measured level of 0.5 metres does not mean the tank contains half as much as a measured level of 1.0 metres - because the cylidnder is narrower at the bottom.)

- your proposed solution is a lot neater than mine ... :-)

but the calculation in your sheet assumes that the tank is standing on one of the circular ends and the 'level' measurement is being done vertically with a maximum value of 3.8m ( the cylinder's length).

The way I read the requirement, the tank is lying on its side, the maximum measured depth would then be twice the radius, and the length of the cylinder is irrelevent in displaying how far up the liquid goes. (It also means that a measured level of 0.5 metres does not mean the tank contains half as much as a measured level of 1.0 metres - because the cylidnder is narrower at the bottom.)

I've done things with lots of GIF or JPG files before as well - you create enough different GIF files to represent the different values you need to show, name them in a way that includes somthing to identify the associated measurement, and have a bunch of macros to choose the appropriate image.

e.g. tank010.gif would show a tank 10% full. tank020.gif would show one 20% full and so on. You then work out how full the tank is, round it to the nearest 10% ( or whatever you have chosen), and use this value to build up a filename, then import that image into your spreadsheet.

All gets very messy. Patrick's way looks a lot easier ...

e.g. tank010.gif would show a tank 10% full. tank020.gif would show one 20% full and so on. You then work out how full the tank is, round it to the nearest 10% ( or whatever you have chosen), and use this value to build up a filename, then import that image into your spreadsheet.

All gets very messy. Patrick's way looks a lot easier ...

aflockhart,

>The way I read the requirement, the tank is lying on its side, the maximum measured depth would then be twice the radius, and the length of the cylinder is irrelevent in displaying how far up the liquid goes. (It also means that a measured level of 0.5 metres does not mean the tank contains half as much as a measured level of 1.0 metres - because the cylidnder is narrower at the bottom.)

You're right about the height vs diameter. I should have used the diamater - see attached file for the correction - and the new screenshot below. I am assuming they dip the tanks from the top of the curved side when the tank is lying on its side - to assess how full/empty they are.

Patrick

Copy-of-Fuel-in-tank-04.xls

MWSnap-019-2011-02-16--13-18-44.jpg

>The way I read the requirement, the tank is lying on its side, the maximum measured depth would then be twice the radius, and the length of the cylinder is irrelevent in displaying how far up the liquid goes. (It also means that a measured level of 0.5 metres does not mean the tank contains half as much as a measured level of 1.0 metres - because the cylidnder is narrower at the bottom.)

You're right about the height vs diameter. I should have used the diamater - see attached file for the correction - and the new screenshot below. I am assuming they dip the tanks from the top of the curved side when the tank is lying on its side - to assess how full/empty they are.

Patrick

Copy-of-Fuel-in-tank-04.xls

MWSnap-019-2011-02-16--13-18-44.jpg

The code puts a coloured autoshape , whose width shows the proportion to which the tank is filled, over the central text box in the tank picture. I have put the code behind a button.

It only works for one tank, but it does demonstrate the technique. Also, with your figures on my system, the tank is extremely overfilled. I think that is because of the "," and "." setting differences between our systems

Copy-of-Fuel-in-tank-1.xls

It only works for one tank, but it does demonstrate the technique. Also, with your figures on my system, the tank is extremely overfilled. I think that is because of the "," and "." setting differences between our systems

Copy-of-Fuel-in-tank-1.xls

GrahamSkan,

If the fuel is 1.1 metres deep (when the tank is on its side) and the tank has a diameter of 2.5 metres it isn't overfilled. Something's wrong with your calcs.

Patrick

If the fuel is 1.1 metres deep (when the tank is on its side) and the tank has a diameter of 2.5 metres it isn't overfilled. Something's wrong with your calcs.

Patrick

jemagnussen,

Can you sketch, on your original worksheet, how you want the level shown?

Saqib

Can you sketch, on your original worksheet, how you want the level shown?

Saqib

ASKER

Patric,

It looks quite ok, will it be possible to remove the grid behind and the labels on the X & Y axis so only the tanks and level is shown?

Graham,

I looked at your calc. but can't quite figure out what goes wrong in my Danish spreadsheet, because it does not work???

saaqibh,

Quite a lot like what Patric has shown, but without the underlaying grid from the graphs.... only the tanks and the different color for the level inside.

All: The calculation in the original file where the radius is used is correct, but might fail when transferred into a US spreadsheet due to som different use of "," and "." as comma seperators and some different names for SQRT and so on.,..

Best regards

Jesper

Would love to see how Grahams macro works, otherwise I will give the points to Patric for his work...

It looks quite ok, will it be possible to remove the grid behind and the labels on the X & Y axis so only the tanks and level is shown?

Graham,

I looked at your calc. but can't quite figure out what goes wrong in my Danish spreadsheet, because it does not work???

saaqibh,

Quite a lot like what Patric has shown, but without the underlaying grid from the graphs.... only the tanks and the different color for the level inside.

All: The calculation in the original file where the radius is used is correct, but might fail when transferred into a US spreadsheet due to som different use of "," and "." as comma seperators and some different names for SQRT and so on.,..

Best regards

Jesper

Would love to see how Grahams macro works, otherwise I will give the points to Patric for his work...

ASKER

Hi Patric,

Changed it a bit more and went back to pure square....

This will do, thank you all for your efforts. If I tumble over the old files some day, I will upload it just for reference for some other user some day.

Best regards and have a nice day/evening whereever you are...

Jesper - in the darkest Africa...

PS: If you get your macro to work Graham, then I'm quite curious as to how it looks... you are right by the way, tons of gif's will be messy....

Fuel-in-tank-1-and-2.xls

Changed it a bit more and went back to pure square....

This will do, thank you all for your efforts. If I tumble over the old files some day, I will upload it just for reference for some other user some day.

Best regards and have a nice day/evening whereever you are...

Jesper - in the darkest Africa...

PS: If you get your macro to work Graham, then I'm quite curious as to how it looks... you are right by the way, tons of gif's will be messy....

Fuel-in-tank-1-and-2.xls

I thought it had over 7000 litres in it. :)

I was making it too complicated, that figure is irrelevant to the display calculation.

I was making it too complicated, that figure is irrelevant to the display calculation.

```
Sub Button15_Click()
'Sub FuelLevel()
Dim sh As Shape
Dim sh1 As Shape
Dim w As Single
With ActiveSheet
For Each sh In .Shapes
If sh.Type = msoTextBox Then
If sh.TextFrame.Characters.Text = "FUEL TANK 1" Then
w = .Cells(4, 4).Value * sh.Width / .Cells(3, 4).Value
If w <= sh.Width Then
Set sh1 = .Shapes.AddShape(msoShapeRectangle, sh.Left, sh.Top, w, sh.Height)
sh.Fill.BackColor.RGB = RGB(125, 124, 125)
Else
MsgBox "Tank 1 is overfull"
End If
End If
End If
Next sh
End With
'End Sub
End Sub
```

Copy-of-Copy-of-Fuel-in-tank-1-1.xls
ASKER

UPS... just realised that it's the wrong calc. used for this Patric - when calculating cylinders laying down then it is necessary to use the radius like this:

=D3*((PI()* D5^2)/2 - (D5 - D4)*SQRT(D5^2 -(D5 - D4)^2) - D5^2*ASIN((D5 - D4)/D5))*1000

to get the volume of diesel at a given level...

Where

D3: lenght

D4: level

D5: radius

Best regards

Jesper

=D3*((PI()* D5^2)/2 - (D5 - D4)*SQRT(D5^2 -(D5 - D4)^2) - D5^2*ASIN((D5 - D4)/D5))*1000

to get the volume of diesel at a given level...

Where

D3: lenght

D4: level

D5: radius

Best regards

Jesper

ASKER

Graham,

You macro works fine now, but could you change it so it instead of changing the width of the band with the name "Tank 1" shows in a diffenrent color the level in the tanks similar to what Patric has made...?

You code works horisontal it should work vertical.... hope I express myself clearly enough to understand...?

Best regards

Jesper

You macro works fine now, but could you change it so it instead of changing the width of the band with the name "Tank 1" shows in a diffenrent color the level in the tanks similar to what Patric has made...?

You code works horisontal it should work vertical.... hope I express myself clearly enough to understand...?

Best regards

Jesper

ASKER

PS: The tank when full has a capasity of more than 18.000 liters....

Brgds

Jesper

Brgds

Jesper

re your comment at 16:07 (UK time)

This depends on whether you need the picture to be a PHYSICAL REPRESENTATION of the tank, or a LOGICAL REPRESENTATION of how much is in it

If the tank is 2m across and your measured levelis 0.5m, do you want to display:

a) a diagram showing the level in the tank quarter of the way up the diagram, as if it was a scale drawing of the tank itself with the contents visible inside

or

b)a diagram showing the calculated "percentage full" using your formula, which will be a bit less than quarter of a tank.

And actually, does exact accuracy really matter ? If it's anything lke my car fuel gauge, I would just interpret it as "roughly a quarter full" or "almost empty" anyway ...

This depends on whether you need the picture to be a PHYSICAL REPRESENTATION of the tank, or a LOGICAL REPRESENTATION of how much is in it

If the tank is 2m across and your measured levelis 0.5m, do you want to display:

a) a diagram showing the level in the tank quarter of the way up the diagram, as if it was a scale drawing of the tank itself with the contents visible inside

or

b)a diagram showing the calculated "percentage full" using your formula, which will be a bit less than quarter of a tank.

And actually, does exact accuracy really matter ? If it's anything lke my car fuel gauge, I would just interpret it as "roughly a quarter full" or "almost empty" anyway ...

From your formula: if the measured level is 25% of the diameter ( half the radius ) then the actual volume in the tank is roughly 20% of the total volume. Is that different enough for anyone to notice on the diagram ?

And if you are measuring with a dipstick anyway, how accurate is that ?

The difference does get more obvious as the tank gets emptier: when the depth is 10% of the diameter, the tank only contains 5% of the volume.

And if you are measuring with a dipstick anyway, how accurate is that ?

The difference does get more obvious as the tank gets emptier: when the depth is 10% of the diameter, the tank only contains 5% of the volume.

ASKER

Hi aflockhart,

Best would be a physical representation with a reasonable degree of accuracy as this is used for the manager in charge to evaluate the need for a new order for fuel, and with the consumption we have we empty one tank in 4-5 days but not with a constant consumption, so it is rather necessary to have a certain degree of accuracy.

A sort of a scale drawing itself (cylinder laying down, 3.8 m long and a radius of 1.25m) would be best as this will give a clean and easily understood "picture" of the content.

Manager will input the measured level in the tanks every day (from a dipstick) and input the value into the spreadsheet, immidiately showing him visually how much fuel is left as well as giving him the excact liters remaining.

Best regards

Jesper

Best would be a physical representation with a reasonable degree of accuracy as this is used for the manager in charge to evaluate the need for a new order for fuel, and with the consumption we have we empty one tank in 4-5 days but not with a constant consumption, so it is rather necessary to have a certain degree of accuracy.

A sort of a scale drawing itself (cylinder laying down, 3.8 m long and a radius of 1.25m) would be best as this will give a clean and easily understood "picture" of the content.

Manager will input the measured level in the tanks every day (from a dipstick) and input the value into the spreadsheet, immidiately showing him visually how much fuel is left as well as giving him the excact liters remaining.

Best regards

Jesper

Try this file

I have used combinations and positions of shapes to get the effect. Change the level in tank 1 to see the effect.

Saqib

Copy-of-Fuel-in-tank-1.xls

I have used combinations and positions of shapes to get the effect. Change the level in tank 1 to see the effect.

Saqib

Copy-of-Fuel-in-tank-1.xls

it sounds like patrickab's method is OK then - if you need a physical diagram that represents the tank as 25% full if the radius is 1.25 and the dipstick reads 0.625, the only calculation needed is dipstick level divided by twice the radius.

ASKER

Hi Saqib,

The way you have made it looks fantastic, is it possible to make it work on tank 2 as well..?

Best regards

Jesper

The way you have made it looks fantastic, is it possible to make it work on tank 2 as well..?

Best regards

Jesper

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

What I needed for this little app.

Much easier to use a cylindrical bar chart - see attached file.

Patrick

Copy-of-Fuel-in-tank-1.xls