RAG Formula & Date Formula

Options
Cheryl Collins
Cheryl Collins ✭✭✭✭✭✭
edited 08/16/21 in Formulas and Functions

Hi

I have 3 questions that I just cannot figure out!

1). I want to auto set my RAG based on a score - I've checked the other community questions and have a formula (shown below) based on previous answers but I'm getting a #UNPARESABLE error

2). I want to set a boolean based on whether a date is more than today +7 days in the past, but again, I'm getting a #UNPARESABLE error


3). I also need a formula that will tell me how many tasks are over due on a project plan based on End Date and Status (either NOT being "Completed" or "Cancelled", or BEING one of "Not Started", In Progress" or "Blocked")

TIA

Cheryl

Tags:

Best Answer

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Cheryl Collins

    Hope you are fine, if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.


    My Email for sharing : Bassam.k@mobilproject.it

    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"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Your first two formulas have misplaced parenthesis.

    In your first, take one of the two before "Red" and move it to just after "<= 4".

    In the second, move the only comma there to after TODAY(-7).


    The third formula will look something like this...

    =COUNTIFS([End Date]:[End Date], @cell < TODAY(), Status:Status, AND(@cell <> "Completed", @cell <> "Cancelled"))

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    Options

    Hi @Paul Newcome

    Thanks for coming back to me. The 2nd formula works so, thanks for this. The first and third are still being problematic:

    1). I don't have a two parenthesis before "Red" so, I'm unsure what you mean, sorry. Also, if I put the parenthesis after "<=4" that will split the RAG that I want to apply to that so, again doesn't seem quite right to me?

    2) My third formula is looking up the Plan sheet so, I have replaced the column names and @cell reference with the sheet look up reference:

    =COUNTIFS({02. iCompli Product Upgrade - Project Plan Range 2}, <TODAY(), {02. iCompli Product Upgrade - Project Plan Range 1}, AND({02. iCompli Product Upgrade - Project Plan Range 1} <> "Completed", {02. iCompli Product Upgrade - Project Plan Range 1} <> "Cancelled"))

    But, I'm getting a #invalidoperation

    Thanks

    Cheryl

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    Options

    Hi @Bassam Khalil

    Let me see what Paul comes back with, if that doesn't work, I will gladly share the sheet. The only problem is the 3rd formula is referring to a project plan sheet that I won't be able to share.

    Thanks

    Cheryl

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Ugh. My apologies. My fingers were moving too fast. Commas. Not parenthesis.


    For the final, you need to leave the "@cell" in place.

    =COUNTIFS({02. iCompli Product Upgrade - Project Plan Range 2}, <TODAY(), {02. iCompli Product Upgrade - Project Plan Range 1}, AND(@cell <> "Completed", @cell <> "Cancelled"))

  • Cheryl Collins
    Cheryl Collins ✭✭✭✭✭✭
    Options

    Hi @Paul Newcome


    Bingo! Thats worked, thanks so much.


    Cheryl

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!