Seeking correct formula for "If greater than, but less than" statement.
If cell A is > 30, but < 61, then cell B is "31 - 60"
Best Answer
-
I finally figured it out: =IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "61-90")
Thank you for your time!
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), "31-60", "")
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 "31-60"
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), "31-60", "")
=IF(And(ColumnAName15=>30, ColumnBName15=<61), "31-60", "")
-
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), "31-60", "")
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), "61-90")
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), "30-60", IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "61-90", IF(AND([Days Open]4 > 90, [Days Open]4 < 120), "91-120", 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), "30-60", IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "61-90", IF(AND([Days Open]4 > 90, [Days Open]4 < 120), "91-120", 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, "91-120", IF([Days Open]11 > 60, "61-90", IF([Days Open]11 > 30, "31-60", "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 in-between
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"))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.5K Get Help
- 62 Global Discussions
- 46 Industry Talk
- 386 Announcements
- 3.5K Ideas & Feature Requests
- 54 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 466 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!
