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

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 | 2
Day 6 | 0
Day 7 | 0
Day 8 | 0
Day 9 | 0
Day 10 | 2
Day 11 | 0
Day 12 | 2
Day 13 | 0
Day 14 | 0
Day 15 | 0
Day 16 | 0
Day 17 | 0
Day 18 | 2
Day 19 | 0
Day 20 | 0
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 | 0
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 | This is the 4th skip of more than 2 rows. The trace back reaches the target at the bottom of the area
Day 47 | 0
Day 48 | 0
Day 49 | 0
Day 50 | 2 | Here we have skips of 2 rows or less | [NAME] to return this row number 50, which is just before the beginning of the 4th skip or more than 2 rows
Day 51 | 2
Day 52 | 0
Day 53 | 2
Day 54 | 2
Day 55 | 0 | This is the 3rd skip of more than 2 rows (counted from the Starting Day - Row 91)
Day 56 | 0
Day 57 | 0
Day 58 | 2 | Here we have skips of 2 rows or less
Day 59 | 2
Day 60 | 2
Day 61 | 0 | This is the 2nd skip of more than 2 rows (counted from the Starting Day - Row 91)
Day 62 | 0
Day 63 | 0
Day 64 | 0
Day 65 | 0
Day 66 | 0
Day 67 | 0
Day 68 | 0
Day 69 | 0
Day 70 | 0
Day 71 | 0
Day 72 | 2 | Here we have skips of 2 rows or less
Day 73 | 0
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 91)
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 | 0
Day 88 | 0
Day 89 | 0
Day 90 | 0
Day 91 | 2 |  | We start the trace here at Row 91
Day 92 | 0
Day 93 | 2
Day 94 | 0
Day 95 | 0
Day 96 | 0
Day 97 | 2
Day 98 | 0
Day 99 | 0
Latest Day
NUMBER OF ROWS LAPSED SINCE APPEARANCE
NUMBER OF ROWS LAPSED - 1 |  | B98 - 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 |  | B94 - B100
NUMBER OF ROWS LAPSED - 3 |  | B92 - B100
NUMBER OF ROWS LAPSED - 4 |  | B77 - B100
NUMBER OF ROWS LAPSED - 5 |  | B75 - B100
NUMBER OF ROWS LAPSED - 6 |  | B73 - B100
NUMBER OF ROWS LAPSED - 7 |  | B61 - B100
NUMBER OF ROWS LAPSED - 8 |  | B60 - B100
NUMBER OF ROWS LAPSED - 9 |  | B59 - B100
NUMBER OF ROWS LAPSED - 10 |  | B55 - B100
NUMBER OF ROWS LAPSED - 11 |  | B54 - B100
NUMBER OF ROWS LAPSED - 12 |  | B52 - B100
NUMBER OF ROWS LAPSED - 13 |  | B51 - B100
NUMBER OF ROWS LAPSED - 14 |  | B46 - 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 91, which belongs to the first 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
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 | Since there are three orange rows above, this is the fourth skip of more than two rows. The backward trace ends here.
FORMULA TO TRACE BACK TO FOURTH SKIP OF MORE THAN TWO ROWS
Starting row number (set by discretion) | 91 |  | 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 4th 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: |  |  | 4