Sum #n/a

[bjanger]

Hi,

I'm trying to sum a row of values, and then calculate the average value. In the row all zero values will be denoted #N/A. The zero values are denoted #N/A to remove the zero-entries when the values are plotted in a graph.

This is my formula for calculating the average, but i'm getting an error and i'm guessing that it has to do with ">0". How can this argument skip #N/A entries?

=SUM(O4:AS4)/(COUNTIF(O4:AS4;">0"))

BR
Bjanger

 

[dafan]

Just use

=AVERAGE(O4:AS4)

as AVERAGE skips zero values and blanks automatically.

 

[bjanger]

Hi Stefan,

I still get the #N/A error using the average formula...
Some cells have values, some have 0, others have =NA() (which shows as #N/A)

-Bjanger

 

[Richard Schollar]

Hi

Try

=SUMIF(O4:AS4,"<>#N/A")/COUNTIF(O4:AS4,"<>#N/A")

 

[bjanger]

Thanks, it's exactly what i needed!

BR
Bjanger

 

[Jon von der Heyden]

Another way using array formula:

=AVERAGE(IF(ISNUMBER(O4:AS4),O4:AS4))

which must be confirmed with control + shift + enter (not just enter)