Count of Interval of Occurances

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3148848%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ECount%20of%20Interval%20of%20Occurances%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3148848%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%20Team%20%2C%26nbsp%3B%3CBR%20%2F%3EBelow%20is%20my%20my%20scenario.%3CBR%20%2F%3EI%20am%20looking%20for%20a%20formula%20which%20will%20tell%20me%20the%20count%20of%20the%20interval%20between%20the%201st%20and%202nd%20occurrences%20.%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F347015i7F305BC856800E83%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22Screenshot%202022-02-10%20at%205.56.36%20PM.png%22%202022-02-10%3D%22%22%20at%3D%22%22%205.56.36%3D%22%22%20pm.png%3D%22%22%20alt%3D%22Screenshot%202022-02-10%20at%205.56.36%20PM.png%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20in%20the%20above%20screen%20shot%20the%20data%20in%20months%20%2C%20for%20eg%202nd%20row%20this%20person%20had%209%20occurrences%20in%20the%20month%20of%20Jan%20which%20was%20its%20first%20occurrence%20and%20in%20the%20feb%20again%20it%20had%202%20occurrences%20which%20was%20its%202nd%20occurrence%20hence%20the%20result%20will%20be%200%26nbsp%3B%3CBR%20%2F%3E2nd%20case%20%3A%20in%20the%203rd%20row%20it%20had%201st%20occurrence%20in%20feb%20and%202nd%20in%20March%20again%20the%20output%20will%20be%200%20.%3CBR%20%2F%3E3rd%20case%20%3A%20row%204%20%2C%201st%20occurrence%20is%20in%26nbsp%3B%20Feb%20and%20the%20next%20in%20Dec%20hence%20the%20gap%20is%209%20which%20means%20the%20output%20is%209%26nbsp%3B%3CBR%20%2F%3E4th%20Case%20%3A%20row%2012%20%2C%20if%20the%20occurrence%20in%20only%201%20time%20which%20is%20in%20dec%20hence%20the%20output%20should%20be%200%20.%3CBR%20%2F%3ENote%20%3A%20I%20am%20only%20looking%20for%20the%20interval%20for%201st%20and%202nd%20occurrence%20only%20.%3CBR%20%2F%3E%3CBR%20%2F%3EThansk%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3148848%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EExcel%20on%20Mac%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional 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