Help

Is it possible to use the Rollup field to average values that are a formula?

Topic Labels: Formulas
Solved
Jump to Solution
1111 2
cancel
Showing results for 
Search instead for 
Did you mean: 
KevinT
5 - Automation Enthusiast
5 - Automation Enthusiast

I am using the field, "Total Days Open" to count the days between the date something is submitted and the date that work is completed. The formula is: 

IF(AND({Work Completed} = BLANK(), {Submission Date} = BLANK()), "",
IF({Work Completed} = BLANK(), DATETIME_DIFF(TODAY(), {Submission Date}, "d"), DATETIME_DIFF({Work Completed}, {Submission Date}, "d")))

On a separate tab, I want to calculate the average total of days based on the Health Plan link to the records I want to average. 

I have been trying to use the Rollup field but it is returning a value of "NaN"

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hm, any chance you could share an example base with this set up so that we could help you debug it?

Eyeballing your formula, I would suggest trying to make sure that the "IF()"s always output a number, and so instead of outputting "", output 0 instead

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hm, any chance you could share an example base with this set up so that we could help you debug it?

Eyeballing your formula, I would suggest trying to make sure that the "IF()"s always output a number, and so instead of outputting "", output 0 instead

Sorry for the late reply here! I was actually able to figure this one out. 

Your suggestion above was correct to have the formula output 0. So the new formula is: 

IF(AND({Work Completed} = BLANK(), {Submitted} = BLANK()),0,
IF({Work Completed} = BLANK(), DATETIME_DIFF(TODAY(), {Submitted}, "d"), DATETIME_DIFF({Work Completed}, {Submitted}, "d")))
 
On the Rollup field, I updated the formula to:
IF(OR(AVERAGE(values), AVERAGE(values)=0), AVERAGE(values))
 
The Rollup is now returning the correct averaged values of the date difference.