
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 10, 2019 07:34 AM
Is it possible to exclude records that have a zero value from an average rollup?
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 10, 2019 08:20 AM
There is no built-in aggregation function that does this. The easiest way I can think of to do this is simply create a formula column on the child table that filters out zeros, then do an AVERAGE() rollup on the formula field instead of directly on the numeric field. E.g.:
New “Nonzero” formula column on child table, with formula “IF(Value>0,Value)”
Rollup column on parent table, redefined as Rollup > Child Table > “Nonzero” > AVERAGE(values)
Should do what you want.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 10, 2019 08:20 AM
There is no built-in aggregation function that does this. The easiest way I can think of to do this is simply create a formula column on the child table that filters out zeros, then do an AVERAGE() rollup on the formula field instead of directly on the numeric field. E.g.:
New “Nonzero” formula column on child table, with formula “IF(Value>0,Value)”
Rollup column on parent table, redefined as Rollup > Child Table > “Nonzero” > AVERAGE(values)
Should do what you want.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 11, 2023 01:29 AM - edited Nov 11, 2023 01:50 AM
Hi @drassi ,
thanks, this helped me to solve my issue. However, there is no example, so I didn't figure it out the first time.
My solution was to return BLANK() instead of a 0, when the Formula encountered a BLANK() at the first or second value. If this is done, the AVERAGE() function of the Rollup skips these values.
- Values is the first table:
- Data: The data are either blanks or numbers from 1 to infinite. They represent "bad" and "good" occurrences.
- Formula: IF(AND({👍}=BLANK(), {👎}=BLANK()), BLANK(), IF({👍}=BLANK(),-{👎},IF({👎}=BLANK(),{👍},{👍}/({👎}+{👍}))))
- Rollup in the other table: IF(COUNTA(values)!=0, AVERAGE(values), BLANK())
Hope it helps other people!
Best regards
Johannes
