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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top