Forum Discussion

JTB76's avatar
JTB76
Copper Contributor
May 28, 2024
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 ...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 28, 2024

JTB76 

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. 

Resources