Average Score with 3 criteria and nested NA in value range

%3CLINGO-SUB%20id%3D%22lingo-sub-2497176%22%20slang%3D%22en-US%22%3EAverage%20Score%20with%203%20criteria%20and%20nested%20NA%20in%20value%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2497176%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20advice%20on%20the%20formula%20to%20compute%20the%20average%20score%20of%20a%20test%20data%2C%20based%20on%203%20criteria.%26nbsp%3B%20The%20score%20range%20has%20nested%20'NA'%20together%20with%20numeric%20score%20values.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECriteria%201%3A%26nbsp%3B%20Group%20-%26nbsp%3B%20A%3C%2FP%3E%3CP%3ECriteria%202%3A%26nbsp%3B%20Test%20-%20M2%3C%2FP%3E%3CP%3ECriteria%203%3A%26nbsp%3B%20Task%20ID%20-%207004%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20to%20use%20%22AVERAGEIFS%22%20but%26nbsp%3B%20it%20ended%20up%20with%20error.%26nbsp%3B%20%26nbsp%3BI%20would%20appreciate%20it%20if%20someone%20could%20advise%20me%20on%20the%20solution%20for%20excel%202016%20and%20365%20(for%20my%20learning%20purpose).%26nbsp%3B%20I%20have%20attached%20the%20sample%20data%20in%20this%20discussion.%26nbsp%3B%20Thanks.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2497176%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-2497362%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20Score%20with%203%20criteria%20and%20nested%20NA%20in%20value%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2497362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1088661%22%20target%3D%22_blank%22%3E%40choongko%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20simply%3C%2FP%3E%0A%3CP%3E%3DAVERAGEIFS(G2%3AG14%2CB2%3AB14%2CK2%2CE2%3AE14%2CK3%2CD2%3AD14%2CK4)%3C%2FP%3E%0A%3CP%3EAVERAGEIFS%20will%20ignore%20text%20values%20such%20as%20%22NA%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, I am advice on the formula to compute the average score of a test data, based on 3 criteria.  The score range has nested 'NA' together with numeric score values. 

 

Criteria 1:  Group -  A

Criteria 2:  Test - M2

Criteria 3:  Task ID - 7004

 

I tried to use "AVERAGEIFS" but  it ended up with error.   I would appreciate it if someone could advise me on the solution for excel 2016 and 365 (for my learning purpose).  I have attached the sample data in this discussion.  Thanks.  

2 Replies

@choongko 

That's simply

=AVERAGEIFS(G2:G14,B2:B14,K2,E2:E14,K3,D2:D14,K4)

AVERAGEIFS will ignore text values such as "NA".

Thanks for your help. I thought I did that earlier, but I got an error. Couldn't figure it out. I tried again, and now it works!!! Must be have been a typo error.