northtree
northtree

Reputation: 9255

How to check a value in an integer range in Google Spreadsheet?

For example, the integer range is separated by comma, such as "4450-4499, 4700-4805, 9920-9959". How to check if an integer is in this range?

Upvotes: 0

Views: 2187

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18707

Paste values:

  • Cell A1 = 4450-4499, 4700-4805, 9920-9959
  • Cell B1 = 4450 or any number

And use this formula:

=IFERROR(JOIN("",QUERY(
{ArrayFormula(REGEXEXTRACT(TRANSPOSE(SPLIT(A1,", ")),"(\d+)")*1),
ArrayFormula(REGEXEXTRACT(TRANSPOSE(SPLIT(A1,", ")),"-(\d+)")*1)},
"where Col1 <= "&B1&" and Col2 >= "&B1&"",0))*1,0)>0

Will return true bacause 4450 is in range 4450-4499.

Upvotes: 1

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

This function takes a number and the range and performs the check, but beware that it has no precautions, so the data provided must fit the format you gave:

function inRange(number, range) {
  range = range.split(", ");
  range = range.map(function(x) {return x.split("-")});

  for (var i in range) {
    if (number >= range[i][0] && number <= range[i][1]) {
      return true;
    }
  }
  return false;
}

Upvotes: 0

Related Questions