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