Find the biggest gap between numbers in rows

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

Try this...

Data Range

	A	B	C
1			Find
2	2,3,4,6,7,8,12,13,14,24,30,42,45,46,48,50,56,58,59,61		59
3	1,2,6,8,11,12,13,16,17,21,24,27,28,33,34,42,44,48,58,61		4
4	3,7,10,13,14,15,18,21,23,24,25,29,30,34,37,48,51,56,57,60	------	------
5	8,11,13,16,17,19,21,27,29,35,36,39,42,44,46,50,53,54,57,60
6	2,4,7,9,21,26,28,30,32,34,35,37,38,39,43,44,50,60,61,62
7	10,13,15,18,21,22,23,24,25,26,40,42,48,49,51,52,56,59,61,62
8	1,2,4,7,14,15,18,20,24,29,30,32,35,41,42,50,52,55,58,62
9	1,4,8,9,10,12,17,24,25,33,37,41,43,44,46,49,52,59,61,62
10	1,2,4,6,9,12,15,17,21,24,30,31,32,36,41,44,47,48,51,58
11	2,7,10,12,15,16,20,24,25,27,30,33,39,44,45,52,54,55,58,60
12	5,7,10,11,20,22,24,31,32,33,36,38,39,41,43,47,50,52,56,58
13	3,6,8,9,14,15,19,21,25,28,34,37,39,45,47,54,55,56,57,59
14	1,2,3,4,5,8,14,15,18,20,23,31,33,37,42,45,46,51,52,55

This array formula** entered in C3:

=MAX(FREQUENCY(IF(ISERROR(FIND(","&C2&",",","&A2:A14&",")),ROW(A2:A14)),IF(ISNUMBER(FIND(","&C2&",",","&A2:A14&",")),ROW(A2:A14))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This may be a bit slow to calculate on >12500 rows of data.

Also, you may have to replace the commas in the formula with semi-colons depending on your regional settings.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.