Find Minimum and maximum year.

[motilulla]

Hello,

I got years in the column "C" in the range C6:C45 I want a formula, which can give me in the range C2 minimum year and in the range C3 maximum year value

*	A	B	C	D
1
2		Min	04/05
3		Max	20/21
4
5			Year
6			19/20
7			18/19
8			20/21
9			19/20
10			19/20
11			17/18
12			20/21
13			17/18
14			19/20
15			20/21
16			19/20
17			20/21
18			20/21
19			20/21
20			20/21
21			20/21
22			20/21
23			19/20
24			20/21
25			18/19
26			20/21
27			20/21
28			20/21
29			04/05
30			20/21
31			20/21
32			17/18
33			20/21
34			19/20
35			18/19
36			18/19
37			20/21
38			20/21
39			19/20
40			19/20
41			20/21
42			19/20
43			20/21
44			19/20
45			18/19
46
47
48

For example, the sample image is attached.

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Habtest]

Try:

Book1
	A	B	C
2		Min	04/05
3		Max	20/21
4
5			Year
6			19/20
7			18/19
8			20/21
9			19/20
10			19/20
11			17/18
12			20/21
13			17/18
14			19/20
15			20/21
16			19/20
17			20/21
18			20/21
19			20/21
20			20/21
21			20/21
22			20/21
23			19/20
24			20/21
25			18/19
26			20/21
27			20/21
28			20/21
29			04/05
30			20/21
31			20/21
32			17/18
33			20/21
34			19/20
35			18/19
36			18/19
37			20/21
38			20/21
39			19/20
40			19/20
41			20/21
42			19/20
43			20/21
44			19/20
45			18/19
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX($C$6:$C$45,MATCH(TEXT(AGGREGATE(15,6,--LEFT(C6:C45,2),1),"00")&"*",$C$6:$C$45,0))
C3	C3	=INDEX($C$6:$C$45,MATCH(TEXT(AGGREGATE(14,6,--LEFT(C6:C45,2),1),"00")&"*",$C$6:$C$45,0))

 

[motilulla]

Try:

Book1
	A	B	C
2		Min	04/05
3		Max	20/21
4
5			Year
6			19/20
7			18/19
8			20/21
9			19/20
10			19/20
11			17/18
12			20/21
13			17/18
14			19/20
15			20/21
16			19/20
17			20/21
18			20/21
19			20/21
20			20/21
21			20/21
22			20/21
23			19/20
24			20/21
25			18/19
26			20/21
27			20/21
28			20/21
29			04/05
30			20/21
31			20/21
32			17/18
33			20/21
34			19/20
35			18/19
36			18/19
37			20/21
38			20/21
39			19/20
40			19/20
41			20/21
42			19/20
43			20/21
44			19/20
45			18/19
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX($C$6:$C$45,MATCH(TEXT(AGGREGATE(15,6,--LEFT(C6:C45,2),1),"00")&"*",$C$6:$C$45,0))
C3	C3	=INDEX($C$6:$C$45,MATCH(TEXT(AGGREGATE(14,6,--LEFT(C6:C45,2),1),"00")&"*",$C$6:$C$45,0))

Habtest, Thank you for giving a formula solution after I applied it, getting an error #NAME?

I am sorry by the way I forgot to mention range C6 below cells have "text format" may be that reason is causing error... Please could you check it?

Kind Regards
Moti

 

[Habtest]

Hmm... I think the cell format would not impact the result. Is the array entered as in "YY/YY"? Can you upload your result by XL2BB?

 

[motilulla]

Hmm... I think the cell format would not impact the result. Is the array entered as in "YY/YY"? Can you upload your result by XL2BB?

Habtest, I can't upload result by using XL2BB? Because I am using version excel 2000 which is mentioned in #Post1, other reason could be some formula function may be not sure are not compatible with my version

I here copying, and pasting again without the row and header may help. Thank you

	Min	04/05
	Max	20/21
		Year
		19/20
		18/19
		20/21
		19/20
		19/20
		17/18
		20/21
		17/18
		19/20
		20/21
		19/20
		20/21
		20/21
		20/21
		20/21
		20/21
		20/21
		19/20
		20/21
		18/19
		20/21
		20/21
		20/21
		04/05
		20/21
		20/21
		17/18
		20/21
		19/20
		18/19
		18/19
		20/21
		20/21
		19/20
		19/20
		20/21
		19/20
		20/21
		19/20
		18/19

Kind Regards

Moti

 

[Peter_SSs]

Hmm... I think the cell format would not impact the result.

But AGGREGATE is only available since Excel 2010 & OP stated in post #1

I am using Excel 2000

Try this. I think will need confirmation with Ctrl+Shift+Enter, not just enter in your version.

21 06 18.xlsm
	B	C
1
2	Min	04/05
3	Max	20/21
4
5		Year
6		19/20
7		18/19
8		20/21
9		19/20
10		19/20
11		17/18
12		20/21
13		17/18
14		19/20
15		20/21
16		19/20
17		20/21
18		20/21
19		20/21
20		20/21
21		20/21
22		20/21
23		19/20
24		20/21
25		18/19
26		20/21
27		20/21
28		20/21
29		04/05
30		20/21
31		20/21
32		17/18
33		20/21
34		19/20
35		18/19
36		18/19
37		20/21
38		20/21
39		19/20
40		19/20
41		20/21
42		19/20
43		20/21
44		19/20
45		18/19
Min Max Year

Cell Formulas
Range	Formula
C2	C2	=INDEX(C6:C45,MATCH(TEXT(MIN(LEFT(C6:C45,2)+0),"00\*"),C6:C45,0))
C3	C3	=INDEX(C6:C45,MATCH(TEXT(MAX(LEFT(C6:C45,2)+0),"00\*"),C6:C45,0))

 

[motilulla]

But AGGREGATE is only available since Excel 2010 & OP stated in post #1



Try this. I think will need confirmation with Ctrl+Shift+Enter, not just enter in your version.

21 06 18.xlsm
	B	C
1
2	Min	04/05
3	Max	20/21
4
5		Year
6		19/20
7		18/19
8		20/21
9		19/20
10		19/20
11		17/18
12		20/21
13		17/18
14		19/20
15		20/21
16		19/20
17		20/21
18		20/21
19		20/21
20		20/21
21		20/21
22		20/21
23		19/20
24		20/21
25		18/19
26		20/21
27		20/21
28		20/21
29		04/05
30		20/21
31		20/21
32		17/18
33		20/21
34		19/20
35		18/19
36		18/19
37		20/21
38		20/21
39		19/20
40		19/20
41		20/21
42		19/20
43		20/21
44		19/20
45		18/19
Min Max Year

Cell Formulas
Range	Formula
C2	C2	=INDEX(C6:C45,MATCH(TEXT(MIN(LEFT(C6:C45,2)+0),"00\*"),C6:C45,0))
C3	C3	=INDEX(C6:C45,MATCH(TEXT(MAX(LEFT(C6:C45,2)+0),"00\*"),C6:C45,0))

Peter_SSs, I tried formula just now and got the results perfect! As request.

I am grateful to you for your kind help. Have a nice time

Regards,
Moti

 

[Peter_SSs]

You are welcome. Glad to help.

BTW, other ways ..

21 06 18.xlsm
	B	C
1
2	Min	04/05
3	Max	20/21
Min Max Year (2)

Cell Formulas
Range	Formula
C2	C2	=TEXT(MIN(LEFT(C6:C45,2)+0),"00\/")&TEXT(MIN(LEFT(C6:C45,2)+1),"00")
C3	C3	=TEXT(MAX(LEFT(C6:C45,2)+0),"00\/")&TEXT(MAX(LEFT(C6:C45,2)+1),"00")

21 06 18.xlsm
	B	C
1
2	Min	04/05
3	Max	20/21
Min Max Year (3)

Cell Formulas
Range	Formula
C2	C2	=REPLACE(TEXT(MIN(SUBSTITUTE(C6:C45,"/","")+0),"0000"),3,0,"/")
C3	C3	=REPLACE(TEXT(MAX(SUBSTITUTE(C6:C45,"/","")+0),"0000"),3,0,"/")

 

[motilulla]

You are welcome. Glad to help.

BTW, other ways ..

21 06 18.xlsm
	B	C
1
2	Min	04/05
3	Max	20/21
Min Max Year (2)

Cell Formulas
Range	Formula
C2	C2	=TEXT(MIN(LEFT(C6:C45,2)+0),"00\/")&TEXT(MIN(LEFT(C6:C45,2)+1),"00")
C3	C3	=TEXT(MAX(LEFT(C6:C45,2)+0),"00\/")&TEXT(MAX(LEFT(C6:C45,2)+1),"00")

21 06 18.xlsm
	B	C
1
2	Min	04/05
3	Max	20/21
Min Max Year (3)

Cell Formulas
Range	Formula
C2	C2	=REPLACE(TEXT(MIN(SUBSTITUTE(C6:C45,"/","")+0),"0000"),3,0,"/")
C3	C3	=REPLACE(TEXT(MAX(SUBSTITUTE(C6:C45,"/","")+0),"0000"),3,0,"/")

Peter_SSs, I tried your latest formulas "TEXT", "REPLAC" and previous "INDEX", one all worked like magic, I am surprised to see so many functions are there to write and get same results.

I appreciate your help and wish you a best weekend ?

Regards,
Moti