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

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!

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

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

JMKIEFER

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

Genevieve P.

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 to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.