Simple Gnatt chart with Numbers for Mac

In my opinion Numbers for Mac is not as powerful or versatile as Microsoft Excel. However, this versatility of Excel is hardly required for my personal home use. This Gnatt chart here illustrates my point. I could use it to track any of the (fictitious) mini projects that I (do not) take up from time to time at home.

I use a nifty trick I picked up from chandoo.org a few years back, and “ported” it so to say to mac for numbers. Of course in comparison to Excel’s conditional formatting capabilities, Numbers’ conditional formatting is woefully inadequate. Nevertheless the end result is not that displeasing to the eye.

Screen Shot 2015-03-09 at 21.41.05

  • First create a Project Overview table, with an estimated start date and estimated end date.
  • Now create the second table called the Gnatt chart table, start with four columns – Task, Start Date, Duration, and End date
  • The columns End date should contain the following formula =WORKDAY(B3,C3), i.e. the working day corresponding to the “duration in days” i.e. C3 after the start date, i.e. B3.
  • Add as many columns after this as you please, put a number to each of these columns. I tend to work with calendar weeks, so I have used calendar weeks. In this example 11 and above.
  • The calendar week columns contain the following formula =IF(ISBLANK($B3),,IF(AND(WEEKNUM($B3)≤E$2,WEEKNUM(DATEVALUE($D3))≥E$2),1,0))

This formula does the following

  1. If the “Start date” column is not filled then do nothing
  2. If the calendar week value lies between the start and end date, then put a “1” to the column
  3. Else put a “0” to the column (it could even be blank actually!)

Now you should see a table which looks like this.

Screen Shot 2015-03-09 at 21.42.23

Firstly one must make the numbers “invisible”. Quiz time! How does one make a cell content invisible?

  1. Google it
  2. Set foreground and back ground colour to white
  3. Everything is always visible – it is all about perspective.
  4. Magic

Once you have done that, you can move on to the conditional formatting part of this little trick and add this rule to the “Gnatt chart area” to see the simple chart you saw in the first figure.

Screen Shot 2015-03-09 at 21.48.45

And you are welcome!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s