Have you ever worked on a project which has a plan? What is your experience like?
Let me tell you what my experience is like:
The project plan was changed dozens of times. Delivery dates, shipping dates, testing dates or any other important milestones were constantly revised, sometimes until the last week. Uncertainty seemed to be the biggest problem project managers spent their time on. My managers worked hard to avoid unpleasant surprises.
And this is where the ubiquitous gantt charts often fail.
- A gantt chart based project plan assumes that there is only one possible end date for each activity.
In real world, the end date for each project activity depends on various factors like productivity, progress of other tasks, extra work, employee mood etc.. Thus, the end dates are best expressed in a format like {best case, realistic case, worst case} instead of just one date.
So I am boldly proposing a new type of project plan, henceforth to be called as,
…wait for it…
Gantt Box Chart
Think of Gantt Box Chart as a holy matrimony between Gantt Chart with Box Plot. The Gantt Box Chart shows uncertainty in project activity end dates by clearly showing 3 end-dates instead of one in the familiar box plot format. The end dates (best case, realistic or plan and worst case) are encoded as a box while the start date is shown as a dot.
Here is how a Gantt Box Chart looks like:
Despite the ugly name, I think the chart is very useful because,
- It shows the uncertainty in plan, very clearly
- It is as easy to read since we follow the Box Plot protocol
- It lets manager focus on the most uncertain portions of project, quickly
- It is new, so the chances of it getting neglected by everyone in boardroom are less 😀
I have summarized few additional observations you can get from a Gantt Box Chart here:
How to make a Gantt Box Chart in Excel?
I will be showing a way to create the gantt box charts in excel on Monday (12th July). Meanwhile, feel free to discuss this new chart and share what you think.
What do you think about Gantt Box Chart?
Would you use Gantt Box Chart for your next project or is this a little too much? What do you think about my proposal? Please share using comments.
Related: Project Management using Excel – an extensive collection of resources, templates, tutorials and ideas for project managers using excel.
13 Responses to “Data Validation using an Unsorted column with Duplicate Entries as a Source List”
Pivot Table will involve manual intervention; hence I prefer to use the 'countif remove duplicate trick' along with 'text sorting formula trick; then using the offset with len to name the final range for validation.
if using the pivot table, set the sort to Ascending, so the list in the validation cell comes back alphabetically.
Hui: Brillant neat idea.
Vipul: I am intrigued by what you are saying. Please is it possible to show us how it can be done, because as u said Hui's method requires user intervention.
Thks to PHD and all
K
Table names dont work directly inside Data validation.
You will have to define a name and point it to the table name and then use the name inside validation
Eg MyClient : Refers to :=Table1[Client]
And then in the list validation say = MyClient
Kieranz,
Pls download the sample here http://cid-e98339d969073094.skydrive.live.com/self.aspx/.Public/data-validation-unsorted-list-example.xls
Off course there are many other ways of doing the same and integrating the formulae in multiple columns into one.
Pls refer to column FGHI in that file. Cell G4 is where my validation is.
Vipul:
Many thks, will study it latter.
Rgds
K
[...] to chandoo for the idea of getting unique list using Pivot tables. What we do is that create a pivot table [...]
@Vipul:
Thanks, that was awesome! 🙂
@Playercharlie Happy to hear that 🙂
Great contribution, Hui. Solved a problem of many years!
Thanks to you, A LOT
Hi Hui,
Greeting
hope you are doing well.
I'm interested to send you a private vba excel file which i need to show detail of pivot in new workbook instead of showing in same workbook as new sheet.
Please contact me on muhammed.ye@gmail.com
Best Regards