Help with a Formula

LLaCosta
LLaCosta ✭✭
edited 12/09/19 in Formulas and Functions

I have a Target % Complete column in a project plan template that essentially looks to see if you are tracking to the % complete you should be based on today's date and the start and end date of any task (or tells you that you are missing dates).

I've included the formula below that returns the result of what your target % complete should be. The thing is, when a task is indicated @ 100%, the cell with this formula goes blank. I want it to say 100% but can't tell where to update the formula to make this happen.

Any thoughts?

 

=IFERROR(IF(Status@row <> "Completed", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) > 1, "Future", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) < 0, "Late", IF([Planned Start]@row = [Today (hidden)]$1, 1 / NETDAYS([Today (hidden)]$1, [Planned End]@row), 1 - (NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row)))))), "Missing Dates")

Tags:
· Share on FacebookShare on Twitter

Comments

  • Genevieve P.
    Genevieve P. Employee Admin

    It looks like all you’re missing is the “Value if False” part of the very first IF statement. You have instructions for what to do if the Status is not Completed, but there’s no indication of what to do if the Status is completed. 

    For example (without the encompassing IFERROR statement for clarity):

     

    IF(Status@row <> “Completed”, {rest of your formula}, “100%”)

     

    Let me know if this makes sense / works!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • LLaCosta
    LLaCosta ✭✭

    Thank you - Conceptually I understand what is missing based on how you outlined it. My question is where you have {rest of your formula}....am I just adding the "100%" in quotes to have it return that value? I am not sure how to create the "rest of your formula"

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

    My apologies for not being more clear - the {rest of your formula} indicated the rest of the formula that you have already written/created. I'll copy/paste it below. 

    In reviewing this, it looks like your column might already be set up as a percent column, is that correct? If that is the case, instead of saying "100%" (which would return that value as text), you can just add: 1. The 1 will equal 100% in a percent column.

    I've bolded the only addition to your formula above (the , 1). Let me know if this works!





    =IFERROR(IF(Status@row <> "Completed", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) > 1, "Future", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) < 0, "Late", IF([Planned Start]@row = [Today (hidden)]$1, 1 / NETDAYS([Today (hidden)]$1, [Planned End]@row), 1 - (NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row))))), 1), "Missing Dates")

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