How to get the lowest range of numbers in a formula

[JTB76]

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

[#2]

@JTB76 

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)
  )

[#3]

@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. 

[#4]

@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

[#5]

@JTB76 

OK.  That's what the first formula does.

[#6]

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?

[#7]

@JTB76 

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.

[#8]

Awesome! Finally works. The only piece that is causing an #NA is the 96+ number

[#9]

@JTB76 

That is good.  The #N/A (not available) is a correct result for the upper bound from 96+.  The IFERROR converts that to text "∞" so that the MIN function does not error.  Were you to replace 96+ by 96-1000 (say) the error trapping would no longer be needed.  96-100 would also be error-free but that would be returned as the smallest range.

[#10]

Gotcha. That makes sense. The only thing now, is that when I copy that code to the subsequent rows, it is reading the data from the first row. The formula is not updating on each subsequent row. Any ideas how to get round that? Cheers

[#11]

@JTB76 

Sorry, I hadn't realised that the ranges do not for a unique set.  You could go into Name Manager and remove the '$'s before the row numbers to make them relative references, Sheet1!$B3:$E3, that copy down as you are used to. 

Alternatively, you could do as I always do and include the multiple rows within a 2D range "Sheet1!$B$3:$E$6".  That means changing the formula to show that you want it to be applied row by row. 

= BYROW(ranges,
    LAMBDA(rng,
      LET(
        lower,   TEXTBEFORE(rng, "-"),
        upper,   TEXTAFTER(rng, "-"),
        size,    IFERROR(1 + upper - lower, "∞"),
        minimum, MIN(size),
        XLOOKUP(minimum, size, rng)
      )
    )
  )

That means you may need to look up the way in which: LET is used to define and evaluate local variables; LAMBDA is used to define functions with the parameters you specify; LAMBDA helper functions feed a sequence of parameters (rows in this instance) for evaluation by a LAMBDA function.  The catch is that all this is very different from traditional spreadsheet working.

I will never go back to the old ways but, equally, many others will never make the move.