What's wrong with this formula?

Ken Hankoff

It returns only Complete or Not Started.

=IF([% Complete]@row = "1", "Complete", IF([% Complete]@row = "75", "End in Sight", IF([% Complete]@row = "50", "Work in Progress", IF([% Complete]@row = "25", "Just Started", "Not Started"))))

Ken Hankoff

Thanks, Paul. Close, but I'm still missing something.

What I'm trying to do is build a formula with logic that evaluates and returns as follows, and I think I've perhaps got things in the wrong order:

If % Complete is 0 display "Not Started"

If % Complete is greater than zero and up to 25% display "Just Started"

If % Complete is greater than 25% and less than 75% display "Work in Progress"

If % Complete is greater than 75% and less than 100% display "End in Sight"

If % Complete is 100% display Complete

Again - I think I'm close with the formula I built and your AND suggestion, but still not working.

Thoughts?

Here it is again: =IF([% Complete]@row = "1", "Complete", IF(AND([% Complete]@row < "1", [% Complete]@row > ".75"), "End in Sight", IF(AND([% Complete]@row < ".75", [% Complete]@row > ".25"), "Work in Process", IF(AND([% Complete]@row < ".25", [% Complete]@row > ".0"), "Just Started", "Not Started"))))

Paul Newcome

Ok. So let's start off with some "Nested IF Rules"...

Nested IF statements work from left to right and stop on the first true value. So if it makes it to the 3rd IF, then by default the first 2 must be false. That means we don't have to specify that in the 3rd since it is already assumed.

Nested IF statements can frequently be written very similar to how you would read exactly what your criteria is in that last post.

If % Complete is 0 display "Not Started"

If % Complete is greater than zero and up to 25% display "Just Started"

If % Complete is greater than 25% and less than 75% display "Work in Progress"

If % Complete is greater than 75% and less than 100% display "End in Sight"

If % Complete is 100% display Complete

Since we can say that previous arguments are assumed to be false just by the formula "skipping over them", we can rewrite your criteria like so...

If % Complete is 0 display "Not Started"

If % Complete is less than 25% display "Just Started"

If % Complete is less than 75% display "Work in Progress"

If % Complete is less than 100% display "End in Sight"

If % Complete is 100% display "Complete"

Now we can read down the list and it will read exactly like a nested IF.

=IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <= .25, "Just Started", IF([% Complete]@row <= .75, "Work In Progress", IF([% Complete]@row < 1, "End In Sight", IF([% Complete]@row = 1, "Complete")))))

Andrée Starå

Hi @Ken Hankoff

Smartsheet looks at the numbers in a column formatted for percentage as values between 0 and 1.

You'd need to use decimal values instead for it to work.

25% = 0,25 (0.25)

50% = 0,5 (0.5)

100% = 1 

Depending on your country/region, you'll need to exchange the comma to a period.

Did that work?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Ken Hankoff

Hi Andree,

That worked (after I made the changes you suggested, and figured out that .050 wouldn't work :-).

I've also got a bigger challenge for you. I'm wondering if there is a way to create a formula that takes this a step further, so that it works on the Parent rows in the hierarchy.

In other words, when the Parent row in the % Complete column automatically (because dependencies are enabled) calculates a value (e.g. 28%), can rules for the value in the Status column (where this formula sits) be made to return a similar result? For example, anything greater than 0% and less than 25.01% would be "Just Started". Anything between 25.01% and 50.01% would be "Work in Progress", etc.

Can it be done?

Ken Hankoff

In the event you think I'm just mooching off of your brilliance (Andree), I did want you to know I am attempting to solve this one on my own.

Although it is not (yet) functioning as expected, I do feel I am getting close with this formula:

=IF([% Complete]@row = "1", "Complete", IF(OR([% Complete]@row < "1", [% Complete]@row > ".75"), "End in Sight", IF(OR([% Complete]@row < ".75", [% Complete]@row > ".25"), "Work in Process", IF(OR([% Complete]@row < ".25", [% Complete]@row > ".0"), "Just Started", "Not Started"))))

Paul Newcome

Try replacing your OR functions with AND functions.

Ken Hankoff

Thanks, Paul. Close, but I'm still missing something.

What I'm trying to do is build a formula with logic that evaluates and returns as follows, and I think I've perhaps got things in the wrong order:

If % Complete is 0 display "Not Started"

If % Complete is greater than zero and up to 25% display "Just Started"

If % Complete is greater than 25% and less than 75% display "Work in Progress"

If % Complete is greater than 75% and less than 100% display "End in Sight"

If % Complete is 100% display Complete

Again - I think I'm close with the formula I built and your AND suggestion, but still not working.

Thoughts?

Here it is again: =IF([% Complete]@row = "1", "Complete", IF(AND([% Complete]@row < "1", [% Complete]@row > ".75"), "End in Sight", IF(AND([% Complete]@row < ".75", [% Complete]@row > ".25"), "Work in Process", IF(AND([% Complete]@row < ".25", [% Complete]@row > ".0"), "Just Started", "Not Started"))))

Paul Newcome

Ok. So let's start off with some "Nested IF Rules"...

Nested IF statements work from left to right and stop on the first true value. So if it makes it to the 3rd IF, then by default the first 2 must be false. That means we don't have to specify that in the 3rd since it is already assumed.

Nested IF statements can frequently be written very similar to how you would read exactly what your criteria is in that last post.

If % Complete is 0 display "Not Started"

If % Complete is greater than zero and up to 25% display "Just Started"

If % Complete is greater than 25% and less than 75% display "Work in Progress"

If % Complete is greater than 75% and less than 100% display "End in Sight"

If % Complete is 100% display Complete

Since we can say that previous arguments are assumed to be false just by the formula "skipping over them", we can rewrite your criteria like so...

If % Complete is 0 display "Not Started"

If % Complete is less than 25% display "Just Started"

If % Complete is less than 75% display "Work in Progress"

If % Complete is less than 100% display "End in Sight"

If % Complete is 100% display "Complete"

Now we can read down the list and it will read exactly like a nested IF.

=IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <= .25, "Just Started", IF([% Complete]@row <= .75, "Work In Progress", IF([% Complete]@row < 1, "End In Sight", IF([% Complete]@row = 1, "Complete")))))

Ken Hankoff

That's great, Paul. Your suggested formula adjustment worked.

I was wondering whether my use of OR and AND was somehow overkill. Clearly you've shown that it was. I need to work on my understanding of the flow of the logic, as this should not be so difficult (for me).

Thank you!!

Andrée Starå

@Ken Hankoff

Haha!

Glad you got it working!

Paul Newcome

Happy to help. 👍️