Statistical Trending in Smartsheet

saltyblueocean

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:

1. 4.55
2. 4.58
3. 4.62
4. 4.64

And this data set would not:

1. 4.55
2. 4.58
3. 4.53
4. 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.

saltyblueocean

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.

Paul Newcome

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?

saltyblueocean

@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.

Paul Newcome

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 auto-number column?

saltyblueocean

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

Paul Newcome

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?

Paul Newcome

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