Forum Discussion
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 ...
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.
- Awesome! Finally works. The only piece that is causing an #NA is the 96+ number