Forum Discussion
How to get the lowest range of numbers in a formula
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?
That is done with a defined name from Name Manager
In this instance 'ranges' is used in the formula. I stopped using direct cell references of the type B3:E3 in 2016 when I also switched to array formulae for everything. It can make it hard for others to recognise what is going on.