How to average large formulas ignoring 0

[nsmjc1]

So in Cell K12 there is a rather large formula (I condensed it for this example).

This formula is to average out the respective cells in column J. For each cell there is a possibility of 9 different entries. NRT, N/O, 1,2,3,4,5,6,7

So based on the formula in K12 I need Cell J12 to Display the answer.

if any one of the Cells referenced in the formula are NRT then I need it to Display NRT. Which the Formula does now.

The problem comes in when a Cell is N/O (Not Observed)

How do I get the formula to Ignore N/O currently I have it set up to recognize N/O as 0. But excel averages 0 in and it affects the answer.

I know in a simple formula I can add <>0 to the formula to ignore zeros. what I cannot figure out is how to write it into a bigger formula such as in K12.

For example the current numbers in Cells J17:J25 should average 5 however the formula averages it as 1 because it calculates N/O as 0.

I could not attach a sample problem however here is a link to it.

https://onedrive.live.com/redir?res...75&authkey=!AOJMGJwmsV2ipL8&ithint=file,.xlsx

 

[Gerald Higgins]

Hi, welcome to the board.

Perhaps something like this

=IF(COUNTIF(J1:J100,"NRT")>0,"NRT",SUMIF(J1:J100,"<>N/O")/(COUNTA(J1:J100)-COUNTIF(J1:J100,"N/O")))

 

[Jonmo1]

Try

=IF(COUNTIF(J17:J25,"NRT"),"NRT",AVERAGE(J17:J25))

 

[XOR LX]

Cross-posted (and with 6 replies already):

how to average large formulas ignoring 0 and blank