Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Excluding Zero values from an Average Rollup

Topic Labels: Formulas
Solved
Jump to Solution
1690 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Is it possible to exclude records that have a zero value from an average rollup?

1 Solution

Accepted Solutions
drassi
6 - Interface Innovator
6 - Interface Innovator

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.

See Solution in Thread

1 Reply 1
drassi
6 - Interface Innovator
6 - Interface Innovator

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.