Need excel formula to find highest number up to a certain number

[mrnacar]

Need excel formula to find highest number up to a certain number. For example, I want to find the highest number up to 26 on the following set of numbers in a single row.

15, 23, 25, 36, 38 Answer = 25
21, 22, 26, 42, 43 Answer = 26
10, 12, 15, 22, 27 Answer = 22

 

[Anonymous1378]

Try the following?

Book86
	A	B	C	D	E	F
2	15	23	25	36	38	25
3	21	22	26	42	43	26
4	10	12	15	22	27	22
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=MAX(IF(A2:E2<=26,A2:E2,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

Are you still using Excel 2007 per your signature?
I suggest that you update your Account details (or click your user name at the top right of the forum) & move your version information to there as helper will be looking here to what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

In case you have updated significantly, try this

23 08 02.xlsm
	A	B	C	D	E	F
1
2	15	23	25	36	38	25
3	21	22	26	42	43	26
4	10	12	15	22	27	22
Max

Cell Formulas
Range	Formula
F2:F4	F2	=MAXIFS(A2:E2,A2:E2,"<=26")

 

[mrnacar]

Try the following?

Book86
	A	B	C	D	E	F
2	15	23	25	36	38	25
3	21	22	26	42	43	26
4	10	12	15	22	27	22
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=MAX(IF(A2:E2<=26,A2:E2,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you. It worked!

 

[mrnacar]

Are you still using Excel 2007 per your signature?
I suggest that you update your Account details (or click your user name at the top right of the forum) & move your version information to there as helper will be looking here to what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

View attachment 96415

In case you have updated significantly, try this

23 08 02.xlsm
	A	B	C	D	E	F
1
2	15	23	25	36	38	25
3	21	22	26	42	43	26
4	10	12	15	22	27	22
Max

Cell Formulas
Range	Formula
F2:F4	F2	=MAXIFS(A2:E2,A2:E2,"<=26")

Thanks Peter! Been awhile since I posted here. Just updated my profile. Thanks again!

 

[Peter_SSs]

Just updated my profile.

In that case you can either use the post #2 formula and you don't need to confirm it with Ctrl+Shift+Enter, or you can use the single-function MAXIFS formula post #3

 

[mrnacar]

In that case you can either use the post #2 formula and you don't need to confirm it with Ctrl+Shift+Enter, or you can use the single-function MAXIFS formula post #3

Great! Good to have options! Thanks again!

 

[Peter_SSs]

Cheers.