Visualize Volumen in a Tank

jemagnussen,

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

Patrick

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

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

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)

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

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

Select allOpen in new window

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

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

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

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

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

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

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

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.)

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 ...

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

Maybe the screenshot below is better?

Patrick
MWSnap-020-2011-02-16--13-29-29.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

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

jemagnussen,

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

Saqib

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...

Jesper,

Perhaps the attached file gets a bit closer.

Patrick
Copy-of-Fuel-in-tank-05.xls

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

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

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

Select allOpen in new window

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

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

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

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

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 ...

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.

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

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

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.

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

What I needed for this little app.