Combining Multiple IF(AND) Formulas.

Kendrick Hanny
Kendrick Hanny ✭✭
in Formulas and Functions

I have studied the Smartsheet Community boards but am unable to get my formula to work. My goal would be to combine the following rules into 1 formula:

  1. If the "% Complete" column is 100% then leave the "At Risk" flag unmarked.
  2. Mark the "At Risk" flag if the "Start" date was passed and the "% Complete" is less than .1 (10%)
  3. Mark the "At Risk" flag IF the "Finish" date has passed and the "% Complete" is less than 1 (100%)


Please see attached screen shot showing my columns and Formula

74.PNG

Thanks in Advance!!

· Share on FacebookShare on Twitter

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/23/21 Answer ✓

    Hi @Kendrick Hanny

    Hope you are fine, please try the following formula and convert it to column formula:

    =IF(AND(Start@row < TODAY(), [% Complete]@row < 0.1), 1, IF(AND(Finish@row < TODAY(), [% Complete]@row < 1), 1, 0))

    screenshot show the result

    1.jpg


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter
  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/24/21 Answer ✓

    @Kendrick Hanny

    You are welcome

    @Genevieve P

    Thank you for accepting my answer 🤗

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!