I've tried multiple searches and haven't found the right keyword/tricky phrase to answer my question.
I am using a form for submissions and trying to calculate how long a trend has been occurring. Right now my working formula is this:
=IF(DidIncidentOccur@row = "Yes", 0, IF(YEARDAY(Date1) > YEARDAY(Date2), DaysSince2 + (Date1 - Date2), DaysSince2))
The problem is that if I have to manually enter a row because my operator forgot to enter data, it breaks the formula and I get a whole slew of "invalid data" or "circular reference" errors. I would like to think there is a way to use the @row function to get something like this:
=IF(DidIncidentOccur@row = "Yes", 0, IF(YEARDAY(Date@row) > YEARDAY(Date@row-1), DaysSince@row-1 + (Date@row - Date@row-1), DaysSince@row-1))
Basically this would mean that anytime the formula is used it is referencing the data in the same column, but 1 row below where the information is entered. Is this even possible in smartsheet? I remember being able to do it using the OFFSET formula in excel, but it appears through searches that even after 2+ years of requests for that function it hasn't been built in.