I need help in Google Sheets to check for the continuity of an event which is a cell value say "3". The continuity needs to be checked for the last 7 cells. If the condition is satisfied (doesn't matter how many times) the value in the Result column is 1.
Please help in solving the problem.
Refer to the attached image for illustration.
CodePudding user response：
Try in P2
=--ARRAYFORMULA(MAX((IFERROR({SPLIT(TEXTJOIN("|",TRUE,IF(A2:O2="",COLUMN(A2:N2),"")),"|"),16}-IFERROR({0,SPLIT(TEXTJOIN("|",TRUE,IF(A2:O2="",COLUMN(A2:N2),"")),"|")})-1)))>=7)
drag to bottom.
I've compared single cells with next, if different retrieve column number. Next create two array, shifiting the second to the right. The difference create the intervals of "3", using MAX check if exists the continuity that return TRUE or FALSE. The double minus transform that in 1 or 0 returning the value asked by OPCodePudding user response：
Continuity Checker
function continuityChecker() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Sheet0");
const vs = sh.getRange(2, 1, sh.getLastRow() -1, 15).getValues()
const o = vs.reduce((a, r, j) => {
r.forEach((c, i) => {
if (i == 0) { a.sum = 0; a.o.push([0]) }
if (c){ a.sum ;}else{a.sum = 0;}
if (a.sum == 7) a.o[a.o.length - 1] = [1];
})
return a;
}, { o: [], sum: 0, output: function () { return this.o; } }).output();
sh.getRange(2, 16, o.length, o[0].length).setValues(o);
}
Output:
| A | B | C | D | E | F | G | H | I | J | K | L | M.N | O | P | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | Result |
| 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 1 |
| 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 0 | ||
| 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 1 | ||
| 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 1 |