Skip to main content
Solved

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

  • January 29, 2023
  • 2 replies
  • 54 views
K
Forum|alt.badge.img+3
  • KevinT
  • New Participant
  • 4 replies

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"

Best answer by TheTimeSavingCo

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

TheTimeSavingCo
Forum|alt.badge.img+31
  • TheTimeSavingCo
  • Brainy
  • 6416 replies
  • Answer
  • January 30, 2023

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

Adam, Airtable Consultant - thetimesaving.company
K
Forum|alt.badge.img+3
  • KevinT
  • Author
  • New Participant
  • 4 replies
  • February 2, 2023

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. 

 

 


Terms & ConditionsAccessibility statement