How to calculate task rows by % Complete

Options
slando
slando ✭✭
edited 07/14/22 in Formulas and Functions

Hello,

I am trying to create a dashboard metric graph that shows the number of tasks by % Complete (i.e. tasks can either be Not Started =0, In Progress = between 0.01 and 0.09, and Completed = 1). I think I need to use the COUNTIF command but I'm getting a #UNPARSEABLE ERROR in the cell no matter how I formulate the string

Any suggestions?


Sean

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @slando

    I think I need more information to know exactly what you're trying to graph out.

    That's one example. Then there's this:

    =AVG([% Complete]:[% Complete])


  • slando
    slando ✭✭
    Options

    Hello Mike TV,

    TY for your reply.

    In short, I have project plan with many milestones, each milestone has a number of tasks associated to it. What I am trying to display on an Executive Dashboard is a Graph widget that shows only 3 scenarios

    1- Total number of Task rows that are "Not Started", meaning that in the % Complete cell = 0%

    2- Total Number of Task rows that are "In Progress", meaning that in the % Complete cell there would have any value between 5% - 99%

    3- Total Number of Task rows that are "Completed", meaning that the % Complete cell = 100%

    My goal is to have the Graph Widget on the Executive Dashboard pull from my Plan sheet (or a calculation sheet that I create to mirror the plan), and show all 120 or so tasks in only those three categories.

    Did this help a bit?


    TY

    Sean

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @slando

    So if you start with a sheet like this:

    You can create a secondary summary sheet like this (just 3 columns and 1 row):

    Formulas are such as for each status, just change the status name for each column:

    =COUNTIF({Status}, ="Not Started")

    Then you can build a graph on a dashboard that looks like this:


  • slando
    slando ✭✭
    Options

    OK, so 2 questions

    1- On my current project Plan, I only have a % Complete column, no column next to it that shows Not Started, In Progress or Completed, do I need to have that column, as the plan has been in play for weeks now and I'd like to avoid adding new columns if at all possible

    2- If I do need it, what is the formula to calculate the "In Progress"? That seems to be where my issue lies. I believe it should somehow show 0.01 and <0.99 = "In progress". But for some reason I can't get it to calculate that for me.

    I have the 0.99 = "In Progress"

    and the <.01 = Not Started

    It just is the In Progress formula one?

    TY


    Sean

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @slando

    Not Started count formula:

    =COUNTIF({% Complete}, =0)

    In Progress count formula:

    =COUNTIFS({% Complete}, >=0.01, {% Complete}, <=0.99)

    Completed count formula:

    =COUNTIF({% Complete}, =1)

  • slando
    slando ✭✭
    Options

    TY sir

    Seems that the In Progress formula doesn't work??


    Sean

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @slando

    It's working for me on my test sheet. The first row is with the ="In Progress" formula from my first answer. The second row is from my second post with the COUNTIFS.

    What error or results are you getting?

  • slando
    slando ✭✭
    Options
  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @slando

    Invalid reference means what's in the curly brackets {} isn't set up in your cross sheet references. You'll need to manually set up the reference or the one you've set up you've got a typo in.

  • slando
    slando ✭✭
    Options

    Mike,

    TY it all makes sense to me , and I do have a "Summary - or what I call a Calculation Sheet", the part that is not clear to me is in your first post where you have the Columns

    Primary Column % Complete Status

    In the Status Cell, what is the formula that I would enter that allows it to auto change to one of the three options (Not Started, In Progress or Competed) based on the % Complete cell being updated as work is done on that task?


    If I can figure that one, the rest is clear and works for me in other metric widgets on my dashboard..


    TY


    Sean

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @slando

    Here's an image of my main sheet again:

    Here's what the {% Complete} is referencing:


  • Mike TV
    Mike TV ✭✭✭✭✭✭
    edited 07/14/22
    Options

    @slando

    Here's a formula you can use to automatically set things to "Not Started", "In Progress", and "Completed":

    =IF(ISBLANK([% Complete]@row), "", IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In Progress", IF([% Complete]@row = 1, "Completed"))))

  • slando
    slando ✭✭
    Options

    I got it to work, though I think there must be an easier format to use, that can read the % Complete Column and insert one of the three options without having to manually update each cell/row as it progresses along. But I will simply add a "Status" column and have to manually select/update one of the options for each row as progress is made.


    TY for the support


    Sean

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @slando

    This right here is what automatically creates one of the three statuses:

    =IF(ISBLANK([% Complete]@row), "", IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In Progress", IF([% Complete]@row = 1, "Completed"))))

  • slando
    slando ✭✭
    Options

    Just saw the last post you added. I think that is what I am looking for, a way to have the Status Column/cells update automatically based on the % Complete amount entered.


    Let me try this format - TY again for all your support


    Sean

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!