CODEWITHSUNDEEP
excelexcel-formula
SGH
SGH

Reputation: 7

Find the first element smaller or equal to a given number in Excel

Numbers
1
5
10
15
23
25

Given column elements like above, how can i write a formula that gets me the biggest number which is smaller or equal to a given number (found in another cell)?

For eg: for input 11, the formula should return 10

EDIT: Correction to the expected return value.

Upvotes: 0

Views: 1254

Answers (1)

P.b
P.b

Reputation: 11468

=MAX(A2:A7*(A2:A7<=11)) Excel prior to 365 need to enter the array formula with ctrl+shift+enter

The part (A2:A7<=11) creates an array of TRUE and FALSE which converts to 1 (TRUE) or 0 (FALSE). Multiplied by the values in range A2:A7. So all false conditions result in value * 0 = 0. True conditions result in value * 1 = value. So running that though MAX returns the max value with condition.

Upvotes: 1

Related Questions