Count the number of gaps of a number occured in a column

Good Day, Hello Everyone. I'm new and I apologize my first post will be Excel-related question. ><
I'm sorry if this got asked but I wasnt able to find what falls under my question

Is it possible to count the number of gaps of a number occured in a column

Say number 8

Let's say its 1st occurence is on cell B4
then its next occurence is on B15

now I want the cell C15 (beside the next occurence, B15), to display 10
because the cells B5-B14 is the gap of its 1st occurence and its 2nd occurence

Hi,

Perhaps just, in C15:

=ROWS($1:15)-MATCH(2,INDEX(1/(B$1:B14=B15),,))-1

Regards

@ragulduy
Thank you. But let me clear some thing.

gaps.jpg

The screenshot on the left side is correct

The value of A7 and A11 is 4
B11 gives the correct answer which is 3
because the number of gaps its first occurence to its second occurence is 3 (A8-A10)

The screenshot on the right side is I entered 7 on cell A11
B11 should give an answer of 1 because it only skipped a cell
but in this case B11 doesnt yielded a result

though I kinda noticed instead of yielding a result in B11, it yielded on B13 but still the answer is still kinda far

Hi,

Perhaps you could help us help you by posting an actual Excel spreadsheet rather than a picture?

Regards

Edit: just seen your last message. If you are happy with the results, you can mark the thread as "Solved".

Originally Posted by XOR LX

Hi,

Perhaps just, in C15:

=ROWS($1:15)-MATCH(2,INDEX(1/(B$1:B14=B15),,))-1

Regards

another question, if its ok,

can I specify on the ROWS part something like greater than 1 rows?

I was thinking if it can be used to compare with the previous rows and do the same logic

like

A- B- C- D- E- F-
09-03-11-22-40-15 N/A-N/A-N/A-N/A-N/A-N/A-
07-24-34-22-20-35 N/A-N/A-N/A-00-N/A-N/A-
31-19-27-17-20-12 N/A-N/A-N/A-N/A-00-N/A-
04-07-19-40-09-33 N/A-01-N/A-N/A-02-N/A-
11-21-12-04-20-08 03-N/A-01-00-01-N/A
23-08-28-09-24-33 N/A-00-N/A-01-03-01
01-09-32-37-33-05 N/A-01-N/A-N/A-00-N/A

like for example 11 on cell A5, cell G3 should yield the answer 3
because 11 occured on the first row and didnt appear on the 3 rows succeding it

Hi,

Sorry, but not sure I understand what your expected results are here? What are all the N/As in your sheet?

Regards

sorry if this is quite confusing
and please disregard the previous file coz there are mistakes
please see the updated xls file here

let me clear things

A row takes reference on the previous row(s) only
A1-F1's data corresponds to G1-L1's data

please refer to the excel file


1st row
cell A1 holds a data which is 9
cell G1 yields an answer of N/A or not applicable because there is no row present before its row
same thing with the other numbers

2nd row
all numbers on the row aren't present on the 1st row except for 22, which is on D2
so its corresponding cell,J2 yields 0 because there is no gap between the 1st occurence and the 2nd occurence of that number

3rd row
all numbers on the row aren't present on the 1st row and 2nd row except for 20, which is on E3
so its corresponding cell, K2 yields 0

4th row
40 on cell D4, is also present on cell E1,
so D4's corresponding cell, J4 yields 2 because gap between the 1st occurence and the 2nd occurence of that number is 2 rows.
7, on cell B4, yields 1 on its corresponding cell H4, because it found 7 on the 2nd row which can be found on cell A2,
9, on cell E4, yields 2 on its corresponding cell K4, because it found 9 on the 1st row which can be found on cell A1,

please let me know if you have questions

Ok, so assuming you just manually input "N/As" in your first row, this array formula** in G2:

=IFERROR(ROWS($1:2)-1/(1/(MAX(IF($A$1:$F1=A2,ROW($A$1:$F1)))))-1,"N/A")

Copy across and down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

You're welcome!