• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting giving wrong results for Gantt chart

D

DashboardNovice

Member
In the attached file, on the 'Dashboard' tab, I am creating a Gantt Chart. I am concerned with the dark blue section for now (which shows the portion of any given activity that has been completed). Disregard the orange portion which shows incomplete.

The conditional format formula is being applied to the entire chart starting with cell H5. As an example see row 18, Activity 14. I have 'Planned' selected in cell V2. The start date is week 4, meaning the dark blue bars should start under the column for week 4, but instead it is starting under week 10. The conditional format formula I determined can be seen in cell H25. This is the formula I used for the CF rule.

I'm sure the formula is right, but it's as if Excel is interpreting column C as D. For example my reference to $C18 appears to be reading as $D18 for some reason. If this is the case, this would explain why the blue bars are beginning at week 10, when they should begin at week 4, as indicated by cell C18.

What can explain this?
 

Attachments

NARAYANK991 said:
Hi ,

See your file now.

I have explained the CF rule here ; see if it helps.

http://chandoo.org/forum/threads/conditional-formatting-is-not-working.16603/#post-100620

Narayan
Click to expand...

I noticed something about your spreadsheet that you uploaded. For some reason, if you make changes to the Actuals column (E or F) the chart will adjust whether Planned or Actual is selected in V2.

Any changes made to the Planned column do not update on the chart, whether Planned or Actual is selected in V2.

Your formula looks correct as far as I can tell. Do you know why changes to the Planned column are not updating even though Planned is selected in V2?

I really appreciate all of your help on this and all other posts. You have been very helpful and I have learned a lot from you.
 
Hi ,

First , we need to understand how the CF is supposed to work.

Suppose we select Planned :

When are the cells supposed to be BLUE , and when are they supposed to be AMBER ?

As far as I can see , the completed percentage refers only to the actual execution of the project ; when we talk of the planned schedule , there is nothing like completed and remaining , unless you introduce the concept of the date on which we are looking at the entire chart , say today's date.

If this is introduced , then we will need to calculate the percentage separately for Planned , since the planned completed percentage will be a calculated figure depending on how far today's date is from the start date , and how many more days remain for completion of the activity , given by the duration.

Suppose we select Actual :

Here the problem is that the weeks are an integral number , whereas the completion is a percentage , which will result in decimal numbers ; how do you want to show the completed periods in BLUE and the remaining periods in AMBER ?

Narayan
 
NARAYANK991 said:
Hi ,

First , we need to understand how the CF is supposed to work.

Suppose we select Planned :

When are the cells supposed to be BLUE , and when are they supposed to be AMBER ?

As far as I can see , the completed percentage refers only to the actual execution of the project ; when we talk of the planned schedule , there is nothing like completed and remaining , unless you introduce the concept of the date on which we are looking at the entire chart , say today's date.

If this is introduced , then we will need to calculate the percentage separately for Planned , since the planned completed percentage will be a calculated figure depending on how far today's date is from the start date , and how many more days remain for completion of the activity , given by the duration.

Suppose we select Actual :

Here the problem is that the weeks are an integral number , whereas the completion is a percentage , which will result in decimal numbers ; how do you want to show the completed periods in BLUE and the remaining periods in AMBER ?

Narayan
Click to expand...

I'm sorry for not being clear enough. Please disregard the donut charts showing completion. That pertains to the phase so it is something different. It has nothing to do with this Gantt chart. With that in mind here is what I was trying to do:

1. If Planned is selected in V75, columns C and D will be used. If Actual is selected, then columns E and F will be used.

2. The blue bars represents the portion completed.

3. The orange bars represents what is remaining to do.

4. If the completed part has decimals, then the blue bar should carry over into the next week. For example, on row 91 (Activity 14) if planned is selected, the start date is 4, plus a completed value of 11.5. So the coloring in blue needs to begin in column 4 and it should end on 11 in column R. The remaining portion in orange would therefore be under 12 and 13. So Activity 14 is done correctly based on what I currently have with 75% completion.

But if I change to 70% completion, there will be a gap under week 11. That white space should be filled in orange.

So I suspect that the problem is with the formula that controls the orange, the remaining portion to complete.
 

Attachments

Hi ,

You have not answered my doubts on the Planned completion vs. Planned remaining.

My point is that the AMBER coloring should be to do only with Actual ; with Planned , there is nothing like Completed and Remaining , unless you introduce a date as on which you are looking at the coloring.

If all the activities , even into the future are to be colored , then there is no question of AMBER coloring when Planned is selected. When Planned is selected , there will be only BLUE coloring.

When Actual is selected , then the completed portion will be colored BLUE , while the remaining portion will be colored AMBER ; you are confirming that the BLUE portion will be based on ROUNDING UP of the completed weeks.

Narayan
 
NARAYANK991 said:
Hi ,

You have not answered my doubts on the Planned completion vs. Planned remaining.

My point is that the AMBER coloring should be to do only with Actual ; with Planned , there is nothing like Completed and Remaining , unless you introduce a date as on which you are looking at the coloring.

If all the activities , even into the future are to be colored , then there is no question of AMBER coloring when Planned is selected. When Planned is selected , there will be only BLUE coloring.

When Actual is selected , then the completed portion will be colored BLUE , while the remaining portion will be colored AMBER ; you are confirming that the BLUE portion will be based on ROUNDING UP of the completed weeks.

Narayan
Click to expand...

I worked on this some more and I found out the answer. This was nothing more than using an absolute reference on V75. That did it. Something that small. Thank you for taking your time to look into this.
 
You must log in or register to reply here.
Back
Top