Menu
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"))) 
674
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).
Conclusion. Using these methods, we can use the IF function of Excel with any range of cells to check single, multiple, or nested 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)
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")))) I suggest using vlookup function to get the nearest match.
Prepare data range and name it: 'numberRange':
Select the range. Go to menu: Data → Named ranges... → define the new named range.
Use this simple formula:
=VLOOKUP(A2,numberRange,2) 
This way you can ommit errors, and easily correct the result.
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
