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

Kevin7859

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.

Paul Newcome

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

Kevin7859

The field with the first/last data

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)))