how to create gantt chart with numbers?

Hi Brandon,

I don't think Jerry is following this thread.

I had a go at recreating Jerry's Gantt Chart, but I got stuck at the Calculations table (see Page 1 of this thread and scroll down to Jerry's reply).

As far as I know, Jerry did not post the formulas for that table.

Perhaps someone could take up the challenge?

Regards,

Ian.

Hi Jerry, I would be very grateful if you could send me a copy of your Gantt Chart template. Thanks a lot. Alain

Hi Alain,

I don't think that Jerry is following this discussion. This discussion has long legs, reaching back to February 2013. Perhaps a more productive approach would be to start a new discussion with a description of your aim. There are many helpful users of Numbers in this forum who can help with problem solving.

Regards,

Ian.

Can you send me the Gantt chart template?

Hi Jerrold,

Can you please send me a copy of this template - I would really appreciate it.

-Daniel

<Email Edited by Host>

Hi Dan,

If you want users to be able to find your email address, please choose to make it public in your profile, not in a message, where it can be read by any spammer's passing 'bot, then used to send you loads of spam.

I've sent a note to the hosts, alerting them to the post including your email address. With luck, they'll arrive before the 'bots do.

Regards,

Barry

PS: Jerry doesn't appear to be monitoring this thread recently.

B

"Perhaps someone could take up the challenge?"

Perhaps.

What's below follows Jerry's model, but may be using different formulas.

Done using Numbers '09 (v2.3).

The model contains two tables and a horizontal bar chart.

ALL data entry is done on the upper table, Project schedule input.

ALL calculations are done on the lower table, Calculations.

The Calculations table should be Locked (Arrange > Lock) to avoid accidental changes in the formulas. It will need to be Unlocked only to permit adding or removing rows to chart a project with more or fewer stages than the seven shown in the example.

Project schedule input:

Rows 1 and 2 of the first table should be the same for every project.

For each row in the blue area, four pieces of data are needed:

A: The name of the current phase. B: The name of the most recent phase that must be completed before the current phase begins, C: the number of weeks separating the end of the earlier phase and the beginning of the current phase, and D: the number of weeks required to complete the current phase.

Calculations:

Row 1 is a header row. Defining it as a header row permits two Numbers features to operate:

1. If each cell in a column contains the same formula, that formula will be automatically filled into any rows added to the bottom of the table.

2. The names of each series of values ("Start Week" and "Duration") will be automatically picked up by the chart.

Column A is a header column. Defining it as a header column permits the second feature above to apply to Phase names listed in this column.

Formulas:

Each formula is entered into the indicated cell (all in Row 2), and filled down to the last row of the table.

A2: =IF(ROW()-2>COUNTA(Project schedule input :: $A),"",OFFSET(Project schedule input :: $A$1,ROW()-1,0))

B2: =IF(ROW()-2>COUNTA(Project schedule input :: $A),"",SUM(D2:E2))

C2: =IF(ROW()=2,0,IF(ROW()-1>COUNTA(Project schedule input :: $A),"",OFFSET($A$1,MATCH(OFFSET(Project schedule input :: $A$1,ROW()-1,1),$A,0)-1,1)))

D2: =IF(ROW()-2>COUNTA(Project schedule input :: $A),"",SUM(C2,OFFSET(Project schedule input :: $A$1,ROW()-1,2)))

E2: =IF(ROW()-2>COUNTA(Project schedule input :: $A),"",OFFSET(Project schedule input :: $A$1,ROW()-1,3))

Notes on the formulas (SKIP this part if you don't care about how it works.)

Each of the formulas contains this IF statement: IF(ROW()-2>COUNTA(Project schedule input :: $A),"",XXX)

Its purpose is to prevent the calculation part of the formula (XXX) from acting in rows beyond the last row needed in the table.

XXX parts:

Columns A and E are collecting data from columns A and D of Project schedule input. Because both pieces of data come from the same row of the table, a simple offset from cell A1 will do the job. The only difference in the OFFSET part of these formulas is the last number (0 or 3), which determines the column from which the value is taken.

Column D also contains the same OFFSET section, with the column offset set to 2 (column C of the input table). OFFSET here returns the delay time between the finish of the prior phase and the start of the current phase. SUM adds this to the finish time of the prior phase found in column C (of Calculations) to get the Start week of the current phase.

Column C gets the finish time of the prior stage. This would be a very simple formula if each of the phases depended on the finish of the one immediately above it on the list, but that is not always the case, as can be seen by the last phase, Lawsuits, which depends not on the completion of the Prepare Invoice phase, but on the completion of Construction.

The XXX part of this formula is: OFFSET($A$1,MATCH(OFFSET(Project schedule input :: $A$1,ROW()-1,1),$A,0)-1,1)

The formula starts with the second OFFSET, which is the same as those above, with the column offset set to 1 (column B, 1 to the right of the base cell, A1). The result is the name of the phase on which the current phase depends. The name is handed off to MATCH, which looks for the matching name in column A (of Calculations), and returns its position in that list. -1 subtracts 1 from the position returned by MATCH, and that result s passed to the first OFFSET, where it is used as the row offset down from A1 (on calculations). The final 1 is the column offset (to column B) where the formula finds the finish time of the prior phase on which the current phase depends.

The Chart:

Select cells D2 to E9 of Calculations, then choose the 'stacked horizontal bar chart' from the Charts button menu. Select the chart, then click the colour button above each column to edit the fill and stroke colours for each section of the bars.

I also stretched the chart horizontally, and used the Chart Inspector to increase the number of steps on the value axis to 5 for nice round numbers, and to place the labels in the middle of each bar section.

See Jerry's post on page 2 of this thread for additional comments on this file.

Regards,

Barry

If you would like a copy of this file, please email your request. You'll find my address in my profile.

B

An alternative approach is to avoid multiple, somewhat complicated formulas and calculations and charts and all that, and simply set up a table with one formula that then allows the application of 'Conditional Highlighting' to make the table look like a chart.

The one formula (in E3, copied right and down) is:

=IF(AND(E$2≥$B3,E$2≤$C3),1,0)

All the formula does is set the cell's value to 1 if the date in row 2 is between the start/end dates in columns B and C. Otherwise it sets it to 0.

Then it is a simple matter to format the cells to make the 1 or 2 "disappear" and apply a background fill if the value is 1.

Try formats similar to these to produce the desired effect:

The rows are easily dragged into a different order as needed.

This also works in Numbers 2 using 'Conditional Formatting'.

SG

HI there Barry

Would love to have a versions and any updates to the file you might have.

Kind regards

Pieter Brink

Hello,

Great job on the template - can you please send a copy to me as well?

Thanks

Hi Jerrold, hi Barry,

afterl a loog search it seems your is the best Gantt template for Numbers (well, even maybe the only one I found 😉), I would really appreciate if you could send 'em to me too.

thanks in advance

<Email Edited By Host>

Hello Jerold, I am reading now a year or more later about your great template and about your generosity. May I also have such a template please? Let me thank you in advance! Best regards. Alberto

************.com

Ciao

<E-mail Edited by Host>

Hi Alberto,

I shall ask the hosts to delete your email address. This is a public forum. Do not let spammers see your personal details 👿.

Regards,

Ian.

Hi,

this his thread is so popular when searching for a gantt in numbers. Could I please recieve a copy?

thanks

neville

A note to all who have posted here requesting a copy of the file:

If you haven't received a copy of the file, it's likely because I haven't yet received your emailed request. I send the file as an attachment tomy reply to your email. It's addressed to the address from which your email was sent.

Please make your request directly to my email address, which you will find listed in my profile, and use the discussion's 'title,' "how to create a gantt chart with numbers" as your subject line.

Regards,

Barry