Statistical Trending in Smartsheet
Hi All,
We previously used minitab to trend control data for laboratory equipment, and while I know Smartsheet wouldn't be able to replicate that level of statistical processing, I want to leverage automations to make this type of data tracking more meaningful in Smartsheet.
What I am trying to do is to call out data (by way of notification automation) that has been trending in the same direction for four consecutive data points.
For example, this data set would trigger an automation:
 4.55
 4.58
 4.62
 4.64
And this data set would not:
 4.55
 4.58
 4.53
 4.62
My thought was to compare the last 2 data points with one another using a symbol column (up arrow every time it goes up, down if it goes down) and then build another helper column that identifies when four up or down arrows have been seen in a row. I am not quite sure how to build the second part of that process though.
Some additional details: the data points are all assigned a unique ID number that are not always consecutive, but always go up; we have upper and lower specs that the numbers need to live inside of.
Answers

Here is what I have built so far:
 Previous ABV Value formula =IFERROR(INDEX([Alcohol % v/v]:[Alcohol % v/v], MATCH([Row ID]@row  1, [Row ID]:[Row ID], 0)), "")
 Alcohol Difference formula =IFERROR([Alcohol % v/v]@row  [Previous ABV Value]@row, "")
 ABV Trend formula =IFERROR(IF([Alcohol Difference]@row > 0, "Up", IF([Alcohol Difference]@row < 0, "Down", IF([Alcohol Difference]@row = 0, "Unchanged"))), "")
ABV Trend Alert would read "ALERT" if the last four data points are going in the same direction, pictured here. Row 78 should have an ALERT notice under ABV trend alert, just not sure what formula would be best.

Will the new values always be entered at the bottom of the sheet so that your "trend" would be four consecutive rows going down the sheet?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome in short yes, but often data points are deleted if they aren't considered "compliant", so the ROW ID won't always be consecutive.

But if one is deleted then will the surrounding rows then be considered the trend?
So if we have
1
2
3
4
5
and then we delete row 3, then the data that is currently in rows 1, 2, 4, and 5 are now the trend right?
Is your [ROW ID] the autonumber column?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome correct, the trend continues on consecutive lines. And ROW ID is the autonumber column.

Excellent. Ok. Hopefully the last question... What about row 79? Would you want that flagged as well since the previous three were the same or not flagged since you already have a row in the trend flagged?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

If you don't mind flagging row 78 and 79 since technically they are both fourth in a run of four that match.
Insert a text/number column we can call "Helper" and use this column formula:
=MATCH([Row ID]@row, [Row D]:[Row ID], 0)
And then in your Alert column you can use this column formula...
=IF(AND(COUNT(DISTINCT(COLLECT([ABV Trend]:[ABV Trend], Helper:Helper, AND(@cell >= Helper@row  3, @cell <= Helper@row)))) = 1, Helper@row >= 4), "ALERT")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 210 Industry Talk
 441 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 300 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!