Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Multiple IF statements between number ranges

Tags:

if-statement

google-sheets

google-sheets-formula

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")))
like image 674
Kristin Avatar asked Aug 31 '14 00:08

Kristin

People also ask

How do I do an IF statement in Excel with multiple ranges?

Step 1: Put the number you want to test in cell C6 (150). Step 2: Put the criteria in cells C8 and C9 (100 and 999). Step 3: Put the results if true or false in cells C11 and C12 (100 and 0). Step 4: Type the formula =IF(AND(C6>=C8,C6<=C9),C11,C12).

Can you do an if statement with a range?

Conclusion. Using these methods, we can use the IF function of Excel with any range of cells to check single, multiple, or nested conditions.

How do you write an if statement with multiple conditions?

When you combine each one of them with an IF statement, they read like this: AND – =IF(AND(Something is True, Something else is True), Value if True, Value if False) OR – =IF(OR(Something is True, Something else is True), Value if True, Value if False) NOT – =IF(NOT(Something is True), Value if True, Value if False)

2 Answers

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"))))
like image 187
eniacAvenger Avatar answered Sep 18 '22 10:09

eniacAvenger

I suggest using vlookup function to get the nearest match.

Step 1

Prepare data range and name it: 'numberRange':

enter image description here

Select the range. Go to menu: Data → Named ranges... → define the new named range.

Step 2

Use this simple formula:

=VLOOKUP(A2,numberRange,2)

enter image description here

This way you can ommit errors, and easily correct the result.

like image 25
Max Makhrov Avatar answered Sep 17 '22 10:09

Max Makhrov
Sign in to Comment

Related questions

Does it matter performance wise if there is an `else` after the first `return`?
Switch vs if statements
Fastest way to convert a dict's keys & values from `bytes` to `str` in python3
Creating a Python list comprehension with an if and break
Refactoring if-else if - else
&& and || in Scala
if else function in pandas dataframe [duplicate]
dplyr if_else() vs base R ifelse()
ASP.NET using Bind/Eval in .aspx in If statement
If vs. else if vs. else statements?
How to break a while loop from an if condition inside the while loop?
data.table alternative for dplyr case_when
Variable Scope issue with if statements (PHP)
Why does ifelse convert a data.frame to a list: ifelse(TRUE, data.frame(1), 0)) != data.frame(1)?
How Switch case Statement Implemented or works internally?
Check if a function returns false in Python
switch case in XSL
Why didn't the compiler warn me about an empty if-statement?
using "if" and "else" Stored Procedures MySQL
Why does initializing a string in an if statement seem different than in a switch statement? [duplicate]

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!