Multiple IF statements between number ranges

if-statement google-sheets google-sheets-formula
Kristin picture Kristin · Aug 31, 2014 · Viewed 106.7k times · Source

I'm trying to set up a formula with multiple IF statements between number ranges but I'm seeing the error:

Formula parse error

Here is the forumula:

=IF((AND(A2>0,A2<500),"Less than 500",
 If(AND(A2>=500,A2<1000),"Between 500 and 1000"),
 If(AND(A2>=1000,A2<1500),"Between 1000 and 1500"),
 If(AND(A2>=1500,A2<2000),"Between 1500 and 2000")))

Answer

eniacAvenger picture eniacAvenger · Sep 22, 2014

It's a little tricky because of the nested IFs but here is my answer (confirmed in Google Spreadsheets):

=IF(AND(A2>=0,    A2<500),  "Less than 500", 
 IF(AND(A2>=500,  A2<1000), "Between 500 and 1000", 
 IF(AND(A2>=1000, A2<1500), "Between 1000 and 1500", 
 IF(AND(A2>=1500, A2<2000), "Between 1500 and 2000", "Undefined"))))

Related questions

Google sheets, if statement multiple conditions

Trying to make an if statement that will return 4 outcomes based on the contents of the cell If the cell is "Yes", "No", "NA", "Unknown", to return 0, 1, 2, 3 respectively. I can use If(A1="Yes", 0,1) but not sure how to handle …

Read More
TEXTJOIN only when the condition is met?

I tried using TextJoin function with IF in it, but it somehow doesn't seem to work. I think I've written the formula correctly, but it doesn't give the solution I'm attempting to get. Objective: I want the values from A-column …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More