comparing cells that are non contiguous and seperated by blank cells

First, Hello All! This is my first post on this forum. Down to the nitty gritty.

The setup..

I would like to compare the values of two cells in a range. If I have two adjacent cells (ie A1 and A2) A1 = 1 A2 = O, I can easily compare the two ie(=a1<a2, or =a1>a2 or what over operator you choose to use. However , here is the bug in the ointment, lets say I want to do a greater than or lesser than test between two cells that are separated by blank cells For instance, {0,"","","",1} where "" symbolizes a blank cell, so I would like the operator to still compare two cells in that range but skip back to the first nonblank cell that has a numerical value and then perform the test. Thanks in advance for your help!


-Grant

Actually no there are about 100 cells and looking back I realized I misstated the problem. The actual values where "" is blank should be zero values. Here is an actual string from the dataset:
1 2 3 4 5 6 7 8 9 10 11
A0.18 0.1798 0.1797 0.1797 0.1797 0.1796 0.1799 0.1806 0.1813 0.1813 0.1838
B=0 0 0 1 1 0 0 0 0 1 0. Suc
C<0 1 1 0 0 1 0 0 0 0 0
D>1 0 0 0 0 0 1 1 1 0 1

The results of the Row B are the True/False values of the = column where it compares data in two other cells in Row A. Therefore, B2's formula is =a1=a2 yields 0 for false. Row c is the result of the < test.... Therefore, Because the formula in C2 is (=A1<B2) it yields 1 for true. The C Row is for the greater than test again same stipulations for the previous two tests. However, A problem happens when I try to do an either greater than or lesser than test when two values are separated by a series of cells that test true for the equal to condition. Such a condition yields a few issues. As you can see in the table above note the results of columns 3:6. This yields two cells that are equal to each other and sandwiched between two cells B3,B6 that both test true for the less then condition.

The values in A1:A11 are a series of x coordinates in scatter plot. Basically I am counting the number if times the x coordinates changed direction in terms of ordinality. However, I started running into problems when I have cells that instantiate equal values because this could cause the following permutations of the problem... the following series are what the cooridinates test true for when separated by n number of cells that yield a true to the = test. . . 1. <====< ... not a change in ordinality. 2. >====> not a change in ordinality 3. <=====> change in ordinality 4. >=====< change in ordinality. I hope this clarifies things.

-Grant

Originally Posted by benishiryo

hi Grant, welcome to the forum. so there are always 2 numbers only? and you always want to use the first number vs last number? try:
=INDEX(A1:A4,MATCH(TRUE,INDEX(ISNUMBER(A1:A4),),0))>LOOKUP(2,1/ISNUMBER(A1:A4),A1:A4)

red portion for first number, blue for last