Use Harvey Balls to determine the % Complete of a Gantt task

Dear,

I am using Harvey Balls to track the % of completion of a task. I would like to already use this column to "paint" the progress of the activity in my Gantt Chart, but I am not able to select this column (apprently, only numeric values are allowed for this purpose).

Is there any way to achieve my goal, without duplicating columns (1 column for Harvey Balls, and a 2nd column with percentages)?

Thank you!

Find more posts tagged with

Comments

JamesR

You will have to have two columns. In the second column have a calculation working out the % based on the Harvy ball value. Hide the column so nobody sees it.

Andrea M.

Hi James, I tried that option but actually it does not work, because as soon as I assing the % column to the "% Complete column" in the "Dependency settings" of my project, it removes the formulas that were referring to the Harvey Balls and it keeps the numbers as such. So if later on I change the progress of a Harvey Ball, the associated % does not update anymore.

Unless I am missing something... i could not make this suggestion work :-(

Travis

Andrea - columns associated with dependencies cannot contain formulas, which is why the formula is stripped out of the % Complete column.

You could remove the % Complete column from dependencies (under Project Settings) then build a formula that shows % based on your Harvey Balls. The drawback of this would be that your Gantt chart will not show % Complete.

Another option is to flip the functionality with a formula that will change your Harvey Balls based on your percent complete.

Let me know if you need help with these formulas!

Dillon Brady

Hi Andrea,

As James mentioned, the only way to get the harvey ball status to display on the Gantt Chart is to use a formula.

You're right with the overriding-- The "% Complete" assignment to the column that you do in Project Settings will override any formulas that are driving the "% Complete" column.

This is because % Complete technically contains a formula when "Dependencies Enabled" are checked. This is how it rolls up to parent rows--such as this example on row 1 here.

To bring it all together, the only way to get the % Complete displaying on the gantt bars based off of your harvey balls would be to:
1. Disable Dependencies on the sheet (uncheck the box) from Project Settings.

2. Create a % Complete column, and use a formula on each row to calculate "% Complete" from the harvey ball.
3. Return to Project Settings and make sure it's looking at your new % Complete column to display on the gantt chart.

Here's a sample formula you could use to determine "% Complete" from a harvey ball: =IF(Completion2 = "Empty", 0, IF(Completion2 = "Quarter", 0.25, IF(Completion2 = "Half", 0.5, IF(Completion2 = "Three Quarter", 0.75, IF(Completion2 = "Full", 1)))))

Here's an example of a sheet with this behavior in play

JamesR

Dillo,

Nicely demonstrated, but we must remember that if you disable Dependencies we loose Fluidity (Modeling) and resource management. But if we do not need these its neat.