Complex Formula?

[Graywolf80]

I am looking find a formula tha will let me know how many times 2 cells move in the same direction value wise.

<TABLE style="WIDTH: 88pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=117 border=0><COLGROUP><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 id=td_post_2746492 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 44pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=59 height=20>0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=58>25</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">100</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">100</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">100</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">75</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>-1</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">25</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>-7</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">10</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">50</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>11</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">250</TD></TR></TBODY></TABLE>

If Column A goes up, and Column B goes up, I would like a cell to increase by one everytime it does it. If Column A and B both go down, again, I would like the same cell increase by one. If One Column goes up, down or stays the same and the other does not move in the same direction, I do not want it to increase this cell.

As some people say, this one is way beyond my paygrade. LOL

Thanks in advance.

 

[Aladin Akyurek]

What is the expected result with that sample?

 

[Graywolf80]

The expected result should be 6 times that Column A and Column B moved in the same direction UP/Down of total of 6 times. The times where the Column A remained the same at 3 for 3 cells in a row, there was no change so it wouldnt be counted. Same with Column B.

 

[Aladin Akyurek]

The expected result should be 6 times that Column A and Column B moved in the same direction UP/Down of total of 6 times. The times where the Column A remained the same at 3 for 3 cells in a row, there was no change so it wouldnt be counted. Same with Column B.

Thanks.

Control+shift+enter, not just enter...

=SUM(IF(A1:A8 > A2:A9,IF(B1:B8 > B2:B9,1)))+SUM(IF(A1:A8 < A2:A9,IF(B1:B8 < B2:B9,1)))

 

[Graywolf80]

I used that Formula and used all the range cells, but the answer comes out o be 0. Maybe I missed something.

 

[Aladin Akyurek]

I used that Formula and used all the range cells, but the answer comes out o be 0. Maybe I missed something.

You need to confirm the formula with control+shift+enter, not just with enter. That is, press the control and the shift keys at the same time while you hit the enter key.

 

[Graywolf80]

So after entering the formula, Press the Cntl Shft and Enter to accept?

 

[Graywolf80]

Re-entered, Then hit Cntl Shift and Enter at same time. Still gives me a zero

 

[shg]

Another way, but you still have to confirm by holding down the Ctrl and Shift keys, and then pressing Enter.

=SUM((SIGN(A2:A9-A1:A8)=SIGN(B2:B9-B1:B8))*(A2:A9<>A1:A8))

 

[Graywolf80]

That did it. Thanks much!!!