Pick out the Min / Max in a running lists

[CaptnPea]

All, did some reading and seems to be some talented people here, and I have a problem.

Collected data from a force displacement recorder (Instron), and what happened due to the type of part being measured was the load increased until a brake gave way, it then decreased and loaded until it broke free again.

So I have some 4000+ data X Y points and I want to clean them up and only see the loadings that the brake broke free at.

So I have data like this.... example starting at data point 1847 to 1872
| A | B | C
COUNT X Y
1847 -3.38401 -98.18226
1848 -3.38451 -91.28931
1849 -3.38517 -84.80958
1850 -3.39 -90.98323
1851 -3.39183 -96.88142
1852 -3.39399 -102.70217
1853 -3.39633 -108.7611
1854 -3.3985 -114.54858
1855 -3.40066 -120.31826
1856 -3.40283 -125.94379
1857 -3.40467 -118.2516
1858 -3.40517 -110.03141
1859 -3.40567 -101.9672
1860 -3.40617 -95.01661
1861 -3.40684 -88.25921
1862 -3.41183 -94.05978
1863 -3.41366 -99.88268
1864 -3.41583 -105.72916
1865 -3.41799 -111.50193
1866 -3.42016 -117.43785
1867 -3.42233 -123.41609
1868 -3.4245 -129.32533
1869 -3.42717 -120.90909
1870 -3.42751 -114.71156
1871 -3.42784 -108.49783
1872 -3.42818 -102.66275


I would like to "pick out" the bold values for the whole set of data, or better for a range of rows I specify. Notice that the values are local minimums. The number of rows between these minimals is not consistent throughout the whole set of data, some may be 10 rows apart others 8, 15, 12 etc...

I would like the results to be in a new set of columns, X1 / Y1, that show only the picked out data. Anyone have any brilliant ideas how to?

Possialbe Inputs:
Start Row
End Row
Max number of rows to look at to pick the max (ie how big of a range to search before indexing to the next range)

Output:
| D | E | F |
Count X Y
1856 -3.40283 -125.94379
1868 -3.4245 -129.32533
....
....


I did something like this years ago to clean up data in a lab I did in college, and know it's going to take some logic and indexing but, just do not practice that in Excel very often.

Any help I can get would be awsome, I will publish the results for all to see.

[martindwilson]

why isnt row one a minimum as it goes up again at row 3

[CaptnPea]

It could be but in this little example hunk of data we are not seeing the row before it, ie row 1846 in the Counter....
Working on it a little I can get an AND argument to work to find points that have higher values on either side of them....
=AND(C25<C24,C25<C26)
this results in a FALSE if the number is not a local min with larger values to either side, and returns TRUE otherwise.

[CaptnPea]

Getting Closer... this works to create the graph:

In Column E
Count number (located in A)
=IF(AND(C25<C24,C25<C26),A25,NA())
In Col, F (returns NA if not true, and the X value if true)
=IF($E24,B25)
In Col, G (returns NA if not true, and the Y value if true)
=IF($E24,C25)

But, if I try to find STDEV of the new list that contains a bunch of #NA that is an Error.

[martindwilson]

if you are happy the and gives you want you want then using an IF to return row we can index match to get result shown
oops to many ands in there should be =IF(AND(C2<C1,C1<C3),ROW(),"")