Find first non #N/A value in either direction

[y3tter]

In Column B1:B14 there are values for relative distance in elevation, which are divided in 14 zones. If you are moving down in elevation the #N/A's will be at the top zones, if you begin moving up in elevation #N/A's will be at the bottom. Basically any zone that is not in the direction in which you are moving will be #N/A. How can you find the the first real value that is not a #N/A, by first looking for if the #N/A is at the top or bottom of the column and then returning the first value after the #N/A. Also, if you are in Zone1 moving down there will be no #N/A's and if you are in Zone14 moving up there will be no #N/A's. There shouldn't be any instance of moving up out of Zone1 or below Zone14.

Below is an example of the 14 zones, the second column would be moving down in elevation while being in Zone6, and the third column is an example of moving up and being in Zone10.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Zone1[/TD]
[TD]#N/A[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]Zone2[/TD]
[TD]#N/A[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]Zone3[/TD]
[TD]#N/A[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]Zone4[/TD]
[TD]#N/A[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Zone5[/TD]
[TD]#N/A[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]Zone6[/TD]
[TD]10[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]Zone7[/TD]
[TD]10[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]Zone8[/TD]
[TD]22[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Zone9[/TD]
[TD]25[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Zone10[/TD]
[TD]35[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Zone11[/TD]
[TD]42[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Zone12[/TD]
[TD]56[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Zone13[/TD]
[TD]66[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Zone14[/TD]
[TD]78[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ron Coderre]

Maybe this....
With your sample data in A1:C14, this regular formula copied across to the right, returns the Zone associated with the first non-error value after or before the #N/A! errors, respectively:

B15: =INDEX($A1:$A14,MATCH(ISNA(B1),INDEX(ISNUMBER(B1:B14),0),0)-ISNUMBER(B1))

Is that something you can work with?

 

[y3tter]

Maybe this....
With your sample data in A1:C14, this regular formula copied across to the right, returns the Zone associated with the first non-error value after or before the #N/A! errors, respectively:

B15: =INDEX($A1:$A14,MATCH(ISNA(B1),INDEX(ISNUMBER(B1:B14),0),0)-ISNUMBER(B1))

Is that something you can work with?

That works PERFECTLY!! Thank you so much!!

 

[y3tter]

Ron Coderre, would it be possible to expand the formula, for an instance that there are no #N/A errors? Moving down in elevation from Zone1 would be in ascending order and moving up from Zone14 would be descending. In either instance the first two low values would be the same.

 

[y3tter]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Zone1[/TD]
[TD]1[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Zone2[/TD]
[TD]1[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]Zone3[/TD]
[TD]5[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]Zone4[/TD]
[TD]7[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]Zone5[/TD]
[TD]12[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Zone6[/TD]
[TD]17[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]Zone7[/TD]
[TD]23[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]Zone8[/TD]
[TD]50[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]Zone9[/TD]
[TD]55[/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD]Zone10[/TD]
[TD]62[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]Zone11[/TD]
[TD]77[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]Zone12[/TD]
[TD]84[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]Zone13[/TD]
[TD]95[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Zone14[/TD]
[TD]101[/TD]
[TD]10


[/TD]
[/TR]
</tbody>[/TABLE]


Here is an example of what I was trying to explain

 

[Ron Coderre]

OK....Using the same data range
This regular formula, copied across to the right, returns the starting point of the series

B14: =INDEX($A1:$A14,MATCH(AGGREGATE(5,6,B1:B14),B1:B14,0)
+IF(IFERROR(B1,1)>IFERROR(B14,14),COUNTIF(B1:B14,AGGREGATE(5,6,B1:B14))-1,0))

Is that something you can work with?

 

[y3tter]

OK....Using the same data range
This regular formula, copied across to the right, returns the starting point of the series

B14: =INDEX($A1:$A14,MATCH(AGGREGATE(5,6,B1:B14),B1:B14,0)
+IF(IFERROR(B1,1)>IFERROR(B14,14),COUNTIF(B1:B14,AGGREGATE(5,6,B1:B14))-1,0))

Is that something you can work with?

Very close, the only issue is being at the top in Zone1. The lower limit will always have duplicate values. So, if in Zone1, both Zone1 and Zone2 will have the same values. Same for Zone14, Zone14 and Zone13 will have the same values. The formula seems to work being in Zone14, but if it was in Zone1 with no #N/A errors, it reports being in Zone2 instead of Zone1

 

[Ron Coderre]

I'm not seeing that issue. These are my results:

Zone1       1       150       #N/A      87
Zone2       1       140       #N/A      77
Zone3       5       130       #N/A      65
Zone4       7       125       #N/A      50
Zone5       12      100       #N/A      46
Zone6       17       94        10       39
Zone7       23       91        10       33
Zone8       50       77        22       20
Zone9       55       62        25        6
Zone10      62       48        35        6
Zone11      77       33        42       #N/A
Zone12      84       28        56       #N/A
Zone13      95       10        66       #N/A
Zone14     101       10        78       #N/A
Results  Zone1   Zone14     Zone6      Zone10

Perhaps your data is slightly different in a significant way?

 

[y3tter]

I found the issue, in one area there were only 12 zones, so Zone13 and Zone14 values were blank. In that instance, Zone2 was selected instead of Zone1. Typically there will always be 14 zones, but in some rare instances there could be less. Is it possible to make the formula dynamic or ignore blank cells?

 

[Ron Coderre]

If you convert each data set to an Excel Table, the range references will adjust automatically as the number of rows expands/contracts.
Using an Excel Table named Table1 with this data:

ZoneRef     Cycle1   Cycle2     Cycle3
Zone1        #N/A       150          1
Zone2        #N/A       140          1
Zone3           5       130          5
Zone4           7       125          7
Zone5          12       100         12
Zone6          17        94         17
Zone7          23        91         23
Zone8          50        77         50
Zone9          55        62         55
Zone10         62        48         62
Zone11         77      #N/A         77
Zone12         84      #N/A         84

This regular forumula in any cell below the Cycle1 column returns that starting point:

=INDEX(Table1[[ZoneRef]:[ZoneRef]]
,MATCH(AGGREGATE(5,6,Table1[Cycle1]),Table1[Cycle1],0)
+IF(IFERROR(INDEX(Table1[Cycle1],1),1)>IFERROR(INDEX(Table1[Cycle1],ROWS(Table1[Cycle1]))
,ROWS(Table1[Cycle1])),COUNTIF(Table1[Cycle1],AGGREGATE(5,6,Table1[Cycle1]))-1
,0))

Admittedly, the formula looks intimidating, but only because of the Structured References
which actually document the formula very nicely.
To apply that formula to other columns, use the fill handle (in the lower right corner of the cell)
to drag the formula to the right.
(Copy/Paste won't work because structure reference formulas are tenacious)
Does that help?