Oct 25 2021 11:00 AM - edited Oct 25 2021 11:01 AM
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.
Oct 25 2021 11:22 AM
Solution=AGGREGATE(1,6,J9,N9,R9)
Is this the formula you are looking for? I entered formula in T9 and copied down.
Oct 25 2021 12:40 PM
Oct 25 2021 11:22 AM
Solution=AGGREGATE(1,6,J9,N9,R9)
Is this the formula you are looking for? I entered formula in T9 and copied down.