Hi I wonder if you can please assist me. I would like to find out how to create a formula that checks a range of cells and counts the number of rows since a specified value occurred last. For example:
If I was looking for the last time 6 occurred it is 3 rows down in Row A therefore the answer would be 3
If I was looking for the last time 1 occurred it is 1 row down in Row B therefore the answer is 1
If I was looking for the last time 35 occurred it is 6 rows down in Row C therefore the answer is 6
A B C D E
<colgroup><col width="87" span="5" style="width: 65pt;"></colgroup><tbody>
</tbody>
If I was looking for the last time 6 occurred it is 3 rows down in Row A therefore the answer would be 3
If I was looking for the last time 1 occurred it is 1 row down in Row B therefore the answer is 1
If I was looking for the last time 35 occurred it is 6 rows down in Row C therefore the answer is 6
A B C D E
26 | 1 | 11 | 23 | 9 |
16 | 29 | 27 | 21 | 38 |
6 | 16 | 29 | 11 | 33 |
33 | 24 | 19 | 26 | 10 |
11 | 20 | 38 | 5 | 6 |
14 | 1 | 35 | 25 | 8 |
3 | 32 | 25 | 2 | 9 |
<colgroup><col width="87" span="5" style="width: 65pt;"></colgroup><tbody>
</tbody>