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

Darcie Wilson

If cell A is > 30, but < 61, then cell B is "31 - 60"

 

 

Darcie Wilson

I finally figured it out: =IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "61-90")

Thank you for your time!

Mike Wilday

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.

Darcie Wilson

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!

Mike Wilday

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", "")

Darcie Wilson

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

 

Darcie Wilson

I finally figured it out: =IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "61-90")

Thank you for your time!

Mike Wilday

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")))))

J. Craig Williams

This last formula has at least two errors in it

=IF([Days Open]4 &nbsp; &nbsp; &nbsp;<strong><em>></em> &nbsp; &nbsp;&nbsp;</strong>30,"Less than 30", IF(AND([Days Open]4 &nbsp;&nbsp;<strong><em> ></em> &nbsp;&nbsp;</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

Mike Wilday

I need to slow down. Sheesh. Thanks for catching those errors, Craig. Good thinking.

J. Craig Williams

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

 

 

Erik Johnson, PE

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"))))