Excel dynamic range offset formula exclude #n/a

Question

I have data that changes depending on the slicer, and I can't figure out the correct offset formula to select only data values that are valid or in other words, exclude any #N/A values. The data looks something like this:

|Column A   |  Column B   |Column C|
|----------------------------------|
|Jan        |   #N/A      |    #N/A|
|Feb        |   4         |     40 |
|Mar        |   5         |    38  |
|Apr        |   12        |     10 |
|May        |   #N/A      |    #N/A|
|Jun        |   #N/A      |    #N/A|

I want the offset range to select only Feb/Mar/Apr rows. But if the data changes because I click on a different filter (example below), I want it to still select only the non-error data (rows Jan/Feb/Mar/Apr):

|Column A   |  Column B  | Column C|
|----------------------------------|
|Jan        |   30       |      60 |
|Feb        |   8        |      83 |
|Mar        |   53       |      14 |
|Apr        |   11       |      41 |
|May        |   #N/A     |     #N/A|
|Jun        |   #N/A     |     #N/A|

Please help! Thank you.

what do you want to do with the non-error data? aggregate has many subfunctions that can be made to ignore errors. — user4039065
do the non-error rows come continuously one after other or there can be an error row(s) in between? — Karpak
@Jeeped i want the offset function to select the non-error data to make a graph, and the graph will change according to the slicer. — J.1
@Karpak as of now i've only seen error either after the values, or both before and after (as shown in examples above). is there a way to have the offset function select whatever data is available (non #N/A values) regardless of where it's located within the columns? — J.1
@Joyce, there could be a direct formula, I remember exploring similar to this few years ago. But couldn't recollect, what I did and how I did. However there is a round about way. I provided that as the answer to this post. — Karpak

Karpak Karpak · Accepted Answer · 2017-04-03T16:19:34

Joyce, You can create an additional set of cells with same dimension and use those cells in your offset. For creating the additional set of cells. Choose any one cell and enter the below formula as Array formula. (Enter the below formula and then press shift + enter). Then copy that cell to all other cells as mentioned in the attached image. Then you can base that new set of cells in your offset. I believe, this will address your requirements. No matter whether the rows or continuous or errors in between. If you use the offset function of the new set of cells, which would have only the non-error data and you can make a graph using that, and the graph will change according to the slicer. Only assumption that I made is that, if it is error both column B and Column C will have the error value. So, it filters only based on the value in column B.

=IFERROR(INDEX($A$1:$C$10,SMALL(IF(ISERROR($B$2:$B$10),"",ROW($B$2:$B$10)),ROW(A1)),COLUMN(A1)),"")

Excel dynamic range offset formula exclude #n/a

2 Answers