How to calculate task rows by % Complete

slando

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

Mike TV

@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

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

@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

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

@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

TY sir

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

Sean

Mike TV

@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

#INVALID REF

Mike TV

@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

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

@slando

Here's an image of my main sheet again:

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

Mike TV

@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

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

@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

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