'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.
Solution 1:[1]
=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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | P.b |