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
-
What would you want it to pull in place of 2012 or 2023 if either of them are blank?
-
The field with the first/last data
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!