average non-contiguous cells while ignoring #N/A errors in excel

[ccampbell2021]

Hello, 

I need to enter a formula that will calculate the average of three non-contiguous cells across a row of data, while also accounting for - and excluding - cells containing #N/A. This formula will need to be copied down multiple rows. I have tried several nested array formulas using AVERAGEIF and CHOOSE (to choose the non-contiguous cells) and keep getting errors returned. It seems that AVERAGEIF will not allow me to average cell values that are non-contiguous.

NOTE: #N/A needs to be in my spreadsheet for the following reasons:

It is necessary for excel to return #N/A error is some cells of my spreadsheet in order to create gaps in graphed data; thus, it is not advantageous to correct the #N/A error - I actually wrote a formula to intentionally place #N/A in my spreadsheet. I have 800+ rows and multiple columns of data and calculations that are all referenced and sorted by a dynamic array. If cells are empty, the dynamic SORTYBY array places a "0" value in the empty cells, which is not helpful when graphing data.

[#2 · Accepted solution]

@ccampbell2021 

=AGGREGATE(1,6,J9,N9,R9)

Is this the formula you are looking for? I entered formula in T9 and copied down.

[#3]

Thank you very much. Yes, that works perfectly! I did see that function and didn't quite understand the application. But I see what you did and it makes perfect sense.