Excel conditional formatting gannt chart

[mozar]

Hello,
I don't know how to fix the conditional formatting on this excel spreadsheet.
1. Why does the period highlight (top right) not change
2. I don't understand why there are "actual" hashmarks where that section is empty. I used to have "0" before but I emptied out the actual section.
3. Why are there "plan" hashmarks where there is no data at all?

I can figure out a formula for the % later.

I am at wits end with this. I've been watching conditional formatting videos and tried to recreate it and it looks even worse. I asked a co-worker for help and she keeps putting me off.

Any suggestions? Assume I'm at a basic level of Excel understanding.
Thanks.

[matchewed]

So what kind of formatting are you trying? Usually it's a fairly basic logic statement of if X occurs format in a Y manner. X can be done as mathematical (<,>,=).

It generally helps to say which cells you're having a problem with as we won't be able to just open your sheet and know what the problem is. I know you're trying to tell us but that information is too vague for us.

[dandarc]

I'd go with "your functions are all fucked up"

Example - look at your Plan function in the name manager.

=PeriodInPlan*(C10 > 0)

PeriodInPlan is probably not what you want either - I can't imagine how having Median in there can be what you actually want from that, but you're only checking if your plan start date is >0.

Bottom line is PeriodInPlan evaluates to true, multiply by "If C10 > 0, then 1 else 0" which is also true.  So the product is True (1 means True).

I suspect that multiple rules are evaluating to true in your conditional formatting, so lots of weird shit can happen.

[dandarc]

Ok - figured out PeriodInPlan.  Median with high / low / value is kind of a clever way to implement a "is this date between the 2 others" function, but not clear to me at all.

PeriodInPlan is defined as: ='Project Plan'!AI$8=MEDIAN('Project Plan'!AI$8,'Project Plan'!$C10,'Project Plan'!$C10+'Project Plan'!$D10-1) (I'm in cell AI - randomly cursor was in there)

Corrected, I'd want to see that as: ='Project Plan'!AI$8=MEDIAN('Project Plan'!AI$8,'Project Plan'!$C10,'Project Plan'!$C10+'Project Plan'!$F10-1)


The function was referring to the "Plan Draft" Date when you actually wanted the "Plan Duration (Days)".  A date is generally a pretty high positive number - I may be wrong on the particulars I'm about to say, but the general concept applies - it is literally the number of days since 1/1/1900.  Add that to your start date, and that's going to be way, way higher than "45 days from now".  So then your "between these dates?" function says "yep - the date you're looking at is between the plan start and the plan start + 1 million days".

You've got all kinds of miss-aimed references like this when I dig in here.

[dandarc]

Another example - your "Actual" function defined in Name Manager is checking the Plan Finish (Column E), when it should be checking the "Actual Start" (Column G)

[mozar]

Ooooh. This is a great start! Where are you guys editing? Basic question: are you editing the functions under conditional formatting or somewhere else? When I open conditional formatting editing it says something like Actual=N3 or whatever cells. Maybe I'm trying to edit in the wrong place?

You've got all kinds of miss-aimed references like this when I dig in here.

I feel better now.

[MDM]

See http://dilbert.com/search_results?terms=project+plan

Also see http://www.pcmag.com/article2/0,2817,2380448,00.asp, and of course https://en.wikipedia.org/wiki/Microsoft_Project and https://en.wikipedia.org/wiki/Primavera_%28software%29.

In other words, are you looking to make pretty pictures for the boss or do you need real project planning/management software?

[dandarc]

2nd MDMs "use the right tool for the job" advice.

But in Excel 2013 - at the top click "Formulas", then "Name Manager" to see what all these placeholders are substituting.  Basically, there are a bunch of user-defined functions in there that have mis-aimed references.

[ShoulderThingThatGoesUp]

Sharepoint can do one of these things too. Excel really isn't a good solution here.

[mozar]

Thanks dandarc. I agree that something else would be better. We have Microsoft project,  but I think my boss wants something...else. I  don't know what this is about really.