|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 3rd, 2004, 04:46 AM
|
|||
|
|||
Gantt chart
I'm re-posting please help:-
please read all in order to understand the problem Thanks for your suggestion I implemented straightaway, however I noticed that it works fine as long as (H2-G2) is positive if negative then it will not work, I did If statement to swap the contents then it works partially i.e the problem of coloring the bar has arised and also I noticed that when Leave2 partially ovelap with Leave1 not correct gantt chart is presented. Reason for overlapping that if someone going for annual leave and part of it is training. Please advice. -----Original Message----- You need to plot differences in date in a gantt chart, which is a floating bar chart. For your data, insert some columns for the chart-ready data. Here are the column assignments: A: Name B: Start1 C: End1 D: Start2 E: End2 F: StartLeave1 (your data) G: EndLeave1 H: StartLeave2 I: EndLeave2 Put these names into row 1: A1: leave blank B1, D1: anything C1: Leave 1 E1: Leave 2 F1-I1: anything Put these formulas into row 2: B2: =F2 C2: =G2-F2 D2: =H2-G2 E2: =I2-H2 Fill these down as far as you have names in column A. Now make a stacked bar chart with the data in columns A through E, with series in columns. The blank in A1 tells Excel that row 1 contains series names and column A contains category labels. You may want to format the vertical axis so the categories are in reverse order and the value axis crosses at the maximum category. Also format the first and third bar series to be invisible: double click on each, click on the Patterns tab, and choose None for Border and for Fill. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Salam wrote: I Tried to create a gantt chart for leave schedule each person allowed to have two leave period in the year , how Can I display a chart showing the person name on the vertical axis and date in the horizontal axis. Note: I do not want to deplicate person names A1:Name B1:StartLeave1 C1:EndLeave1 D1:StartLeave2 E1:EndLeave2 A2:John B2:2/3/04 C2:5/4/04 D2:2/6/04 E2:2/7/04 (d/m/y format) Thanks and Happy New year 
|
|
#3
January 5th, 2004, 05:38 AM
|
|||
|
|||
|
Gantt chart
I tried but without success , please can you break it down
into very simple way, Excel 2002 Data is as follow A1: Blank A2: Salam A3: John A4: Peter B1: LeaveStart B2: 2/2/04 B3: 5/2/04 B4: 6/6/04 C1: LeaveEnd C2: 3/3/04 C3: 4/3/04 C4; 7/9/04 D1:TrainingStart D2: 5/5/04 D3: 2/1/04 D4; 1/7/04 E1:TrainingEnd E2: 5/6/04 E3: 10/1/04 E4: 20/7/04 -----Original Message----- I have responded to your question in the earlier thread: You could make your formulas smarter. This one gives you the difference, no matter which is larger: =IF(H2G2,H2-G2,G2-H2) Also you need to decide how you want overlapping bars to be shown in the chart, then adjust the formulas accordingly. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: I'm re-posting please help:- please read all in order to understand the problem Thanks for your suggestion I implemented straightaway, however I noticed that it works fine as long as (H2-G2) is positive if negative then it will not work, I did If statement to swap the contents then it works partially i.e the problem of coloring the bar has arised and also I noticed that when Leave2 partially ovelap with Leave1 not correct gantt chart is presented. Reason for overlapping that if someone going for annual leave and part of it is training. Please advice. -----Original Message----- You need to plot differences in date in a gantt chart, which is a floating bar chart. For your data, insert some columns for the chart-ready data. Here are the column assignments: A: Name B: Start1 C: End1 D: Start2 E: End2 F: StartLeave1 (your data) G: EndLeave1 H: StartLeave2 I: EndLeave2 Put these names into row 1: A1: leave blank B1, D1: anything C1: Leave 1 E1: Leave 2 F1-I1: anything Put these formulas into row 2: B2: =F2 C2: =G2-F2 D2: =H2-G2 E2: =I2-H2 Fill these down as far as you have names in column A. Now make a stacked bar chart with the data in columns A through E, with series in columns. The blank in A1 tells Excel that row 1 contains series names and column A contains category labels. You may want to format the vertical axis so the categories are in reverse order and the value axis crosses at the maximum category. Also format the first and third bar series to be invisible: double click on each, click on the Patterns tab, and choose None for Border and for Fill. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Salam wrote: I Tried to create a gantt chart for leave schedule each person allowed to have two leave period in the year , how Can I display a chart showing the person name on the vertical axis and date in the horizontal axis. Note: I do not want to deplicate person names A1:Name B1:StartLeave1 C1:EndLeave1 D1:StartLeave2 E1:EndLeave2 A2:John B2:2/3/04 C2:5/4/04 D2:2/6/04 E2:2/7/04 (d/m/y format) Thanks and Happy New year .
|
|
#4
January 5th, 2004, 05:39 PM
|
|||
|
|||
|
Gantt chart
Ah, now you know what I mean, make the formulas smarter. Applying the
dumb formulas, you get a range like this: Start Leave Gap Train LvStart LvEnd TrStart TrEnd Salam 2/2/04 30 63 31 2/2/04 3/3/04 5/5/04 5/6/04 John 5/2/04 28 -62 8 5/2/04 4/3/04 2/1/04 10/1/04 Peter 6/6/04 93 -68 19 6/6/04 7/9/04 1/7/04 20/7/04 The negatives are a sign that my assumptions were too simplistic. Your case has training occurring before, during, or after leave. So our formulas must be smarter, to allow for leave, training, leave+training, or a gap, and with leave before or after training. With LeaveStart, LeaveEnd, TrainingStart, and TrainingEnd dates in columns I through L, set up this range in A1:H4: Start Leave Train Gap Both Leave Train Salam John Peter (Yes, I know there are two columns each for Leave and Train.) Keep cell A1 blank, so Excel makes the chart properly. Put these formulas where indicated: B2: =MIN(I2,K2) C2: =IF(I2K2,MIN(K2,J2)-I2,0) D2: =IF(K2I2,MIN(L2,I2)-K2,0) E2: =IF(MIN(J2,L2)MAX(I2,K2),MAX(I2,K2)-MIN(J2,L2),0) F2: =IF(MIN(J2,L2)MAX(I2,K2),MIN(J2,L2)-MAX(I2,K2),0) G2: =IF(J2L2,J2-MAX(L2,I2),0) H2: =IF(L2J2,L2-MAX(J2,K2),0) Fill these down as far as you have names in column A. Make a stacked bar chart from columns A through H, with the series in columns. Format the Start and Gap series to be invisible (border and area both None), format both Leave series the same, format both Training series the same. Remove extraneous legend entries by selecting the legend, then selecting the text of the entry, and pressing Delete (don't select the legend key, the little colored square, or you'll delete the series). You now have one color each for leave and training, and a third for overlaps. I don't think this is going to be easily understood at first glance. It would be better to have two bars for each name, one for leave, and one for training. The trick to a chart like this is in staggering of the data. The technique is shown on Stephen Bullen's site (http://bmsltd.co.uk) and jazzed up a little by Bernard Liengme (http://www.stfx.ca/people/bliengme/E...ps/Columns.htm). Set up your data like this: Start Leave Train LvStart LvEnd TrStart TrEnd Blank Row Salam 2/2/04 30 2/2/04 3/3/04 5/5/04 31 5/5/04 5/6/04 Blank Row John 5/2/04 28 5/2/04 4/3/04 2/1/04 8 2/1/04 10/1/04 Blank Row Peter 6/6/04 93 6/6/04 7/9/04 1/7/04 19 1/7/04 20/7/04 Blank Row There are two rows of data for each name, plus blank rows which space the data in the chart. You need cell A1 to be blank, and some dummy thing in cell A2, at least until after the chart is made. The cells under Leave and Train have the appropriate durations. Make a stacked bar chart from columns A to D, with series by column. Format the Start series to be invisible. Now, any overlap is obvious, as a span within which you have both bars (more obvious than translating colors into one or both activities). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: I tried but without success , please can you break it down into very simple way, Excel 2002 Data is as follow A1: Blank A2: Salam A3: John A4: Peter B1: LeaveStart B2: 2/2/04 B3: 5/2/04 B4: 6/6/04 C1: LeaveEnd C2: 3/3/04 C3: 4/3/04 C4; 7/9/04 D1:TrainingStart D2: 5/5/04 D3: 2/1/04 D4; 1/7/04 E1:TrainingEnd E2: 5/6/04 E3: 10/1/04 E4: 20/7/04 -----Original Message----- I have responded to your question in the earlier thread: You could make your formulas smarter. This one gives you the difference, no matter which is larger: =IF(H2G2,H2-G2,G2-H2) Also you need to decide how you want overlapping bars to be shown in the chart, then adjust the formulas accordingly. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: I'm re-posting please help:- please read all in order to understand the problem Thanks for your suggestion I implemented straightaway, however I noticed that it works fine as long as (H2-G2) is positive if negative then it will not work, I did If statement to swap the contents then it works partially i.e the problem of coloring the bar has arised and also I noticed that when Leave2 partially ovelap with Leave1 not correct gantt chart is presented. Reason for overlapping that if someone going for annual leave and part of it is training. Please advice. -----Original Message----- You need to plot differences in date in a gantt chart, which is a floating bar chart. For your data, insert some columns for the chart-ready data. Here are the column assignments: A: Name B: Start1 C: End1 D: Start2 E: End2 F: StartLeave1 (your data) G: EndLeave1 H: StartLeave2 I: EndLeave2 Put these names into row 1: A1: leave blank B1, D1: anything C1: Leave 1 E1: Leave 2 F1-I1: anything Put these formulas into row 2: B2: =F2 C2: =G2-F2 D2: =H2-G2 E2: =I2-H2 Fill these down as far as you have names in column A. Now make a stacked bar chart with the data in columns A through E, with series in columns. The blank in A1 tells Excel that row 1 contains series names and column A contains category labels. You may want to format the vertical axis so the categories are in reverse order and the value axis crosses at the maximum category. Also format the first and third bar series to be invisible: double click on each, click on the Patterns tab, and choose None for Border and for Fill. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Salam wrote: I Tried to create a gantt chart for leave schedule each person allowed to have two leave period in the year , how Can I display a chart showing the person name on the vertical axis and date in the horizontal axis. Note: I do not want to deplicate person names A1:Name B1:StartLeave1 C1:EndLeave1 D1:StartLeave2 E1:EndLeave2 A2:John B2:2/3/04 C2:5/4/04 D2:2/6/04 E2:2/7/04 (d/m/y format) Thanks and Happy New year .
|
|
#5
January 6th, 2004, 04:18 AM
|
|||
|
|||
|
Gantt chart
Thanks a lot it helped me also in understanding some
tricks about bar charting, last qestion is there this way to place the corresponding LvStart at the left side of the bar and LvEnd at the right side. Thanks again -----Original Message----- Ah, now you know what I mean, make the formulas smarter. Applying the dumb formulas, you get a range like this: Start Leave Gap Train LvStart LvEnd TrStart TrEnd Salam 2/2/04 30 63 31 2/2/04 3/3/04 5/5/04 5/6/04 John 5/2/04 28 -62 8 5/2/04 4/3/04 2/1/04 10/1/04 Peter 6/6/04 93 -68 19 6/6/04 7/9/04 1/7/04 20/7/04 The negatives are a sign that my assumptions were too simplistic. Your case has training occurring before, during, or after leave. So our formulas must be smarter, to allow for leave, training, leave+training, or a gap, and with leave before or after training. With LeaveStart, LeaveEnd, TrainingStart, and TrainingEnd dates in columns I through L, set up this range in A1:H4: Start Leave Train Gap Both Leave Train Salam John Peter (Yes, I know there are two columns each for Leave and Train.) Keep cell A1 blank, so Excel makes the chart properly. Put these formulas where indicated: B2: =MIN(I2,K2) C2: =IF(I2K2,MIN(K2,J2)-I2,0) D2: =IF(K2I2,MIN(L2,I2)-K2,0) E2: =IF(MIN(J2,L2)MAX(I2,K2),MAX(I2,K2)-MIN(J2,L2),0) F2: =IF(MIN(J2,L2)MAX(I2,K2),MIN(J2,L2)-MAX(I2,K2),0) G2: =IF(J2L2,J2-MAX(L2,I2),0) H2: =IF(L2J2,L2-MAX(J2,K2),0) Fill these down as far as you have names in column A. Make a stacked bar chart from columns A through H, with the series in columns. Format the Start and Gap series to be invisible (border and area both None), format both Leave series the same, format both Training series the same. Remove extraneous legend entries by selecting the legend, then selecting the text of the entry, and pressing Delete (don't select the legend key, the little colored square, or you'll delete the series). You now have one color each for leave and training, and a third for overlaps. I don't think this is going to be easily understood at first glance. It would be better to have two bars for each name, one for leave, and one for training. The trick to a chart like this is in staggering of the data. The technique is shown on Stephen Bullen's site (http://bmsltd.co.uk) and jazzed up a little by Bernard Liengme (http://www.stfx.ca/people/bliengme/E...ps/Columns.htm). Set up your data like this: Start Leave Train LvStart LvEnd TrStart TrEnd Blank Row Salam 2/2/04 30 2/2/04 3/3/04 5/5/04 31 5/5/04 5/6/04 Blank Row John 5/2/04 28 5/2/04 4/3/04 2/1/04 8 2/1/04 10/1/04 Blank Row Peter 6/6/04 93 6/6/04 7/9/04 1/7/04 19 1/7/04 20/7/04 Blank Row There are two rows of data for each name, plus blank rows which space the data in the chart. You need cell A1 to be blank, and some dummy thing in cell A2, at least until after the chart is made. The cells under Leave and Train have the appropriate durations. Make a stacked bar chart from columns A to D, with series by column. Format the Start series to be invisible. Now, any overlap is obvious, as a span within which you have both bars (more obvious than translating colors into one or both activities). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: I tried but without success , please can you break it down into very simple way, Excel 2002 Data is as follow A1: Blank A2: Salam A3: John A4: Peter B1: LeaveStart B2: 2/2/04 B3: 5/2/04 B4: 6/6/04 C1: LeaveEnd C2: 3/3/04 C3: 4/3/04 C4; 7/9/04 D1:TrainingStart D2: 5/5/04 D3: 2/1/04 D4; 1/7/04 E1:TrainingEnd E2: 5/6/04 E3: 10/1/04 E4: 20/7/04 -----Original Message----- I have responded to your question in the earlier thread: You could make your formulas smarter. This one gives you the difference, no matter which is larger: =IF(H2G2,H2-G2,G2-H2) Also you need to decide how you want overlapping bars to be shown in the chart, then adjust the formulas accordingly. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ wrote: I'm re-posting please help:- please read all in order to understand the problem Thanks for your suggestion I implemented straightaway, however I noticed that it works fine as long as (H2- G2) is positive if negative then it will not work, I did If statement to swap the contents then it works partially i.e the problem of coloring the bar has arised and also I noticed that when Leave2 partially ovelap with Leave1 not correct gantt chart is presented. Reason for overlapping that if someone going for annual leave and part of it is training. Please advice. -----Original Message----- You need to plot differences in date in a gantt chart, which is a floating bar chart. For your data, insert some columns for the chart- ready data. Here are the column assignments: A: Name B: Start1 C: End1 D: Start2 E: End2 F: StartLeave1 (your data) G: EndLeave1 H: StartLeave2 I: EndLeave2 Put these names into row 1: A1: leave blank B1, D1: anything C1: Leave 1 E1: Leave 2 F1-I1: anything Put these formulas into row 2: B2: =F2 C2: =G2-F2 D2: =H2-G2 E2: =I2-H2 Fill these down as far as you have names in column A. Now make a stacked bar chart with the data in columns A through E, with series in columns. The blank in A1 tells Excel that row 1 contains series names and column A contains category labels. You may want to format the vertical axis so the categories are in reverse order and the value axis crosses at the maximum category. Also format the first and third bar series to be invisible: double click on each, click on the Patterns tab, and choose None for Border and for Fill. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Salam wrote: I Tried to create a gantt chart for leave schedule each person allowed to have two leave period in the year , how Can I display a chart showing the person name on the vertical axis and date in the horizontal axis. Note: I do not want to deplicate person names A1:Name B1:StartLeave1 C1:EndLeave1 D1:StartLeave2 E1:EndLeave2 A2:John B2:2/3/04 C2:5/4/04 D2:2/6/04 E2:2/7/04 (d/m/y format) Thanks and Happy New year . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
