Using Time Results to Calculate Probability

Good Afternoon,

I had a friend ask me a question which I thought would be simple to sort out, but after two days of noodle scratching I felt I needed assistance.

Here is the scenario.

There is a large fluid tank that as a production process continues will drain at a fixed speed. That speed is 360 seconds per % point of drop. So to go from 45% full to 44% full takes 360 seconds, for example.

That tank starts draining automatically at an unknown time as production is started elsewhere in the plant. By manually pushing a button and interrogating the tank the operator (my friend) can tell how full it is. He enters these numbers in a Excel log. It is very important that he know when the tank will reach 0% as he needs to shut off the pumps and start a scouring process to remove the corrosive remnants of the material and reset for the next production run.

However, as he has more than one job to do at the same time (Don't we all) he cannot check the tank regularly. He needs to predict when this tank will reach 0% based on infrequent data. He gave me some sample data which I attached for your reference.

By using the TIME function he changes the first three individual fields he enters (HH, MM, SS) into standard 24h style time. These are the times he tests the tank and records the found % result. When the tank goes down a % point he calculates the time bracket by simple subtraction. So in the example when the tank goes down from 8% to 7% the value changed at some point in that 3m 14s window. When the tank goes from 7% to 6% it changed at some point in a 1m 55s window. With several of those brackets established he will try and guesstimate when it will hit 0%. If he shuts the pumps down early the leftover material is wasted (with much static from his manager) and if he shuts down late he can damage the pumps (with much static from his manager).

So here is the question. Given the fixed 360 second value, is there any way to take his entered data and give a probability guess of what time the tank will reach 0%? Obviously the more data entered will be more accurate, but can his Excel sheet show:

The tank will reach 0% between 15:14:04 and 15:14:45

for example, based on the time brackets recorded during the % changes???

Cheers. Hope I explained it well enough. Ask if you need more info.

Shad