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 30, 2024

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.

JTB76's avatar
JTB76
Copper Contributor
May 30, 2024
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
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 30, 2024

    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.

     

Resources