Burndown Chart

[MaddMike]

My Boss has asked me to create a Burndown Chart.

Basically it will begin with the total number of tasks to be completed on the left edge of the Chart and will show the progress against a date line as things are completed the number remaining will decrease over time to zero.

I have a spread sheet that has the date column and more than one task can share the same date. I also have on the same row of each task a cell where I place a "x" if the task is complete.

I not a chart wizard (no pun intended) so I have no idea how to start except I know it will be a line chart.

Any help would be appreciated.

TIA

 

[Jon Peltier]

What you need to do is convert your list to a running total of tasks remaining. I think your data is arranged something like this:

Dates in A2:A23
Task names in B1:K1
"x" in B2:K23 if a task is finished on that date.

Here's what I've added:

L2: formula
=COUNTIF(B2:K2,"x")

This counts the "x" in that row. Fill the formula down to L23.

M1=10, total number of tasks
M2: formula
=M1-L2

This counts the total of the date before minus the completed number from this date. Fill the formula down to M23.

The chart:

Select A2:A23, hold CTRL while selecting M2:M23, so a range with two areas is selected. Run the chart wizard and create a line chart.

 

[MaddMike]

Thanks but that doesn't seem to be working for me.

Perhaps this is a better discription:

status        Date            Bldg
x             9/12/2006       A1
x             9/12/2006       A1
x             9/17/2006       A4
x             9/19/2006       A4
x             9/19/2006       A5
              9/20/2006       B1
              10/12/2006      B1
              10/12/2006      C1
              10/12/2006      C1

The data to be graphed is in colums.

I can total the number of buildings (bldg) and the number of completions (status)

I'm having touble with the graph because:

I want to have the running dates without duplicates across the x axis

I want the y axis to start at the total number of buildings to be done

I want the graph to indicate the total to be completed as time goes by. The are a total of 161 buildings to be reduced to 0 when finished.

 

[Jon Peltier]

Okay, I made a pretty good guess, given that you had only said that you had a date column. You need aother date column with one date per row.

You also made it hard to copy and paste your data, because it's filled with alternating spaces and non breaking spaces, and in the code block, you only need spaces.

Anyway, assuming the data you show is in A:C, and I'll assume it's 161 rows plus a header row (although you show duplicate buildings.....). So in D2, enter this formula:

=IF(B2=B1,"",B2)

which gives the date if it's the first time it appears in the list, or a blank. In E2:

=IF(ISERROR(SMALL($D$2:$D$10,ROW()-1)),NA(),SMALL($D$2:$D$10,ROW()-1))

which lists the dates in sorted order, with #N/A beyond the last item in the list. Without the IF(ISERROR()) piece, you'd get #NUM! errors, which hose the chart, but #N/A are ignored. In F2:

=161-SUMPRODUCT(1*($A$2:$A$10="x"),1*($B$2:$B$10<=E2))

which count the number of "x" up to and including the date in column E, subtracted from 161. In fact, it's better to put 161 into a cell and reference the cell, but it's only 6:30 am. Fill these formulas down to row 162. Keep E1 blank, and put a label in F1, something like "To Go" or whatever. The whole range looks like this, except your #N/A in columns E and F won't start in row 7 but much lower:

status  Date        Bldg                          To Go
x       9/12/2006   A1    9/12/2006   9/12/2006   159
x       9/12/2006   A1                9/17/2006   158
x       9/17/2006   A4    9/17/2006   9/19/2006   156
x       9/19/2006   A4    9/19/2006   9/20/2006   156
x       9/19/2006   A5                10/12/2006  156
        9/20/2006   B1    9/20/2006   #N/A        #N/A
        10/12/2006  B1    10/12/2006  #N/A        #N/A
        10/12/2006  C1                #N/A        #N/A
        10/12/2006  C1                #N/A        #N/A

Select the data in E:F and make your line chart.

 

[MaddMike]

Thanks Jon!!

That helps. I should have made it clearer when I first asked the question.