AlexanderFun

Q: how to create gantt chart with numbers?

i'm a new user of numbers, as I use microsoft excel to create gantt chart before,it' s easy, but now, I like to use mac numbers, possibly, I'm not familiar with numbers, so i couldn't find a way to solve the date as a value and reverse the axises. Could somebody like to help me to solve this? Thanks.

Posted on Feb 10, 2013 7:22 PM

Close

Q: how to create gantt chart with numbers?

  • All replies
  • Helpful answers

first Previous Page 11 of 12 last Next
  • by Barry,

    Barry Barry Sep 7, 2015 11:33 PM in response to dankilback
    Level 7 (32,502 points)
    iWork
    Sep 7, 2015 11:33 PM in response to dankilback

    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

  • by Barry,

    Barry Barry Sep 9, 2015 1:23 AM in response to Yellowbox
    Level 7 (32,502 points)
    iWork
    Sep 9, 2015 1:23 AM in response to Yellowbox

    "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.

    Screen Shot 2015-09-08 at 11.26.27 PM.png

    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.

    Screen Shot 2015-09-09 at 1.09.38 AM.png

    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

  • by SGIII,

    SGIII SGIII Sep 9, 2015 8:31 AM in response to Barry
    Level 6 (10,730 points)
    Mac OS X
    Sep 9, 2015 8:31 AM in response to Barry

    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.

     

    Screen Shot 2015-09-09 at 11.12.22.png

     

     

    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:

     

    Screen Shot 2015-09-09 at 11.28.09.png

     

    Screen Shot 2015-09-09 at 11.26.11.png

     

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

     

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

     

    SG

  • by AudioVoyeur,

    AudioVoyeur AudioVoyeur Sep 14, 2015 1:21 PM in response to Barry
    Level 1 (0 points)
    Sep 14, 2015 1:21 PM in response to Barry

    HI there Barry

     

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

     

    Kind regards

    Pieter Brink

  • by ppd6677,

    ppd6677 ppd6677 Oct 8, 2015 2:20 PM in response to Jerrold Green1
    Level 1 (0 points)
    Oct 8, 2015 2:20 PM in response to Jerrold Green1

    Hello,

     

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

     

    Thanks

  • by webethical,

    webethical webethical Oct 11, 2015 9:23 AM in response to AlexanderFun
    Level 1 (0 points)
    Oct 11, 2015 9:23 AM in response to AlexanderFun

    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>

  • by alberto maria,

    alberto maria alberto maria Oct 15, 2015 5:32 AM in response to Jerrold Green1
    Level 1 (0 points)
    Oct 15, 2015 5:32 AM in response to Jerrold Green1

    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>

  • by Yellowbox,

    Yellowbox Yellowbox Oct 15, 2015 5:29 AM in response to alberto maria
    Level 6 (10,515 points)
    Mac OS X
    Oct 15, 2015 5:29 AM in response to alberto maria

    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.

  • by Firefly98,

    Firefly98 Firefly98 Dec 6, 2015 12:27 PM in response to Yellowbox
    Level 1 (0 points)
    Dec 6, 2015 12:27 PM in response to Yellowbox

    Hi,

     

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

     

    thanks

     

    neville

  • by Barry,

    Barry Barry Dec 6, 2015 4:23 PM in response to Firefly98
    Level 7 (32,502 points)
    iWork
    Dec 6, 2015 4:23 PM in response to Firefly98

    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 to my 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

  • by zeroink,

    zeroink zeroink Dec 7, 2015 10:46 AM in response to Barry
    Level 1 (0 points)
    Dec 7, 2015 10:46 AM in response to Barry

    Hi Barry,

    I do not see your email address in your profile bio info. Please advise. Thanks.

  • by Barry,

    Barry Barry Dec 7, 2015 11:25 AM in response to zeroink
    Level 7 (32,502 points)
    iWork
    Dec 7, 2015 11:25 AM in response to zeroink

    Hi zeroink,

     

    Looks like Apple has stopped displaying email addresses on the profile page(s).

     

    Hover the mouse on my name above any of my posts. A floating window will open, and you should be able to control-click (or right click) on the address to copy it, or just click to open a pre-addressed new message in Mail.

     

    Regards,

    Barry

  • by zeroink,

    zeroink zeroink Dec 7, 2015 11:32 AM in response to Barry
    Level 1 (0 points)
    Dec 7, 2015 11:32 AM in response to Barry

    Thank you!

  • by hyper-space,

    hyper-space hyper-space Feb 27, 2016 4:30 AM in response to Jerrold Green1
    Level 1 (0 points)
    Feb 27, 2016 4:30 AM in response to Jerrold Green1

    Hello Jerrold,

     

    I see you've created very good template. Could you please send it to me?

     

    ***********.com  - I use numbers 3.2 version btw.

     

    All the best

     

     

    <E-mail Edited by Host>

  • by jessewclark,

    jessewclark jessewclark Mar 16, 2016 10:58 AM in response to AlexanderFun
    Level 1 (20 points)
    Mar 16, 2016 10:58 AM in response to AlexanderFun

    I'm amazed and impressed that this thread has persisted so long on simple asks-and-emails of the documents. http://www.iworkcommunity.com/?c=22 seems to be far from current, but might that be a place that the docs in question could be posted, saving any individual from having to manually send it out?

first Previous Page 11 of 12 last Next