Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Burn-down chart
I am working on a project with 300 products we are monitoring, with dates
posted for each stage of completion. I was asked to create a burn-down chart x-axis being weeks/dates and y-axis being the number of products that have not achieved that stage in the project. Each stage would have its own 'line'. So in the beginning all products will not have achieved any stage, so the left side of the chart will have points at the top of the y-axis. And by the end of the project they will all have points at the bottom of the y-axis. I have an excel spreadsheet of all the products in rows, and the stages in columns, with the dates that the product achieves that stage in the cells. I have never heard of a burn-down chart. Can anyone give me hints on how to do this? |
#2
|
|||
|
|||
Gail -
You describe data arranged like this: Product Stage 1 Stage 2 Stage 3 A 4/11/2005 4/21/2005 4/25/2005 B 4/8/2005 4/11/2005 4/15/2005 C 4/1/2005 4/6/2005 4/14/2005 D 4/18/2005 4/30/2005 5/17/2005 E 4/13/2005 4/21/2005 4/26/2005 F 4/19/2005 5/6/2005 5/15/2005 G 4/10/2005 4/19/2005 5/1/2005 H 4/14/2005 4/19/2005 5/5/2005 (in this example it's in A2:D10) The date in a column indicates when the product in that row reached the column's stage. Now set up this range (A14:D22): Week Stage 1 Stage 2 Stage 3 3/31/2005 4/7/2005 4/14/2005 4/21/2005 4/28/2005 5/5/2005 5/12/2005 5/19/2005 The column headers are the stage labels, the row headers start with an arbitrary starting date earlier than the first completion date of Stage 1, and increment by weeks. Now some formulas. In B15 type this formula: =SUM(IF($A15<B$3:B$10,1,0)) and don't press Enter yet, first hold down CTRL+Shift, then press Enter. This makes an array formula, which Excel indicates by putting curly brackets around it: {=SUM(IF($A15<B$3:B$10,1,0))} This tells you how many products that week have not finished with that stage yet. We're comparing the date in column A of the table (always column A, not the dollar sign) with the dates in column B, rows 3-10 (always rows 3-10, note the dollar signs). No dollar signes on the B means when we copy the formula to other columns, we look at the column the formula is in. Copy the cell, then select the range B15:D22, and paste. This fills the range like so: Week Stage 1 Stage 2 Stage 3 3/31/2005 8 8 8 4/7/2005 7 7 8 4/14/2005 2 6 7 4/21/2005 0 2 6 4/28/2005 0 2 4 5/5/2005 0 1 2 5/12/2005 0 0 2 5/19/2005 0 0 0 Select this range, and use the chart wizard to make a line chart. Excel puts the dates along the bottom and draws three lines, one for each stage. They all start up at 8 on 3/31, because none have finished any stages. The stage series lines drop, Stage 1 fastest, and at the end they have all reached zero. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Gail wrote: I am working on a project with 300 products we are monitoring, with dates posted for each stage of completion. I was asked to create a burn-down chart x-axis being weeks/dates and y-axis being the number of products that have not achieved that stage in the project. Each stage would have its own 'line'. So in the beginning all products will not have achieved any stage, so the left side of the chart will have points at the top of the y-axis. And by the end of the project they will all have points at the bottom of the y-axis. I have an excel spreadsheet of all the products in rows, and the stages in columns, with the dates that the product achieves that stage in the cells. I have never heard of a burn-down chart. Can anyone give me hints on how to do this? |
#3
|
|||
|
|||
Thank you so much for your time. You are obviously an expert at Access! I
think I got the chart to work for me, with your excellent advise. Just two last questions... Since we are near the beginning of the project, there are no dates in the cells for most of the stages. As the stage is completed, a date will appear. Right now I don't believe the counts are correct, as the fields are either null or have blanks in them. Is there a way to accurately count the cells with DATES only? At present we are just starting this project. I want to show the entire 15 weeks of the project, but ONLY want to show the lines on the chart up to the current week (at present, week 2). Is there a way to do this? THANKS "Jon Peltier" wrote: Gail - You describe data arranged like this: Product Stage 1 Stage 2 Stage 3 A 4/11/2005 4/21/2005 4/25/2005 B 4/8/2005 4/11/2005 4/15/2005 C 4/1/2005 4/6/2005 4/14/2005 D 4/18/2005 4/30/2005 5/17/2005 E 4/13/2005 4/21/2005 4/26/2005 F 4/19/2005 5/6/2005 5/15/2005 G 4/10/2005 4/19/2005 5/1/2005 H 4/14/2005 4/19/2005 5/5/2005 (in this example it's in A2:D10) The date in a column indicates when the product in that row reached the column's stage. Now set up this range (A14:D22): Week Stage 1 Stage 2 Stage 3 3/31/2005 4/7/2005 4/14/2005 4/21/2005 4/28/2005 5/5/2005 5/12/2005 5/19/2005 The column headers are the stage labels, the row headers start with an arbitrary starting date earlier than the first completion date of Stage 1, and increment by weeks. Now some formulas. In B15 type this formula: =SUM(IF($A15<B$3:B$10,1,0)) and don't press Enter yet, first hold down CTRL+Shift, then press Enter. This makes an array formula, which Excel indicates by putting curly brackets around it: {=SUM(IF($A15<B$3:B$10,1,0))} This tells you how many products that week have not finished with that stage yet. We're comparing the date in column A of the table (always column A, not the dollar sign) with the dates in column B, rows 3-10 (always rows 3-10, note the dollar signs). No dollar signes on the B means when we copy the formula to other columns, we look at the column the formula is in. Copy the cell, then select the range B15:D22, and paste. This fills the range like so: Week Stage 1 Stage 2 Stage 3 3/31/2005 8 8 8 4/7/2005 7 7 8 4/14/2005 2 6 7 4/21/2005 0 2 6 4/28/2005 0 2 4 5/5/2005 0 1 2 5/12/2005 0 0 2 5/19/2005 0 0 0 Select this range, and use the chart wizard to make a line chart. Excel puts the dates along the bottom and draws three lines, one for each stage. They all start up at 8 on 3/31, because none have finished any stages. The stage series lines drop, Stage 1 fastest, and at the end they have all reached zero. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Gail wrote: I am working on a project with 300 products we are monitoring, with dates posted for each stage of completion. I was asked to create a burn-down chart x-axis being weeks/dates and y-axis being the number of products that have not achieved that stage in the project. Each stage would have its own 'line'. So in the beginning all products will not have achieved any stage, so the left side of the chart will have points at the top of the y-axis. And by the end of the project they will all have points at the bottom of the y-axis. I have an excel spreadsheet of all the products in rows, and the stages in columns, with the dates that the product achieves that stage in the cells. I have never heard of a burn-down chart. Can anyone give me hints on how to do this? |
#4
|
|||
|
|||
Hi Gail -
Actually, I know nothing about Access, fortunately you were asking about Excel. Change the formula in B15 to this: =SUM(IF($A15<B$3:B$10,1,0))+SUM(IF(B$3:B$10=0,1,0) ) You still have to hold CTRL+SHIFT while pressing ENTER. This assumes that you have either a date or a blank in each cell in the date range. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Gail wrote: Thank you so much for your time. You are obviously an expert at Access! I think I got the chart to work for me, with your excellent advise. Just two last questions... Since we are near the beginning of the project, there are no dates in the cells for most of the stages. As the stage is completed, a date will appear. Right now I don't believe the counts are correct, as the fields are either null or have blanks in them. Is there a way to accurately count the cells with DATES only? At present we are just starting this project. I want to show the entire 15 weeks of the project, but ONLY want to show the lines on the chart up to the current week (at present, week 2). Is there a way to do this? THANKS "Jon Peltier" wrote: Gail - You describe data arranged like this: Product Stage 1 Stage 2 Stage 3 A 4/11/2005 4/21/2005 4/25/2005 B 4/8/2005 4/11/2005 4/15/2005 C 4/1/2005 4/6/2005 4/14/2005 D 4/18/2005 4/30/2005 5/17/2005 E 4/13/2005 4/21/2005 4/26/2005 F 4/19/2005 5/6/2005 5/15/2005 G 4/10/2005 4/19/2005 5/1/2005 H 4/14/2005 4/19/2005 5/5/2005 (in this example it's in A2:D10) The date in a column indicates when the product in that row reached the column's stage. Now set up this range (A14:D22): Week Stage 1 Stage 2 Stage 3 3/31/2005 4/7/2005 4/14/2005 4/21/2005 4/28/2005 5/5/2005 5/12/2005 5/19/2005 The column headers are the stage labels, the row headers start with an arbitrary starting date earlier than the first completion date of Stage 1, and increment by weeks. Now some formulas. In B15 type this formula: =SUM(IF($A15<B$3:B$10,1,0)) and don't press Enter yet, first hold down CTRL+Shift, then press Enter. This makes an array formula, which Excel indicates by putting curly brackets around it: {=SUM(IF($A15<B$3:B$10,1,0))} This tells you how many products that week have not finished with that stage yet. We're comparing the date in column A of the table (always column A, not the dollar sign) with the dates in column B, rows 3-10 (always rows 3-10, note the dollar signs). No dollar signes on the B means when we copy the formula to other columns, we look at the column the formula is in. Copy the cell, then select the range B15:D22, and paste. This fills the range like so: Week Stage 1 Stage 2 Stage 3 3/31/2005 8 8 8 4/7/2005 7 7 8 4/14/2005 2 6 7 4/21/2005 0 2 6 4/28/2005 0 2 4 5/5/2005 0 1 2 5/12/2005 0 0 2 5/19/2005 0 0 0 Select this range, and use the chart wizard to make a line chart. Excel puts the dates along the bottom and draws three lines, one for each stage. They all start up at 8 on 3/31, because none have finished any stages. The stage series lines drop, Stage 1 fastest, and at the end they have all reached zero. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Gail wrote: I am working on a project with 300 products we are monitoring, with dates posted for each stage of completion. I was asked to create a burn-down chart x-axis being weeks/dates and y-axis being the number of products that have not achieved that stage in the project. Each stage would have its own 'line'. So in the beginning all products will not have achieved any stage, so the left side of the chart will have points at the top of the y-axis. And by the end of the project they will all have points at the bottom of the y-axis. I have an excel spreadsheet of all the products in rows, and the stages in columns, with the dates that the product achieves that stage in the cells. I have never heard of a burn-down chart. Can anyone give me hints on how to do this? |
#5
|
|||
|
|||
I have been fighting battles on both the Access and Excel fronts... sorry
for the confusion. I REALLY appreciate your help. "Jon Peltier" wrote: Hi Gail - Actually, I know nothing about Access, fortunately you were asking about Excel. Change the formula in B15 to this: =SUM(IF($A15<B$3:B$10,1,0))+SUM(IF(B$3:B$10=0,1,0) ) You still have to hold CTRL+SHIFT while pressing ENTER. This assumes that you have either a date or a blank in each cell in the date range. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Gail wrote: Thank you so much for your time. You are obviously an expert at Access! I think I got the chart to work for me, with your excellent advise. Just two last questions... Since we are near the beginning of the project, there are no dates in the cells for most of the stages. As the stage is completed, a date will appear. Right now I don't believe the counts are correct, as the fields are either null or have blanks in them. Is there a way to accurately count the cells with DATES only? At present we are just starting this project. I want to show the entire 15 weeks of the project, but ONLY want to show the lines on the chart up to the current week (at present, week 2). Is there a way to do this? THANKS "Jon Peltier" wrote: Gail - You describe data arranged like this: Product Stage 1 Stage 2 Stage 3 A 4/11/2005 4/21/2005 4/25/2005 B 4/8/2005 4/11/2005 4/15/2005 C 4/1/2005 4/6/2005 4/14/2005 D 4/18/2005 4/30/2005 5/17/2005 E 4/13/2005 4/21/2005 4/26/2005 F 4/19/2005 5/6/2005 5/15/2005 G 4/10/2005 4/19/2005 5/1/2005 H 4/14/2005 4/19/2005 5/5/2005 (in this example it's in A2:D10) The date in a column indicates when the product in that row reached the column's stage. Now set up this range (A14:D22): Week Stage 1 Stage 2 Stage 3 3/31/2005 4/7/2005 4/14/2005 4/21/2005 4/28/2005 5/5/2005 5/12/2005 5/19/2005 The column headers are the stage labels, the row headers start with an arbitrary starting date earlier than the first completion date of Stage 1, and increment by weeks. Now some formulas. In B15 type this formula: =SUM(IF($A15<B$3:B$10,1,0)) and don't press Enter yet, first hold down CTRL+Shift, then press Enter. This makes an array formula, which Excel indicates by putting curly brackets around it: {=SUM(IF($A15<B$3:B$10,1,0))} This tells you how many products that week have not finished with that stage yet. We're comparing the date in column A of the table (always column A, not the dollar sign) with the dates in column B, rows 3-10 (always rows 3-10, note the dollar signs). No dollar signes on the B means when we copy the formula to other columns, we look at the column the formula is in. Copy the cell, then select the range B15:D22, and paste. This fills the range like so: Week Stage 1 Stage 2 Stage 3 3/31/2005 8 8 8 4/7/2005 7 7 8 4/14/2005 2 6 7 4/21/2005 0 2 6 4/28/2005 0 2 4 5/5/2005 0 1 2 5/12/2005 0 0 2 5/19/2005 0 0 0 Select this range, and use the chart wizard to make a line chart. Excel puts the dates along the bottom and draws three lines, one for each stage. They all start up at 8 on 3/31, because none have finished any stages. The stage series lines drop, Stage 1 fastest, and at the end they have all reached zero. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Gail wrote: I am working on a project with 300 products we are monitoring, with dates posted for each stage of completion. I was asked to create a burn-down chart x-axis being weeks/dates and y-axis being the number of products that have not achieved that stage in the project. Each stage would have its own 'line'. So in the beginning all products will not have achieved any stage, so the left side of the chart will have points at the top of the y-axis. And by the end of the project they will all have points at the bottom of the y-axis. I have an excel spreadsheet of all the products in rows, and the stages in columns, with the dates that the product achieves that stage in the cells. I have never heard of a burn-down chart. Can anyone give me hints on how to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Timeline Chart? | Charts and Charting in Excel | |||
Editing a Chart Directly?? | Charts and Charting in Excel | |||
Editing a Chart Directly?? | Charts and Charting in Excel | |||
Problem with xlusrgal.xls file | Charts and Charting in Excel | |||
pivot table multi line chart | Charts and Charting in Excel |