CODEWITHSUNDEEP
if-statementgoogle-sheetsnested
robinsop97
robinsop97

Reputation: 3

Nesting "IF" Statements in Google Sheets to categorize numbers

I am trying to categorize numbers based on various levels of qualifications standards. For instance, Qualification "A" requires a number between 44.09 and 35.60. Qualification "B" requires a number between 35.59 and 29.30. Finally, qualification "C" requires a number at or less than 29.29. If the number is above the highest "A" qualification of 44.09, then an "X" should be displayed.

B2 happens to be the cell this formula is categorizing.

I have tried nesting "IF" statements in the following manner, however the formula incorrectly classifies the number.

=IF(44.09>=B2>35.59,"A", IF(35.59>=B2>29.29,"B", IF(B2<=29.29,"C","X")))

When I input "20" into "B2", the formula will classify it as "A", even though it should be "C".

Perhaps I have the "<>=" statements incorrectly stated, but I have tried fixing them to no avail. How can I change the formula to correctly classify these numbers?

Upvotes: 0

Views: 152

Answers (4)

Erik Tyler
Erik Tyler

Reputation: 9345

Another approach:

=ArrayFormula(VLOOKUP(B2,{-999,"C"; 29.291,"B"; 35.591,"A"; 44.091,"X"},2,TRUE))

Here, B2 is simply looked up within a virtual array containing two columns: the first with lower limits at each tier and the second with return values. Since the search column is in strict ascending order, TRUE will return the last threshold passed if no exact match is found.

Note: If your values will never dip below 0, you can change the -999 to 0.

Upvotes: 1

Boris Baublys
Boris Baublys

Reputation: 1203

=IF(B2<29.29,"C",
  IF(B2<=35.59,"B",
    IF(B2<=44.09,"A","X")))

Upvotes: 1

Peter Koltai
Peter Koltai

Reputation: 9734

You cannot make range (between y and x) comparison like that. You have to use the AND keyword to test for higher than and lower than at the same time:

=IF(AND(44.09>=B2, B2>=35.6),"A", IF(AND(36>B2, B2>=29.3),"B", IF(B2<29.3,"C","X")))

You can build more complex conditions that way, for example using OR, and you are not restricted to two conditions, you can expand to more.

Upvotes: 0

player0
player0

Reputation: 1

use:

=IF((44.09>=B2)*(B2>35.59), "A", 
 IF((35.59>=B2)*(B2>29.29), "B", 
 IF(B2<=29.29,              "C", "X")))

see: https://webapps.stackexchange.com/q/123729/186471

Upvotes: 1

Related Questions