Trying to calculate a change over time, but ignore missing data

Formula: =IFERROR(ABS([2012]@row - [2023]@row) / MAX([2012]@row:[2023]@row), "")

but the formula returns a 100% if the first column data is missing. If the last column data is missing, the % change is skewed lower.

The data is not restricted or proprietary. it is new construction cost by zip code over time.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What would you want it to pull in place of 2012 or 2023 if either of them are blank?

  • Kevin7859
    Kevin7859 ✭✭✭✭✭

    The field with the first/last data

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    First one would be

    =VALUE(LEFT(JOIN(COLLECT([2012]@row:[2023]@row, [2012]@row:[2023]@row, @cell <> ""), "~"), FIND("~", JOIN(COLLECT([2012]@row:[2023]@row, [2012]@row:[2023]@row, @cell <> ""), "~")) - 1))


    Last one would be

    =VALUE(MID("~" + JOIN(COLLECT([2012]@row:[2023]@row, [2012]@row:[2023]@row, @cell <> ""), "~") + "~", FIND("!", SUBSTITUTE("~" + JOIN(COLLECT([2012]@row:[2023]@row, [2012]@row:[2023]@row, @cell <> ""), "~") + "~", "~", "!", COUNTIFS([2012]@row:[2023]@row, @cell <> ""))) + 1, FIND("!", SUBSTITUTE("~" + JOIN(COLLECT([2012]@row:[2023]@row, [2012]@row:[2023]@row, @cell <> ""), "~") + "~", "~", "!", COUNTIFS([2012]@row:[2023]@row, @cell <> "") + 1)) - (FIND("!", SUBSTITUTE("~" + JOIN(COLLECT([2012]@row:[2023]@row, [2012]@row:[2023]@row, @cell <> ""), "~") + "~", "~", "!", COUNTIFS([2012]@row:[2023]@row, @cell <> ""))) + 1)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!