Solved

Visualize Volumen in a Tank

Posted on 2011-02-16
34
722 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
ID: 34905484
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
ID: 34905527
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
ID: 34905586
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 45

Expert Comment

by:patrickab
ID: 34905671
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
ID: 34905687
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
ID: 34905699
Oops. Cross-posted. This works with your original workbook.
0
 

Author Comment

by:jemagnussen
ID: 34905976
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
ID: 34906012
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
ID: 34906045
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
ID: 34906064
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
ID: 34906090
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
ID: 34906110
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
ID: 34906208
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
ID: 34906252
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
ID: 34906363
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
ID: 34906438
Maybe the screenshot below is better?

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

Expert Comment

by:GrahamSkan
ID: 34906529
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34906770
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
ID: 34907031
jemagnussen,

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

Saqib
0
 

Author Comment

by:jemagnussen
ID: 34907267
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
ID: 34907307
Jesper,

Perhaps the attached file gets a bit closer.

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

Author Comment

by:jemagnussen
ID: 34907593
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
ID: 34907820
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
ID: 34907831
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
ID: 34908112
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
ID: 34908407
PS: The tank when full has a capasity of more than 18.000 liters....

Brgds
Jesper
0
 
LVL 17

Expert Comment

by:aflockhart
ID: 34908506
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
ID: 34908642
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
ID: 34908660
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
ID: 34908736
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
ID: 34908740
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
ID: 34913821
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
ID: 34914447
0
 

Author Closing Comment

by:jemagnussen
ID: 34915083
What I needed for this little app.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

680 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