Hi,
Being new to Excel a lot of this is flying above my head. Using the Gantt chart at http://chandoo.org/wp/project-management/ as a starting point and then building on it I have been learning a lot of basic stuff which is enjoyable. Such as making the chart time aware so that it can autocalc which week it is in from a project start date or having it auto feed a list so that a Burndown chart will update itself from the data entered into the Gannt chart. Thanks Chandoo for making Excel a little more accessible and not as daunting as it can sometimes seem to a beginner.
I have a question about the formula that either fills the cell with Planned Complete, actual complete or wether to leave it blank in the area that looks like a bar chart. I have entered a Week End column between Start and Dur and have now set the Dur to autocalc it's value with =IF(ISBLANK(I11),"",I11+1-H11). The problem I am having is that when the cell is blank in the duration field now the bar graph that indicates progress is now filled with #es.
Would I be correct in thinking that I need to put a ISBLANK into the formula which is:
=IF($U$5=INDEX(ganttTypes,1),IF(AND(O$7>=$E11,O$7<=$E11+$G11),IF($E11+$G11*$K11>O$7,$AF$5,""),""),IF(AND(O$7>=$H11,O$7<=$H11+$J11),IF($H11+$J11*$K11>O$7,$AF$5,""),""))
To be honest, out of all the formulas that I have been adding that is the only beast in there that I do not truly understand so if anyone else also has the time to break that down for me I would be grateful. From what I can gather it can be broken down into 3:
=IF($U$5=INDEX(ganttTypes,1),
IF(AND(O$7>=$E11,O$7<=$E11+$G11),IF($E11+$G11*$K11>O$7,$AF$5,""),""),
IF(AND(O$7>=$H11,O$7<=$H11+$J11),IF($H11+$J11*$K11>O$7,$AF$5,""),""))
And then again into 5?
=IF($U$5=INDEX(ganttTypes,1),
IF(AND(O$7>=$E11,O$7<=$E11+$G11),
IF($E11+$G11*$K11>O$7,$AF$5,""),""),
IF(AND(O$7>=$H11,O$7<=$H11+$J11),
IF($H11+$J11*$K11>O$7,$AF$5,""),""))
Thanks in advance
Being new to Excel a lot of this is flying above my head. Using the Gantt chart at http://chandoo.org/wp/project-management/ as a starting point and then building on it I have been learning a lot of basic stuff which is enjoyable. Such as making the chart time aware so that it can autocalc which week it is in from a project start date or having it auto feed a list so that a Burndown chart will update itself from the data entered into the Gannt chart. Thanks Chandoo for making Excel a little more accessible and not as daunting as it can sometimes seem to a beginner.
I have a question about the formula that either fills the cell with Planned Complete, actual complete or wether to leave it blank in the area that looks like a bar chart. I have entered a Week End column between Start and Dur and have now set the Dur to autocalc it's value with =IF(ISBLANK(I11),"",I11+1-H11). The problem I am having is that when the cell is blank in the duration field now the bar graph that indicates progress is now filled with #es.
Would I be correct in thinking that I need to put a ISBLANK into the formula which is:
=IF($U$5=INDEX(ganttTypes,1),IF(AND(O$7>=$E11,O$7<=$E11+$G11),IF($E11+$G11*$K11>O$7,$AF$5,""),""),IF(AND(O$7>=$H11,O$7<=$H11+$J11),IF($H11+$J11*$K11>O$7,$AF$5,""),""))
To be honest, out of all the formulas that I have been adding that is the only beast in there that I do not truly understand so if anyone else also has the time to break that down for me I would be grateful. From what I can gather it can be broken down into 3:
=IF($U$5=INDEX(ganttTypes,1),
IF(AND(O$7>=$E11,O$7<=$E11+$G11),IF($E11+$G11*$K11>O$7,$AF$5,""),""),
IF(AND(O$7>=$H11,O$7<=$H11+$J11),IF($H11+$J11*$K11>O$7,$AF$5,""),""))
And then again into 5?
=IF($U$5=INDEX(ganttTypes,1),
IF(AND(O$7>=$E11,O$7<=$E11+$G11),
IF($E11+$G11*$K11>O$7,$AF$5,""),""),
IF(AND(O$7>=$H11,O$7<=$H11+$J11),
IF($H11+$J11*$K11>O$7,$AF$5,""),""))
Thanks in advance