Spreadsheet: 773738d1648029922-formula-to-trace-back-to-the-nth-number-with-skip-criteria-and-return-row-number-formula-trace-back
Sheet: Tab 1

Day 1 |  |  | In column B we have a series of number 2.
Day 2 | 0 |  | Each of these represents the appearance of a research reading, arranged in chronological order.
Day 3 | 0
Day 4 | 0
Day 5
Day 6 | 0
Day 7 | 0
Day 8 | 0
Day 9 | 0
Day 10 | 2
Day 11 | 0
Day 12
Day 13 | 0
Day 14 | 0
Day 15 | 0
Day 16 | 0
Day 17 | 0
Day 18 | 2
Day 19 | 0
Day 20 | 2
Day 21 | 2
Day 22 | 0
Day 23 | 0
Day 24 | 0
Day 25 | 0
Day 26 | 0
Day 27 | 2
Day 28 | 2
Day 29 | 0
Day 30 | 2
Day 31 | 0
Day 32 | 0
Day 33 | 0
Day 34 | 0
Day 35 | 0
Day 36 | 0
Day 37 | 0
Day 38 | 0
Day 39 | 2
Day 40 | 0
Day 41 | 2
Day 42 | 0
Day 43 | 0
Day 44 | 0
Day 45 | 2
Day 46 | 0
Day 47 | 0
Day 48 | 0
Day 49
Day 50
Day 51
Day 52
Day 53
Day 54
Day 55
Day 56 | 2
Day 57 |  | This is the 2nd skip of more than 2 rows (counted from the Starting Day - Row 87). The trace back reaches the target at the bottom of the area
Day 58
Day 59
Day 60
Day 61 | 0
Day 62 | 0
Day 63 | 0
Day 64 | 0
Day 65 | 2 | Here we have skips of 2 rows or less | [NAME] to return this row number 65, which is just before the beginning of the 2nd skip or more than 2 rows
Day 66 | 0
Day 67 | 0
Day 68 | 2
Day 69 | 0
Day 70 | 2
Day 71 | 0
Day 72 | 0
Day 73 | 2
Day 74 | 2
Day 75 | 0
Day 76 | 2
Day 77 | 0 | This is the 1st skip of more than 2 rows (counted from the Starting Day - Row 87)
Day 78 | 0
Day 79 | 0
Day 80 | 0
Day 81 | 0
Day 82 | 0
Day 83 | 0
Day 84 | 0
Day 85 | 0
Day 86 | 0
Day 87 | 2 |  | We start the trace here at Row 87
Day 88 | 0
Day 89 | 0
Day 90 | 0
Day 91 | 0
Day 92 | 0
Day 93 | 2
Day 94
Day 95
Day 96
Day 97
Day 98
Day 99 | 0
Latest Day
NUMBER OF ROWS LAPSED SINCE APPEARANCE
NUMBER OF ROWS LAPSED - 1 |  | B94 - B100 | Here we have formula to calculate the number of rows between each of the Number 2 and the Lastest Day (Row 100)
NUMBER OF ROWS LAPSED - 2 |  | B88 - B100
NUMBER OF ROWS LAPSED - 3 |  | B77 - B100
NUMBER OF ROWS LAPSED - 4 |  | B75 - B100
NUMBER OF ROWS LAPSED - 5 |  | B74
NUMBER OF ROWS LAPSED - 6 |  | B71 - B100
NUMBER OF ROWS LAPSED - 7 |  | B69 - B100
NUMBER OF ROWS LAPSED - 8 |  | B66 - B100
NUMBER OF ROWS LAPSED - 9 |  | B57 - B100
NUMBER OF ROWS LAPSED - 10 |  | B46 - B100
NUMBER OF ROWS LAPSED - 11 |  | B42 - B100
NUMBER OF ROWS LAPSED - 12 |  | B40 - B100
NUMBER OF ROWS LAPSED - 13 |  | B31 - B100
NUMBER OF ROWS LAPSED - 14 |  | B29 - B100
IN-BETWEEN SKIPS |  |  | Here are the number of rows (inclusive of the bottom row) skipped between each of the Number 2
SKIP 2 - 1 |  | B104 - B103 | Orange colors are skips of more than 2 rows
SKIP 3 - 2 |  | B105 - B104 | Yellow colors are skips of 2 rows or less
SKIP 4 - 3 |  | B106 - B105 | We start the trace at row 87, which belongs to the second orange group here
SKIP 5 - 4 |  | B107 - B106
SKIP 6 - 5 |  | B108 - B107
SKIP 7 - 6 |  | B109 - B109
SKIP 8 - 7 |  | B110 - B109
SKIP 9 - 8 |  | B111 - B110 | Since this is the 2nd time the orange color appears since we started the trace back, this is the 2nd skip of more than two rows. The backward trace ends here.
SKIP 10 - 9 |  | B112 - B111
SKIP 11 - 10 |  | B113 - B112
SKIP 12 - 11 |  | B114 - B113
SKIP 13 - 12 |  | B115 - B114
SKIP 14 - 13 |  | B116 - B115
FORMULA TO TRACE BACK TO FOURTH SKIP OF MORE THAN TWO ROWS
Starting row number (set by discretion) | 87 |  | Here is the row number from which to trace back (starting point).
 |  |  | This is necessary because we have thousands of rows with real data. So we have to start somewhere, dependent on needs.
Formular to return Row Number of the 2nd Skip of More Than Two Rows |  |  | Here we want a formula to trace back until we reach the bottom of the 2nd skip of more than two rows, and return the corresponding row number
No. of days/rows lapsed since the 2nd Skip of More Than Two Rows
 |  |  | Find: |  |  | 2
 |  |  | Skip of more than x: |  |  | 2
 |  |  | nth occurrence: |  |  | 2