Seeking correct formula for "If greater than, but less than" statement.
Best Answer

J. Craig Williams Top Contributor
This last formula has at least two errors in it
=IF([Days Open]4 <strong><em>></em> </strong>30,"Less than 30", IF(AND([Days Open]4 <strong><em> ></em> </strong>30, [Days Open]4 < 61), "3060", IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "6190", IF(AND([Days Open]4 > 90, [Days Open]4 < 120), "91120", IF([Days Open]4 > 120, "Greater than 120")))))
The first check for for greater than 30, but should have been less than.
The second check was for greater than 30, but should have been greater than or equal to
Further, here is a shorter version:
=IF([Days Open]11 > 120, "Greater than 120", IF([Days Open]11 > 90, "91120", IF([Days Open]11 > 60, "6190", IF([Days Open]11 > 30, "3160", "30 or Less"))))
I dispense with the AND() because I let order of the function dictate where the value lies. If it is 90, I know already that it is not greater than 120 and is not greater than 90.
Nesting is important, but don't get caught up in the way we describe the problem, but think about the way the computer will understand it.
Craig
5
Answers
Try this... You only need brackets around the column names if they have spaces. the 1 after the column name = the row you are are specifying.
=IF(And([Column A Name]1=>30, [Column B Name]1=<61), "3160", "")
If the item doesn't meet your criteria then the result will be blank.
Mike, Thanks for the reply. I explained problem incorrectly.
cell A15 has the number 31 in it.
I need cell B15 to determine if cell A15 has a number in it that is greater than 30, but less than 61.
If it does, then I want B15 to say "3160"
Thank you for your assistance!
While Excell and Google Sheets reference columns with Letters, formulas in Smartsheet reference the column names. Hence A15 would be [Column A Name]15 if there are spaces in the column name or ColumnName15 if there are no spaces.
See the following examples and replace Column A name is the name of Column A.
=IF(And([Column A Name]15=>30, [Column B Name]15=<61), "3160", "")
=IF(And(ColumnAName15=>30, ColumnBName15=<61), "3160", "")
I understand about the columns... it was a poor example to use.
But the formula isn't working...and I'm unclear why you placed Column B Name where you did? In my mind, that should also be Column A Name. Column B Name is where I want the text to go based on Column A....but that didn't work either. Both produced error: #INVALID OPERATION
IF(And([Column A Name]15=>30, [Column B Name]15=<61), "3160", "")
Maybe the attached screenshot will help? If not, I wont bug you anymore
I finally figured it out: =IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "6190")
Thank you for your time!
Yep  sorry I got confused by the Column A and Column B thing. Good job. Glad I helped get you onto the right track!
If you want the formula to give you the run down for each of them, nest the formulas...
=IF([Days Open]4>30,"Less than 30", IF(AND([Days Open]4 > 30, [Days Open]4 < 61), "3060", IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "6190", IF(AND([Days Open]4 > 90, [Days Open]4 < 120), "91120", IF([Days Open]4 > 120, "Greater than 120")))))
This last formula has at least two errors in it
=IF([Days Open]4 <strong><em>></em> </strong>30,"Less than 30", IF(AND([Days Open]4 <strong><em> ></em> </strong>30, [Days Open]4 < 61), "3060", IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "6190", IF(AND([Days Open]4 > 90, [Days Open]4 < 120), "91120", IF([Days Open]4 > 120, "Greater than 120")))))
The first check for for greater than 30, but should have been less than.
The second check was for greater than 30, but should have been greater than or equal to
Further, here is a shorter version:
=IF([Days Open]11 > 120, "Greater than 120", IF([Days Open]11 > 90, "91120", IF([Days Open]11 > 60, "6190", IF([Days Open]11 > 30, "3160", "30 or Less"))))
I dispense with the AND() because I let order of the function dictate where the value lies. If it is 90, I know already that it is not greater than 120 and is not greater than 90.
Nesting is important, but don't get caught up in the way we describe the problem, but think about the way the computer will understand it.
Craig
I need to slow down. Sheesh. Thanks for catching those errors, Craig. Good thinking.
A little testing goes a long way, Mike. I'm guilty of sacrificing quality for speed as well, but have been burned so often in my previous line of work, I get a physical pain when I know I am doing it.
A wrong answer is worse than no answer at all.
Craig
Trying to have a column post a statement based on another columns %
<1% Not Started
100% Complete
>1% thru <100% In Progress
I can get the not started and Complete to work but I get an error for the inbetween
Here is the formula I have used
=IF([% Complete]90 =0, "Not Started", IF([% Complete]90 = 1, "Complete", IF(AND([% Complete]90, >.01, [% Complete]90, <.99, "In Progress"))))