Solved

Visualize Volumen in a Tank

Posted on 2011-02-16
34
692 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:jemagnussen
  • 12
  • 9
  • 6
  • +2
34 Comments
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
jemagnussen,

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

Patrick

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

Author Comment

by:jemagnussen
Comment Utility
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
0
 
LVL 17

Expert Comment

by:aflockhart
Comment Utility
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)
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
Oops. Cross-posted. This works with your original workbook.
0
 

Author Comment

by:jemagnussen
Comment Utility
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
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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
0
 

Author Comment

by:jemagnussen
Comment Utility
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
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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
0
 

Author Comment

by:jemagnussen
Comment Utility
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
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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
0
 
LVL 17

Expert Comment

by:aflockhart
Comment Utility
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.)
0
 
LVL 17

Expert Comment

by:aflockhart
Comment Utility
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 ...
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
Maybe the screenshot below is better?

Patrick
MWSnap-020-2011-02-16--13-29-29.jpg
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
jemagnussen,

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

Saqib
0
 

Author Comment

by:jemagnussen
Comment Utility
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...
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
Jesper,

Perhaps the attached file gets a bit closer.

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

Author Comment

by:jemagnussen
Comment Utility
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
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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

Open in new window

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

Author Comment

by:jemagnussen
Comment Utility
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
0
 

Author Comment

by:jemagnussen
Comment Utility
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
0
 

Author Comment

by:jemagnussen
Comment Utility
PS: The tank when full has a capasity of more than 18.000 liters....

Brgds
Jesper
0
 
LVL 17

Expert Comment

by:aflockhart
Comment Utility
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 ...
0
 
LVL 17

Expert Comment

by:aflockhart
Comment Utility
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.
0
 

Author Comment

by:jemagnussen
Comment Utility
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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
0
 
LVL 17

Expert Comment

by:aflockhart
Comment Utility
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.
0
 

Author Comment

by:jemagnussen
Comment Utility
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
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 total points
Comment Utility
0
 

Author Closing Comment

by:jemagnussen
Comment Utility
What I needed for this little app.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now