Forum Discussion
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 is the smallest range.
Hope that makes sense.
Thanks
10 Replies
The range/bin least populated by a data sample would be given by
= LET( lower, VALUE(TEXTBEFORE(range, {"-","+"})), count, DROP(FREQUENCY(sample, lower),1), XLOOKUP(MIN(count), count, range) )In each case it is the way ranges have been specified that makes for difficulty.
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
