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

11/30/21
Answered - Pending Review

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() > [email protected], IF([% Complete]@row < 1, "Red", "Gray"), IF([% Complete]@row = 1, "Gray", IF([% Complete]@row <= (([email protected] - ([email protected] - TODAY())) / [email protected]), "Yellow", "Green"))), "Gray")

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

Answers

  • 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, [email protected] = TODAY()), "Yellow", IF(AND([Milestone Checkbox]@row = 1, [% Complete]@row < 1, [email protected] > TODAY()), "Green",

    So add it in:

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

    Let me know if this works for you!

    Cheers,

    Genevieve

  • 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

  • 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

Sign In or Register to comment.