Count number cells that are consecutively higher/lower than actual value

[arete]

I need help with a formula that calculates the number of consecutive cells upwards that contain higher (column D) or lower (column E) values than the actual.
I have manually made the below table, to show the desired result.
I guess this can be done with the proper combination of VLOOKUP, OFFSET, INDEX, COUNT, COUNTIF and maybe LARGE functions.
Column B can contain duplicates of values, that makes simple VLOOKUP solutions inappropriate. Simple nested IF's are also impossible due to the many thousand rows to be processed.
Thank you!

 

[bertie]

If you are happy to insert an extra couple of columns, C & D in the example then you can use the formula listed. Summing the totals is a simple matter of summing Columns C & D.

This does throw up a problem for the last row in the data, you will need to decide how you want to handle this.


Sheet1

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 89px;"><col style="width: 89px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height: 68px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="background-color: rgb(0, 0, 128); color: rgb(255, 255, 255);">Index</td><td style="background-color: rgb(0, 0, 128); color: rgb(255, 255, 255);">Value</td><td style="background-color: rgb(0, 0, 128); color: rgb(255, 255, 255);">Increment x</td><td style="background-color: rgb(0, 0, 128); color: rgb(255, 255, 255);">Increment y</td><td style="background-color: rgb(0, 0, 128); color: rgb(255, 255, 255);">Higher than x previous cell</td><td style="background-color: rgb(0, 0, 128); color: rgb(255, 255, 255);">Higher than y previous cell</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="text-align: right;">1</td><td style="text-align: right;">-76</td><td style="text-align: right;">1</td><td style="text-align: right;">0</td><td style="text-align: right;">1</td><td style="text-align: right;">0</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: right;">2</td><td style="text-align: right;">1187</td><td style="text-align: right;">0</td><td style="text-align: right;">1</td><td style="text-align: right;">0</td><td style="text-align: right;">1</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: right;">3</td><td style="text-align: right;">539</td><td style="text-align: right;">1</td><td style="text-align: right;">0</td><td style="text-align: right;">2</td><td style="text-align: right;">0</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: right;">4</td><td style="text-align: right;">53520</td><td style="text-align: right;">0</td><td style="text-align: right;">1</td><td style="text-align: right;">0</td><td style="text-align: right;">2</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: right;">5</td><td style="text-align: right;">35216</td><td style="text-align: right;">1</td><td style="text-align: right;">0</td><td style="text-align: right;">3</td><td style="text-align: right;">0</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td>=IF(B2<B3,1,0)<b3,1,0)></b3,1,0)></td></tr><tr><td>D2</td><td>=IF(B2>B3,1,0)</td></tr><tr><td>E2</td><td>=IF(C2=1,1,0)</td></tr><tr><td>F2</td><td>=IF(D2=1,1,0)</td></tr><tr><td>A3</td><td>=+A2+1</td></tr><tr><td>C3</td><td>=IF(B3<b4,1,0)></b4,1,0)></td></tr><tr><td>D3</td><td>=IF(B3>B4,1,0)</td></tr><tr><td>E3</td><td>=IF(C3=0,0,MAX($E$2:E2)+1)</td></tr><tr><td>F3</td><td>=IF(D3=0,0,MAX($F$2:F2)+1)</td></tr><tr><td>A4</td><td>=+A3+1</td></tr><tr><td>C4</td><td>=IF(B4<b5,1,0)></b5,1,0)></td></tr><tr><td>D4</td><td>=IF(B4>B5,1,0)</td></tr><tr><td>E4</td><td>=IF(C4=0,0,MAX($E$2:E3)+1)</td></tr><tr><td>F4</td><td>=IF(D4=0,0,MAX($F$2:F3)+1)</td></tr><tr><td>A5</td><td>=+A4+1</td></tr><tr><td>C5</td><td>=IF(B5<b6,1,0)></b6,1,0)></td></tr><tr><td>D5</td><td>=IF(B5>B6,1,0)</td></tr><tr><td>E5</td><td>=IF(C5=0,0,MAX($E$2:E4)+1)</td></tr><tr><td>F5</td><td>=IF(D5=0,0,MAX($F$2:F4)+1)</td></tr><tr><td>A6</td><td>=+A5+1</td></tr><tr><td>C6</td><td>=IF(B6<b7,1,0)></b7,1,0)></td></tr><tr><td>D6</td><td>=IF(B6>B7,1,0)</td></tr><tr><td>E6</td><td>=IF(C6=0,0,MAX($E$2:E5)+1)</td></tr><tr><td>F6</td><td>=IF(D6=0,0,MAX($F$2:F5)+1)</td></tr></tbody></table></td></tr></tbody></table>

 

[arete]

Thanks for your reply, bertie!

I don't see that this is the proper solution:

- Your formula counts everything from the beginning, but I would like to get relative counts -> please check row 14 in my example: 129,838 is smaller than 149,154 (one cell above), 147,666 (two cells above) and 139,695 (three cells above) but not smaller than 129,216 (four cells above). So number 3 goes into the "Lower than previous y values" column. Another example -> check row 21: 144,326 is higher than 136,579 (one cell above), 136,365 (two cells above), 137,807 (three cells above) and 130,210 (four cells above) but not higher than 145,119 (five cells above). So number 4 goes into the "Higher than previous x values" column.

- Your E and F columns are practically the same (I need the count for higher and lower values)

- Your spreadsheet formulas contain incomplete formulas for all cells in column C

 

[arete]

Anyone, a good idea?

 

[Domenic]

Try...

C3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(ROW($B$2:B3)>MAX(IF($B$2:B3>B3,ROW($B$2:B3))),IF($B$2:B3 < B3,1)))

D3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(ROW($B$2:B3)>MAX(IF($B$2:B3 < B3,ROW($B$2:B3))),IF($B$2:B3>B3,1)))

 

[arete]

Thank you very much, Domenic! It seems to work perfectly, you are a genius!