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

Topic Labels: Formulas
Solved
670 2
cancel
Showing results for
Did you mean:  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  16 - Uranus

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

2 Replies 2  16 - Uranus

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  5 - Automation Enthusiast

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. 