Finding the biggest gap

[Tigerexcel]

Looking for a function/formula to work out the largest difference between successive years. Column H illustrates what the result should be.
As a follow-up, how would the formula need to change if the original data wasn't sequenced eg Sam 1945 1968 1930 1990.

Book6
	A	B	C	D	E	F	G	H
2		Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Biggest difference
3	Sam	1930	1945	1968	1990			23
4	Jack	1950	1976	2010				34
5	Tom	1903	1947	1953	1977	1985	2020	44
Sheet4

 

[Tigerexcel]

Book7
	A	B	C	D	E	F	G	H	I	J
1		Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Biggest difference	Year Max	Year Min
2	Sam	1930	1945	1968	1990			23	1968	1945
3	Jack	1950	1976	2010				34	2010	1976
4	Tom	1903	1947	1953	1977	1985	2020	44	1947	1903
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=MAX(C2:G2-B2:F2)

Desired output in columns I and J.
Ties would be an issue and are certain to occur, if that complicates things too much, I'm ok with the assumption that there are no ties.

 

[Peter_SSs]

Ties would be an issue and are certain to occur, if that complicates things too much

To help assess if it complicates things too much I was hoping that raising the issue would ensure that you included at least one example like that with your latest sample & results.
Could you do that please?

 

[Tigerexcel]

To help assess if it complicates things too much I was hoping that raising the issue would ensure that you included at least one example like that with your latest sample & results.
Could you do that please?

Ok,

Book7
	A	B	C	D	E	F	G	H	I	J	K	L
1		Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Biggest difference	Year Max	Year Min	Year Max (tied result)	Year Min (tied result)
2	Sam	1930	1945	1968	1991			23	1968	1945	1991	1968
3	Jack	1950	1976	2010				34	2010	1976
4	Tom	1903	1947	1953	1977	1985	2020	44	1947	1903
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=MAX(C2:G2-B2:F2)

 

[Peter_SSs]

Thanks. Try these. Column I Formulas copied to columns K, M, etc and column J formulas copied to columns L, N, etc

TigerExcel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Biggest difference	Year Max	Year Min	Year Max (tied result)	Year Min (tied result)	Year Max (tied result)	Year Min (tied result)
2	Sam	1930	1945	1968	1991			23	1968	1945	1991	1968
3	Jack	1950	1976	2010				34	2010	1976
4	Tom	1903	1947	1953	1977	1985	2020	44	1947	1903
5	Jen	2000	2002	2004	2005	2006	2008	2	2002	2000	2004	2002	2008	2006
Sheet3

Cell Formulas
Range	Formula
H2:H5	H2	=MAX(C2:G2-B2:F2)
I2:I5,K2:K5,M2:M5	I2	=MID(CONCAT(IF($C2:$G2-$B2:$F2=$H2,$C2:$G2,"")),COUNTIF($I$1:I$1,"*Max*")*4-3,4)
J2:J5,L2:L5,N2:N5	J2	=MID(CONCAT(IF($C2:$G2-$B2:$F2=$H2,$B2:$F2,"")),COUNTIF($I$1:J$1,"*Min*")*4-3,4)

 

[Tigerexcel]

Thanks. Try these. Column I Formulas copied to columns K, M, etc and column J formulas copied to columns L, N, etc

TigerExcel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Biggest difference	Year Max	Year Min	Year Max (tied result)	Year Min (tied result)	Year Max (tied result)	Year Min (tied result)
2	Sam	1930	1945	1968	1991			23	1968	1945	1991	1968
3	Jack	1950	1976	2010				34	2010	1976
4	Tom	1903	1947	1953	1977	1985	2020	44	1947	1903
5	Jen	2000	2002	2004	2005	2006	2008	2	2002	2000	2004	2002	2008	2006
Sheet3

Cell Formulas
Range	Formula
H2:H5	H2	=MAX(C2:G2-B2:F2)
I2:I5,K2:K5,M2:M5	I2	=MID(CONCAT(IF($C2:$G2-$B2:$F2=$H2,$C2:$G2,"")),COUNTIF($I$1:I$1,"*Max*")*4-3,4)
J2:J5,L2:L5,N2:N5	J2	=MID(CONCAT(IF($C2:$G2-$B2:$F2=$H2,$B2:$F2,"")),COUNTIF($I$1:J$1,"*Min*")*4-3,4)

Thanks Peter, that works nicely.