Averaging results but one has N/A

[avalanche]

Hi,

I just received some great help on one question, and I need to follow up with another. On my Master spreadsheet, I have one tab that looks to the six other employee worsheets and averages out the "% Met" and brings that average into a this one master "Rollup" file.

However, one employee has N/A in the field (rather than 98.0% for ex.).
This then gives me a #VALUE result instead of an average of the 6 employees. Is there a way to write the formula to account for N/A ?

Thanks,
Tom

 

[MrKowz]

=SUMIF(C22:C33,">0")/MAX(1,COUNTIF(C22:C33,">0"))

should not be affected by an #N/A in C22:C33.

Therein the problem lies, these are not contiguous ranges (they are each on separate worksheets), and it is not an #N/A error, but rather a string value "N/A" entered by someone else.

I'm glad you popped in on this though, i'm struggling trying to get this figured out as well (although the easiest solution would be to just clear the cells with "N/A" )

 

[MrKowz]

Taking the idea that Aladin posted, testing for >0 rather than ISNUMBER, I came up with this working solution:

Note, this will still require the CTRL+SHIFT+ENTER

=SUM(IF('Jenny Boyd'!M5>0,'Jenny Boyd'!M5,""),IF('Kimberly Britton'!M5>0,'Kimberly Britton'!M5,""),IF('Karzett Parham'!M5>0,'Karzett Parham'!M5,""))/COUNT(IF('Jenny Boyd'!M5>0,'Jenny Boyd'!M5,""),IF('Kimberly Britton'!M5>0,'Kimberly Britton'!M5,""),IF('Karzett Parham'!M5>0,'Karzett Parham'!M5,""))

 

[avalanche]

Hey Aladin, thanks for chiming in but that still didnt work. Since I'm looking to all these multiple sheets, it still returns #VALUE.

 

[avalanche]

HOT ****! That worked! Thank you to both of you and I've told all my coworkers around me about Mr.Excel for help....you guys rock THANK YOU!