Forum Discussion
Solved
How to get the lowest range of numbers in a formula
Hi All - I have the below ranges in different cells in Excel: 4-24, 25-72, 73-96, 96+ What I need to write a formula for, is to show in another column when searching all the above, which one ...
Do you mean the smallest range or do you required the range with the fewest contents?
The first would be given by
= LET(
lower, TEXTBEFORE(range, "-"),
upper, TEXTAFTER(range, "-"),
size, IFERROR(1 + upper - lower, "∞"),
minimum, MIN(size),
XLOOKUP(minimum, size, range)
)PeterBartholomew1 Thanks for your response. So the way I see to write this is:
= LET(lower, TEXTBEFORE(range, "4-24"), upper, TEXTAFTER(range, "96+"), size, IFERROR(1 + upper - lower, "∞"), minimum, MIN(size), XLOOKUP(minimum, size, range))
What I am trying to achieve:
If column A = 4-24, Column B = 72-96 and Column C = 25-72, then column D would bring back 4-24
- Hi Peter - I still cant get it to work. I am putting that formula into my additional column, but it just comes back with #VALUE!......Where in the formula do I quote which cells to look at?
