Find first non #N/A value in either direction

y3tter

Board Regular
Joined
Nov 11, 2012
Messages
147
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.


Zone1#N/A87
Zone2#N/A77
Zone3#N/A65
Zone4#N/A50
Zone5#N/A46
Zone61039
Zone71033
Zone82220
Zone9256
Zone10356
Zone1142#N/A
Zone1256#N/A
Zone1366#N/A
Zone1478#N/A

<tbody>
</tbody>
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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:
Code:
B15: =INDEX($A1:$A14,MATCH(ISNA(B1),INDEX(ISNUMBER(B1:B14),0),0)-ISNUMBER(B1))

Is that something you can work with?
 
Last edited:
Upvote 0
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:
Code:
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!!
 
Upvote 0
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.
 
Upvote 0
Zone11150
Zone21140
Zone35130
Zone47125
Zone512100
Zone61794
Zone72391
Zone85077
Zone95562
Zone106248
Zone117733
Zone128428
Zone139510
Zone1410110



<tbody>
</tbody>


Here is an example of what I was trying to explain
 
Upvote 0
OK....Using the same data range
This regular formula, copied across to the right, returns the starting point of the series
Code:
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?
 
Upvote 0
OK....Using the same data range
This regular formula, copied across to the right, returns the starting point of the series
Code:
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
 
Upvote 0
I'm not seeing that issue. These are my results:
Code:
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?
 
Last edited:
Upvote 0
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?
 
Last edited:
Upvote 0
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:
Code:
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:
Code:
=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?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top