OFFSET here returns the delay time between the finish of the prior phase and the start of the current phase. 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.Ĭolumn D also contains the same OFFSET section, with the column offset set to 2 (column C of the input table). Because both pieces of data come from the same row of the table, a simple offset from cell A1 will do the job. Its purpose is to prevent the calculation part of the formula (XXX) from acting in rows beyond the last row needed in the table.Ĭolumns A and E are collecting data from columns A and D of Project schedule input. Notes on the formulas (SKIP this part if you don't care about how it works.)Įach of the formulas contains this IF statement: IF(ROW()-2>COUNTA(Project schedule input :: $A),"",XXX) Defining it as a header column permits the second feature above to apply to Phase names listed in this column.Įach formula is entered into the indicated cell (all in Row 2), and filled down to the last row of the table.Ī2: =IF(ROW()-2>COUNTA(Project schedule input :: $A),"",OFFSET(Project schedule input :: $A$1,ROW()-1,0))ī2: =IF(ROW()-2>COUNTA(Project schedule input :: $A),"",SUM(D2:E2))Ĭ2: =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)))ĭ2: =IF(ROW()-2>COUNTA(Project schedule input :: $A),"",SUM(C2,OFFSET(Project schedule input :: $A$1,ROW()-1,2)))Į2: =IF(ROW()-2>COUNTA(Project schedule input :: $A),"",OFFSET(Project schedule input :: $A$1,ROW()-1,3))
#DRAW A FLOW CHART FOR OSX SERIES#
The names of each series of values ("Start Week" and "Duration") will be automatically picked up by the chart.Ĭolumn A is a header column. 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.Ģ. Defining it as a header row permits two Numbers features to operate:ġ. 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. Rows 1 and 2 of the first table should be the same for every project.įor each row in the blue area, four pieces of data are needed:Ī: The name of the current phase. 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. The Calculations table should be Locked (Arrange > Lock) to avoid accidental changes in the formulas. The model contains two tables and a horizontal bar chart.ĪLL data entry is done on the upper table, Project schedule input.ĪLL calculations are done on the lower table, Calculations.
The document follows Jerry's model, but may be using different formulas. For a copy of the file, see instructions at the end of this post. Here is a copy of my take on Jerry's template, posted in the thread linked in Wayne's message above.
0 kommentar(er)
