Formula to extrapolate time from depth (2 columns)

[RobbieC]

Hi there, I have 2 columns of data collected from a 'water soakaway'

The hole is 10 metres deep and is filled (or nearly) with water. The timer begins. The overall depth of the water is recorded at each interval (which in this example, I have used 1 minute interval, but it could be anything)
Column A (minutes) & column B (depth) - as you can see the first reading at 0mins is 9m (not filled to the very top)

What I'm looking to do is to be able to extrapolate the from the 25%, 50% & 75% depth figures and get the time passed for each of these depths

In this example, the line is linear and we know that the 50% depth mark is 4.5m. Similarly, we can work out that the 25% Empty mark is 6.75m and the 75% empty mark is 2.25

I need to be able to work out the time passed as each of these depths is reached...

Can anyone point me in the right direction?

The relevant columns with data are A2:A40 (time) and B2:B40 (depth)

Thanks very much

 

[jasonb75]

Perhaps, for 25% empty (75% full)

=LOOKUP(B2*(1-25%),B:B,A:A)

The correct formula depends on whether you expect 6.0, 7.0, 6.42 or 6:26 as the correct answer.

edit:- realised the mistake in my formula after seeing @etaf's post below, I'm going to hold off on correcting it until you have clarified which result would be expected (closest measured match or accurately calculated result, if closest measure, should it be earlier, later or absolute closest?).

 

[etaf]

would a lookup work, if you added a column to calc the % based on the starting volume and the lookup to nearest value

something like

Book2
	A	B	C	D	E	F
1	Time	Depth	%		Required Depth	Time point
2	00:00:00	9.000	100%
3	00:08:08	8.243	92%		75%	00:20:13
4	00:10:33	8.178	91%		50%	00:29:53
5	00:12:58	8.001	89%		25%	00:37:08
6	00:15:23	7.880	88%
7	00:17:48	7.573	84%
8	00:20:13	7.341	82%
9	00:22:38	6.556	73%
10	00:25:03	5.850	65%
11	00:27:28	5.079	56%
12	00:29:53	4.577	51%
13	00:32:18	3.864	43%
14	00:34:43	3.235	36%
15	00:37:08	2.603	29%
16	00:39:33	2.074	23%
17	00:41:58	1.961	22%
18	00:44:23	1.514	17%
19	00:46:48	1.087	12%
20	00:49:13	0.188	2%
21	00:51:38	0.134	1%
22	00:54:03	0.118	1%
23	00:56:28	0.000	0%
Sheet1

Cell Formulas
Range	Formula
F3:F5	F3	=INDEX(A:A,MATCH(E3,C:C,-1))
C2:C23	C2	=B2/$B$2

 

[McGuilliam]

I would solve this with an regression analysis. To do this you should make sure your VBA analysis toolpack is turned on. This would allow you to run an regression analysis under the data tab with B2:B40 as Y-value and A2:A40 . With the output you can can calculate the exact point by ((depth-intercept)/X variable)

 

[RobbieC]

Hi guys, thanks for your help.

As this is technically an 'engineering' log, the data needs to be as accurate as possible, so getting a MATCH as close as possible isn't really accurate enough...

Getting the time to the closest second would be ideal...

I've got it to plot the values onto a graph, so maybe that can be used to extrapolate the exact time it passes the marks?

 

[McGuilliam]

Hi guys, thanks for your help.

As this is technically an 'engineering' log, the data needs to be as accurate as possible, so getting a MATCH as close as possible isn't really accurate enough...

Getting the time to the closest second would be ideal...

View attachment 25422
I've got it to plot the values onto a graph, so maybe that can be used to extrapolate the exact time it passes the marks?

Hi Robbie

if you work with a regression analysis this would get you pretty close. If you rather work with percentages you can off course calculate these from your raw data and use this as data in your regression.