Skip to main content

Rollup sum (value) of formula not working

  • January 15, 2019
  • 2 replies
  • 80 views
DTL
Forum|alt.badge.img+11
  • DTL
  • Known Participant
  • 18 replies

I’m trying to sum values in a rollup function an am getting an #Error value returned.

Here’s how it’s structured. In one table I have “Start” and “End.” These are date and time fields. I have a separate column in the same table that is a formula called “Hours” Basically, it calculates the time and turns it into a decimal like “1.1” hours. It is a way to log activities for a specific project. So most projects will have multiple entries per project with various time entries.

In a separate table, I’m trying to use a rollup sum(values) function to calculate all the hours for specific projects. I’ve been able to accomplish this with all expenses related to projects, but not with the time. For some projects it displays a number or 0.0 but for others it displays “#ERROR!”

    2 replies

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • January 15, 2019

    Are your calculated time values right- or left-aligned in their cells? If the latter, they are being returned as text, and not numeric, values. This can happen for a number of reasons; most commonly, you may have used the empty string value — that is, ‘""’ — in an attempt to ensure the formula returned a blank value under some conditions. Unfortunately, if a formula can return different data types, Airtable returns everything as a text representation. Likewise, explicitly testing a variable against the empty string to test for a null value can also cause the field to return as text. In either case, the recommended solution is to reformat the formula to eliminate all references to the empty string. (For instance, rather than writing IF({X}!='', [DoThis]), use the short-circuited version IF({X},[DoThis]). Similarly, IF({X}='',…) can be replaced with IF(NOT({X}),…).) Under some circumstances, it may be possible to force a conversion back to a numeric value by wrapping the formula in a VALUE() function, but doing so would be unlikely to eliminate #ERROR! responses completely.

    If that’s not the case, give us a little more information about your failing formula, and I’ll back up and run it over again.

    DTL
    Forum|alt.badge.img+11
    • DTL
    • Author
    • Known Participant
    • 18 replies
    • January 15, 2019

    Are your calculated time values right- or left-aligned in their cells? If the latter, they are being returned as text, and not numeric, values. This can happen for a number of reasons; most commonly, you may have used the empty string value — that is, ‘""’ — in an attempt to ensure the formula returned a blank value under some conditions. Unfortunately, if a formula can return different data types, Airtable returns everything as a text representation. Likewise, explicitly testing a variable against the empty string to test for a null value can also cause the field to return as text. In either case, the recommended solution is to reformat the formula to eliminate all references to the empty string. (For instance, rather than writing IF({X}!='', [DoThis]), use the short-circuited version IF({X},[DoThis]). Similarly, IF({X}='',…) can be replaced with IF(NOT({X}),…).) Under some circumstances, it may be possible to force a conversion back to a numeric value by wrapping the formula in a VALUE() function, but doing so would be unlikely to eliminate #ERROR! responses completely.

    If that’s not the case, give us a little more information about your failing formula, and I’ll back up and run it over again.


    Thanks, that helped me solve the problem.


    Terms & ConditionsAccessibility statement