Cumulative distance between intercept points on Excel graphs

[stubroad]

Hi there, hoping you can help with the following:

Looking at the chart below, it is possible to use an excel formula/VBA to calculate the cumulative time below a certain value of x (as indicated by the arrows I've added to the chart)?

NB The graph shows the variation in tide height (x) over time .

The graph looks like this:

The data looks a littles something like this : (extract)

Really stumped on where to begin with this one; any suggestions or input would be very gratefully received.

Stuart

(Excel 2007 & Windows Vista)

 

[gardnertoo]

Assuming you have the actual recorded data and not just the curve, this will work:

Book1
	A	B	C	D
1		Levels of interest:	4	2
2	Date/Time	Tide	Minutes above	Minutes below
3
4	3/7/09 4:30 AM	2.57	0	0
5	3/7/09 4:36 AM	2.52	0	0
6	3/7/09 4:42 AM	2.47	0	0
7	3/7/09 4:48 AM	2.43	0	0
8	3/7/09 4:54 AM	2.39	0	0
9	3/7/09 5:00 AM	2.35	0	0
10	3/7/09 5:06 AM	2.3	0	0
11	3/7/09 5:12 AM	2.26	0	0
12	3/7/09 5:18 AM	2.22	0	0
13	3/7/09 5:24 AM	2.18	0	0
14	3/7/09 5:30 AM	2.13	0	0
15	3/7/09 5:36 AM	2.09	0	0
16	3/7/09 5:42 AM	2.06	0	0
17	3/7/09 5:48 AM	2	0	0
18	3/7/09 5:54 AM	1.94	0	6
19	3/7/09 6:00 AM	1.92	0	12
20	3/7/09 6:06 AM	1.88	0	18
21	3/7/09 6:12 AM	1.84	0	24
22	3/7/09 6:18 AM	1.8	0	30
23	3/7/09 6:24 AM	1.76	0	36
24	3/7/09 6:30 AM	1.73	0	42
25	3/7/09 6:36 AM	1.71	0	48
26	3/7/09 6:42 AM	1.65	0	54
27	3/7/09 6:48 AM	1.62	0	60
28	3/7/09 6:54 AM	1.59	0	66
Sheet1

Graphing it could look like this, if you add some "dummy" columns to differentiate between the normal tide levels and your extremes: