Here is the solution to include columns A through H. That's as far as we can
take this using nested IF() statements due to the built in limit of 7 nested
functions.
The first two columns are kind of special cases, as shown by your example
data and result.
For column A to be counted, the value in it simply needs to be greater than
the value in column B.
Column B needs to be both smaller than the value in column A and larger than
the value in column C to count.
For the rest of the series it is just a matter of back tracking to make sure
that the series continues to decline in value from right to left. Any time
that a value goes higher than that of the cell to the right of it, it causes
failure of the test at that point.
Here is the 8-column formula, split at the + symbols for easier reading:
=IF(A2>B2,1,0)+
IF(C2<B2,IF(A2>B2,1,0),0)+
IF(A2>B2,IF(B2>C2,1,0),0)+
IF(A2>B2,IF(B2>C2,IF(C2>D2,1,0),0),0)+
IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,1,0),0),0),0)+
IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,1,0),0),0),0),0)+
IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,1,0),0),0),0),0),0)+
IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,IF(G2>H2,1,0),0),0),0),0),0),0)
Here it is again so that you can easily cut and paste it:
=IF(A2>B2,1,0)+IF(C2<B2,IF(A2>B2,1,0),0)+IF(A2>B2,IF(B2>C2,1,0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,1,0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,1,0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,1,0),0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,1,0),0),0),0),0),0)+IF(A2>B2,IF(B2>C2,IF(C2>D2,IF(D2>E2,IF(E2>F2,IF(F2>G2,IF(G2>H2,1,0),0),0),0),0),0),0)
You can pick up a sample workbook with this solution in it along with a code
solution.
I'm not greatly pleased with the code, it's clumsy, but it does work and it
is easily modified for varying number of columns of data. Not sure how well
it work with fewer than 4 columns of data. That workbook can be downloaded
from my site here:
http://www.jlathamsite.com/uploads/SteveC_Sequences.xls - right click and
'save target as' to grab it.
One thing that continues to confuse me is that a sequence like 10 9 10 you
show as a count of 1, but 10 9 8 would be 3, there seems no way to get a
sequence of 2? Somehow I think that the 'answer' to this riddle may be why
the data in the second column has to be handled as a special case.
And you can call me Jerry - most people do.