Calculating when a tank will reach 0 via excel

[LaMaqu1na]

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.

 

[Ezguy4u]

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
	A	B	C	D	E	F	G	H	I
1
2	Beginning Tank Level	83		0.933333333	units/per minute
3	Current Tank Level	27			Total time * units/per minute = Beginning Tank Level
4	Difference Changed	56			Total time = 83 / 0.93333
5	Timeframe in minutes	60		88.9286	Total Time in decemial form
6	Minutes left before tank reaches 0	48		55.71428571	Convert decimal to minutes and seconds
7				88
8				01:28:55.714
Sheet1 (4)

Cell Formulas
Range	Formula
D2	D2	=B4/B5
D5	D5	=B2/D2
D6	D6	=60*(D5-88)

 

[AhoyNC]

Perhaps this link will be of help.

https://www.haywardflowcontrol.com/assets/documents/flowcontrol/pdf/VessTime.pdf

 

[LaMaqu1na]

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]

Perhaps this link will be of help.

https://www.haywardflowcontrol.com/assets/documents/flowcontrol/pdf/VessTime.pdf

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]

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