Calculating when a tank will reach 0 via excel

LaMaqu1na

New Member
Joined
Oct 20, 2020
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
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 MT10: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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
126
Office Version
  1. 2019
Platform
  1. Windows
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)
 

LaMaqu1na

New Member
Joined
Oct 20, 2020
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
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.
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
235
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,573
Messages
5,548,853
Members
410,880
Latest member
LBrand
Top