Interperating graph data (extrapolating)

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
371
Office Version
  1. 2010
Platform
  1. Windows
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):
soakaway_values.jpg

From this data I can produce this graph:
soakaway_chart.jpg

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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,783
Office Version
  1. 2010
Platform
  1. Windows

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
Joined
Dec 14, 2016
Messages
371
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 14, 2016
Messages
371
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 14, 2016
Messages
371
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 7, 2008
Messages
21,783
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,758
Messages
5,626,683
Members
416,200
Latest member
Pulsar3000

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
Top