List

[greenleaf]

I have a list of numbers. for example:

1
2
3
4
5
6
4
2
1
0
3
4
5


Notice the numbers go from low to high, then go back down to low, then back up to high. The first high is 6, the low that follows it is 0, and the final high is 5.

Now, I have gazillions of data string, I just want to pick out the pivot points (meaning the highs or the lows). In other words, given the above string of numbers, the answer I want to derive at is simply 6,0 and 5. Do you know if I can do that via formulas?

Thanks in advance!!!

[SamuelT]

Hi,

How are you wanting your results returned? For instance, do you want everything in a single cell - i.e. 6,0,5, etc. or do you want each value in a single cell - i.e.

6
0
5

I'm thinking you can probably do something with the LARGE and SMALL formulas, but need some clarification.

Cheers,

SamuelT

[NBVC]

See attached for a suggestion.

Suggestion uses a helper column to find the pivot points..

then a count is done on these pivot points and another formula lists them in a column without blanks.

Note: the formula in E2 is an array formula and must be confirmed with CTRL+SHIFT+ENTER not just ENTER to get the { } brackets...then it can be copied down as far as you want.

Formula in B2:

=IF(AND(A3<A2,A2>A1),A2,IF(AND(A3>A2,A2<A1),A2,""))

copied down

Formula in D2 to get count of pivots:

=COUNT(B2:B14)

Formula in E2 to extract pivot points:

=IF(ROWS($B$1:$B1)>$D$2,"",INDEX($B$2:$B$14,SMALL(IF(ISNUMBER($B$2:$B$14),ROW($B$2:$B$14)-ROW($B$2)+1),ROWS($B$2:$B2))))

confirmed with CSE keys and copied down