# What's wrong with this formula?

Options
✭✭✭✭

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

Tags:

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭
Options

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?

• ✭✭✭✭
edited 08/26/20
Options

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

• ✭✭✭✭✭✭
Options

Try replacing your OR functions with AND functions.

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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!!

• ✭✭✭✭✭✭
Options

Haha!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!