finding the average of 5 appsheet fields with some zero's in it.

[Rehabman]

I have a appsheet virtual column that is suppose to find the average of 5 other fields. 

I can do this just fine,  except when one or more of the fields are empty or 0, they should be excluded from the average. But the zeros are calculated in the average.  How do i exclude the blanks and 0's from the average formula?

is there an easy away to do this.

Thanks for an help!!

[SkrOYC · Accepted solution]

AVERAGE(
  LIST(
    [Field1],
    [Field2],
    [Field3],
    [Field4],
    [Field5]
  )
  -LIST("")
  -LIST(0)
)

It should work, although I haven't had your need before

[Joseph_Seddik]

That would also unique his list, which might not be a desired behavior. 

[SkrOYC]

Yes.

The other way would require IFS, although you need to add the "/X" hardcoded

[Joseph_Seddik]

Upon thinking further about it, I see that in case of duplicates the result would be skewed; for you’ll have both wrong sum and wrong count. 

I’d add another column with 1 in case of blank, then sum/(count - count). 

[Rehabman]

SkrOYC,   this seems to work great, thank you

[Joseph_Seddik]

This is a general solution to calculate the average of a values in a column "Value", that allows for both duplicate and unique values. It accommodates for having both positive and negative values, and excludes blank or zero values from the average calculation.

1. Add a new column named "hasValue", with the following app formula:
   
   IF(ABS([Value]) > 0, 1, 0)
   
   
2. The average can be calculated using the following expression:
   
   1.0 * SUM(table[Value]) / SUM(table[hasValue])