SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2882928%22%20slang%3D%22en-US%22%3Eaverage%20non-contiguous%20cells%20while%20ignoring%20%23N%2FA%20errors%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2882928%22%20slang%3D%22en-US%22%3E%3CP%3Eello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20enter%20a%20formula%20that%20will%20calculate%20the%20average%20of%20three%20non-contiguous%20cells%20across%20a%20row%20of%20data%2C%20while%20also%20accounting%20for%20-%20and%20excluding%20-%20cells%20containing%20%23N%2FA.%20This%20formula%20will%20need%20to%20be%20copied%20down%20multiple%20rows.%20I%20have%20tried%20several%20nested%20array%20formulas%20using%20AVERAGEIF%20and%20CHOOSE%20(to%20choose%20the%20non-contiguous%20cells)%20and%20keep%20getting%20errors%20returned.%20It%20seems%20that%20AVERAGEIF%20will%20not%20allow%20me%20to%20average%20cell%20values%20that%20are%20non-contiguous.%3C%2FP%3E%3CP%3E%3CU%3ENOTE%3A%20%23N%2FA%20needs%20to%20be%20in%20my%20spreadsheet%20for%20the%20following%20reasons%3A%3C%2FU%3E%3C%2FP%3E%3CP%3EIt%20is%20necessary%20for%20excel%20to%20return%20%23N%2FA%20error%20is%20some%20cells%20of%20my%20spreadsheet%20in%20order%20to%20create%20gaps%20in%20graphed%20data%3B%20thus%2C%20it%20is%20not%20advantageous%20to%20correct%20the%20%23N%2FA%20error%20-%20I%20actually%20wrote%20a%20formula%20to%20intentionally%20place%20%23N%2FA%20in%20my%20spreadsheet.%20I%20have%20800%2B%20rows%20and%20multiple%20columns%20of%20data%20and%20calculations%20that%20are%20all%20referenced%20and%20sorted%20by%20a%20dynamic%20array.%20If%20cells%20are%20empty%2C%20the%20dynamic%20SORTYBY%20array%20places%20a%20%220%22%20value%20in%20the%20empty%20cells%2C%20which%20is%20not%20helpful%20when%20graphing%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2882928%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2883048%22%20slang%3D%22en-US%22%3ERe%3A%20average%20non-contiguous%20cells%20while%20ignoring%20%23N%2FA%20errors%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2883048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F954335%22%20target%3D%22_blank%22%3E%40ccampbell2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAGGREGATE(1%2C6%2CJ9%2CN9%2CR9)%3C%2FP%3E%3CP%3EIs%20this%20the%20formula%20you%20are%20looking%20for%3F%20I%20entered%20formula%20in%20T9%20and%20copied%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2883343%22%20slang%3D%22en-US%22%3ERe%3A%20average%20non-contiguous%20cells%20while%20ignoring%20%23N%2FA%20errors%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2883343%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much.%20Yes%2C%20that%20works%20perfectly!%20I%20did%20see%20that%20function%20and%20didn't%20quite%20understand%20the%20application.%20But%20I%20see%20what%20you%20did%20and%20it%20makes%20perfect%20sense.%3C%2FLINGO-BODY%3E
New Contributor

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 Replies
best response confirmed by ccampbell2021 (New Contributor)
Solution

@ccampbell2021 

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

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

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.