Count of Interval of Occurances

Copper Contributor

Hi Team , 
Below is my my scenario.
I am looking for a formula which will tell me the count of the interval between the 1st and 2nd occurrences .Screenshot 2022-02-10 at 5.56.36 PM.png

 

As you can in the above screen shot the data in months , for eg 2nd row this person had 9 occurrences in the month of Jan which was its first occurrence and in the feb again it had 2 occurrences which was its 2nd occurrence hence the result will be 0 
2nd case : in the 3rd row it had 1st occurrence in feb and 2nd in March again the output will be 0 .
3rd case : row 4 , 1st occurrence is in  Feb and the next in Dec hence the gap is 9 which means the output is 9 
4th Case : row 12 , if the occurrence in only 1 time which is in dec hence the output should be 0 .
Note : I am only looking for the interval for 1st and 2nd occurrence only .

Thansk

2 Replies

@vshivku1 

=IFERROR(SMALL(IF(B2:M2<>"",COLUMN(A:L)),2)-SMALL(IF(B2:M2<>"",COLUMN(A:L)),1)-1,0)

 

Maybe with this formula which works in my spreadsheet. Enter the formula as arrayformula with ctrl+shift+enter if you don't work with Office365 or 2021.

Thank you so much !!
It works