Averaging a range of non-required item totals if "ERR" is not present in the range

[drake171]

I'm having trouble writing a formula that does the following:

• If "ERR" is present in any cells within the range, return "ERR"
• If "ERR" is not present in the range, return the average of the range

Seems like this should be a simple formula I just haven't figured out how to make it work as expected!

I am trying to obtain total average compliance for standard items (16 total) as well as area-specific items (could be zero items, or up to 10 items depending on the area). For the standard/required items I used the following formula, it just isn't applicable for the non-required items, so I'm stuck:

=IF(COUNT(AM10:BB10)=16,AVERAGE(AM10:BG10),"ERR")

Any help is greatly appreciated!

 

[Aladin Akyurek]

Is this ERR just a text value, that is, it is not an error value Excel generates?

If text...

=IF(ISNUMBER(MATCH("err",AM10:BG10,0)),"ERR",AVERAGE(AM10:BG10))

 

[drake171]

"ERR" is a text value, correct. You fixed my issue. Thank you so much!

 

[Aladin Akyurek]

"ERR" is a text value, correct. You fixed my issue. Thank you so much!

You are welcome.