# Interperating graph data (extrapolating)

#### RobbieC

##### Active Member
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

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### shg

##### MrExcel MVP

 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

##### Active Member
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

##### Active Member
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

##### Active Member
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

##### MrExcel MVP
You're welcome.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,631
Messages
5,838,467
Members
430,549
Latest member
jayjay2022

### 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.

### Which adblocker are you using?

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

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