Excel if function to group numbers by value-CodePudding

I am trying write an excel function that groups a long list of numbers.

Eg.

<165 "reject" ( less than 165 = reject)
>=165 <167 "F" (greater than or equal to 165 and less than 167 = group F)
>=167 <169 "D" (greater than or equal to 167 and less than 169 = group D)
>=169 <171 "B" (greater than or equal to 169 and less than 171 = group B)
>=171 <173 "A" (greater than or equal to 171 and less than 173 = group A)
>=173 <175 "C" (greater than or equal to 173 and less than 175 = group C)
>=175 <177 "E" (greater than or equal to 175 and less than 177 = group E)
>=177 "reject" ( greater than or equal to 177 = reject)

Any advice is greatly appreciated!

Thank you

I have tried the formula below but am getting the error "too many arguments in the function"

{=IF(C4<165,"reject165",IF(C4>=165,C4<167,"F",IF(C4>=167,C4<169,"D",IF(C4>=169,C4<171,"B",IF(C4>=165,C4<167,"A",IF(C4>=165,C4<167,"C",IF(C4>=165,C4<167,"E",IF(C4>=177,"reject177","null"))))))))}

CodePudding user response：

=IF(C4<165,"reject165",IF(AND(C4>=165,C4<167),"F",IF(AND(C4>=167,C4<169),"D",IF(AND(C4>=169,C4<171),"B",IF(AND(C4>=171,C4<173),"A",IF(AND(C4>=173,C4<175),"C",IF(AND(C4>=175,C4<177),"E",IF(C4>=177,"reject177","null"))))))))

CodePudding user response：

You can try with IFS function that doesn't require dealing with nested IF-condition as follow:

=IFS(OR(C4<165, C4>=177), "reject",
  AND(C4>=165, C4<167), "F", AND(C4>=167, C4<169), "D",
  AND(C4>=169, C4<171), "B", AND(C4>=171, C4<173), "A",
  AND(C4>=173, C4<175), "C", AND(C4>=175, C4<177), "E"
)

Or you can take advantage of the logic of the intervals to build a sequence and find the letter as follow:

=LET(value, C4, start, SEQUENCE(6,1,165,2), end, start 2, 
  letters, {"F"; "D";"B"; "A"; "C"; "E"},
  FILTER(letters, (value >= start) * (value < end ),"reject")
)

Because the intervals don't overlap, FILTER will return just a single letter, and if the value doesn't match any of the intervals then it returns reject.

A similar solution can be obtained via XLOOKUP replacing FILTER on the previous formula as follow:

  XLOOKUP(1, (value >= start) * (value < end), letters, "reject")