Need assistance with adjusting formula to allow for 0 duration milestones in my Auto Health formula.

Options
JMKIEFER
JMKIEFER ✭
in Formulas and Functions

Hi - I am using the following formula to display the auto health of each row in my sheet. I now need to capture key milestones, which have a "0" duration and need to adjust my formula to allow for this. Currently, as you can see my formula is dividing by the duration and if it is "0", it is displaying the default (gray). How can I add to this formula so that if a row is flagged as a Milestone (I have a checkbox column for this), it will correctly calculate?

IFERROR(IF(TODAY() > Due@row, IF([% Complete]@row < 1, "Red", "Gray"), IF([% Complete]@row = 1, "Gray", IF([% Complete]@row <= ((Duration@row - (Due@row - TODAY())) / Duration@row), "Yellow", "Green"))), "Gray")

THANK YOU in advance for any assistance. I've been at this for a while and am ready to give up!

· Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @JMKIEFER

    What do you want it to do if it's a Milestone date?

    I presume if it's Complete you still want it to be Grey. Then if it's anything less that Complete and the Milestone date is Today, then it's Yellow, and if it's in the Past it's Red, and in the future, Green. Is that correct?

    If so, we can add in two separate nested IF statements to see if the checkbox is checked, and then look at the other criteria, like so:

    IF(AND([Milestone Checkbox]@row = 1, [% Complete]@row < 1, Due@row = TODAY()), "Yellow", IF(AND([Milestone Checkbox]@row = 1, [% Complete]@row < 1, Due@row > TODAY()), "Green",

    So add it in:

    =IFERROR(IF(TODAY() > Due@row, IF([% Complete]@row < 1, "Red", "Gray"), IF([% Complete]@row = 1, "Gray", IF(AND([Milestone Checkbox]@row = 1, [% Complete]@row < 1, Due@row = TODAY()), "Yellow", IF(AND([Milestone Checkbox]@row = 1, [% Complete]@row < 1, Due@row > TODAY()), "Green", IF([% Complete]@row <= ((Duration@row - (Due@row - TODAY())) / Duration@row), "Yellow", "Green"))))), "Gray")

    Let me know if this works for you!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • JMKIEFER
    JMKIEFER ✭
    Options

    Thank you so much, Genevieve. I am receiving an "Incorrect Argument Set" error. But at least this brings me one step closer! I just need to figure out where it's off. I really appreciate your help. Formulas are not a strong skill set for me. :)

    If you have any other suggestions, that would be great. Even if it's a completely different formula for calculating the auto health of a line (some with a 0 duration).

    Have a great afternoon.

    Kind regards,

    Melissa

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @JMKIEFER

    Can you copy/paste the formula you're using? I used [Milestone Checkbox]@row as my example column with the checkbox, but you'll need to swap that out for your actual checkbox column.

    You could also use the Duration column looking for 0 instead of the checkbox.

    Make sure that you're closing off the AND statements, too, before indicating if it should be "yellow" or "green".

    Along with the formula pasted, it would be helpful to see the formula open in your sheet in a screen capture, but please block out sensitive data.

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter

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!