# Calculating when a tank will reach 0 via excel

#### LaMaqu1na

##### New Member
Hello, I've spent quite some time working on this spreadsheet that will help me calculate when a tank with reach 0 at work. I have no formal excel training and am for the most part, self taught. What I am trying to do is calculate what time a tank of fluid will reach 0 based on two set points that I am able to trend via systems already in place at work. The draw from the tank is constant and does not change.

Here is what I have so far...

 Beginning Tank Level 83​ Current Tank Level 27​ Difference Changed 56​ Timeframe in minutes 60​ Minutes left before tank reaches 0 48​

This is the formula I used =((B2/B3)*B4)/60*100 to find out how many minutes I have left before that tank reaches 0.

I then used the following formula to find out the exact time I'll reach 0

In G8 I have
=now()
In G9 I have
=TIME(HOUR(G8),MINUTE(G8)+B5,SECOND(G8))

I must be doing something wrong because after the tank reached 0 I went back to check my sheet and the times didn't match up.

What I'd really like to do is type in the Beginning tank level, the current tank level, the interval of the two times and be told exactly when that tank will reach 0.

Another person helping me used my equations and then added his own and this is what he got...

 tank level 56​ % Change =B3/60​ min left =G2/G3​ G4=60 time 9:11 PM​ to MT 10:11 PM to 10% 10:03 PM

He used my difference changed to calculate when the tank will reach 0 based on a changing tank level... being able at any time to type in the tank level between the beginning tank level and 0 and then be able to instantly see when it'll reach 0.

I know this might come off as confusing and to be honest after typing this out I've achieved to confuse myself even more. Thank you in advance for any and all help.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Ezguy4u

##### Board Regular
Let's get the ball rolling. Now I was a solid C Student in Algebra. So this could be correct. I came up with 1 hour 28 minutes and around 55 seconds to empty the container. If you want to add this to a time to come up with when the container would be empty, we can work on that too. I am going to suggest we don't use the NOW() function. Just sayin. Anyway maybe we can get some of the A Students to weigh in and see what they come up with.

Book1
ABCDEFGHI
1
2Beginning Tank Level830.933333333units/per minute
3Current Tank Level27Total time * units/per minute = Beginning Tank Level
4Difference Changed56Total time = 83 / 0.93333
5Timeframe in minutes6088.9286Total Time in decemial form
6Minutes left before tank reaches 04855.71428571Convert decimal to minutes and seconds
788
801:28:55.714
Sheet1 (4)
Cell Formulas
RangeFormula
D2D2=B4/B5
D5D5=B2/D2
D6D6=60*(D5-88)

#### AhoyNC

##### Well-known Member
Perhaps this link will be of help.

#### LaMaqu1na

##### New Member
I'm sorry, I should have added that the tank from 83-0 took 2hrs 47minutes. I was able to trend that data after completion.

#### LaMaqu1na

##### New Member
Perhaps this link will be of help.
I'm not sure that would be of any help because these tanks are emptied and refilled by very large pumps controlling flow and all other measurements of the actual tank are unknown.

#### TotallyConfused

##### Board Regular
Hello LaMaqu1na
I want to WELCOME you to the site. This is a wonderful place full of very helpful information.

Going on when you said that, "The draw from the tank is constant and does not change" then I don't understand some of your numbers.
From your posting #1:
Beginning Tank Level. . . . . . . . . . . . . . 83
Current Tank Level . . . . . . . . . . . . . . . . 27
Difference Changed . . . . . . . . . . . . . . 56
Timeframe in minutes. . . . . . . . . . . . . .60
Minutes left before tank reaches 0 . . 48

For the moment, let's forget Excel and just do some simple math.

1 ) You ask how long it will take for the tank to go from 83 down to a level of '0'. According to your figures, it would take 60 + 48 = 108 minutes (1 hour 48 minutes) at the rate of 83/108 = 0.768519 units per minute. Yet in your posting #4, you said, "I'm sorry, I should have added that the tank from 83-0 took 2hrs 47minutes. I was able to trend that data after completion." Isn't that the answer to your original question and why the difference? At 2 hours 47 minutes (167 minutes) you were pumping at the rate of 83/167 = 0.497006 units per minute. Why the difference in units per minute and total time required?

2 ) If I'm reading those numbers correctly, you pumped out 56 units in 60 minutes = 56/60 = a rate of 0.9333333 units per minute. (confirmed by Ezguy4u in posting #2). Then you say it took 48 minutes to pump out the remaining 27 units which would be 27/48 = a rate of 0.5625 units per minute. Why the difference in rates per minute?

My answer: Using the figures from #2 above, then I'll say it would take you 83/0.93333 = 88.928889 minutes (1 hour and almost 29 minutes) which puts me in agreement with Ezguy4u post #2. As you can see from the numbers above here, I could come up with several different total times needed, depending on which numbers I used.

I'll be following this thread as I'm curious to see what the final result will be.

TotallyConfused

Replies
3
Views
120
Replies
0
Views
51
Replies
5
Views
77
Replies
5
Views
63
Replies
0
Views
254