Burn-down chart

[Gail]

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?

 

[Jon Peltier]

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 A210) The date in a column indicates when the
product in that row reached the column's stage.

Now set up this range (A1422):

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 B1522, 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]

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]

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]

I have been fighting battles on both the Access and Excel fronts... sorry
for the confusion. I REALLY appreciate your help.