Interperating graph data (extrapolating)

[RobbieC]

Hi there, I have a spreadsheet where I am entering reading from a soakaway test (to see how long water drains in a hole in the ground)

I have a table where I record my readings over time (I just record the depth in m):

From this data I can produce this graph:

So the blue line represents the readings that I have taken. The graph also shows the green lines which represent the 25% & 75% points of the test.

What I need to do is to calculate at what time (mins) the soakaway hits the 25% & 75% marks.

In this example, the 75% is 0.75*2 (2 being the total depth) which is 1.5. This happens to be one of the readings taken so in this example it's pretty easy to calculate (45 mins)

But the 25% mark is crossed without an exact reading, so I need to calculate the exact time the curve of the graph passes that point (somewhere around 12.5 mins)

Obviously, automating both of theses graph-crossing points to get the exact values is the aim of the exercise. Trouble is, I have no idea how to do this... must be a formula to extrapolate or something....

If you can possibly point me in the right direction, I'd be very grateful. It has been a journey to get this far and to get those 2 values would be the cherry on the cake

Thanks very much

Rob

 

[shg]

	A​	B​	C​	D​	E​	F​
1​	Time​	Drop​	​	Drop​	Time​	​
2​	0​	0.20​		0.5​	12.50​	E2: =PERCENTILE(A2:A16, PERCENTRANK(B2:B16, D2, 6))
3​	5​	0.30​		1.5​	45.00​
4​	10​	0.40​
5​	15​	0.60​
6​	20​	0.80​
7​	25​	0.90​
8​	30​	1.00​
9​	35​	1.20​
10​	40​	1.40​
11​	45​	1.50​
12​	50​	1.60​
13​	60​	1.80​
14​	75​	1.90​
15​	90​	1.95​
16​	105​	2.00​

 

[RobbieC]

Hi there shg, thank you very much for this. It works!!!

I take it that the '6' in the PERCENTRANK formula refers to the significant digits in the result

The only problem that I have is that the 2 arrays (time & depth) which are A3:A23 & B3:B23 respectively, may contain different number of readings. ie this above example contains 15 readings, but another soakaway may contain 17 or the full range of 21 readings.

Is it possible to adapt the formula for E2 to include the full possible range of used values entered

E2: =PERCENTILE(A3:A23, PERCENTRANK(B3:B23, D2, 6)) - this is the possible full range (21 readings), but if only 15 readings are taken this would correct itself the include only the range with values ie:

E2: =PERCENTILE(A3:A17, PERCENTRANK(B3:B17, D2, 6))

So it calculates the 'used range'

At the moment, if I include the full range into the equation for the 25% mark, it gives a value of 17.9 instead of 12.5 (this is probably because of the 6 blank readings at the end of the range...

I hope I've explained where I'm coming from.

Basically, I don't want to be adjusting the formula range, just have it automated...

Once again, thanks very much for your help shg. Much appreciated!

 

[RobbieC]

What I'm meaning to say, is that column A will always contain those time intervals (therefore there will be no blanks).

The range is the used range in column B...

 

[RobbieC]

Hi again shg, I have just realised that I can use this to calculate the used range in a seperate cell (F1):

=$B$2:INDEX(SOAKAWAY!$B:$B,COUNTA($B:$B))

then in E2 I can use INDIRECT to calculate:

=PERCENTILE(INDIRECT("B2:B"&F1), PERCENTRANK(INDIRECT("C2:C"&F1), D2, 6))

This all now works! Thanks very much again for your help!

 

[shg]

You're welcome.