Burndown Chart

MaddMike

Board Regular
Joined
Jun 6, 2003
Messages
131
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Jun 6, 2003
Messages
131
Thanks but that doesn't seem to be working for me.

Perhaps this is a better discription:
Code:
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

Code:
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

Board Regular
Joined
Jun 6, 2003
Messages
131
Thanks Jon!!

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

Forum statistics

Threads
1,141,756
Messages
5,708,332
Members
421,565
Latest member
Lastadiego

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top