# Burndown Chart

##### Board Regular
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
"x" in B2:K23 if a task is finished on that date.

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

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

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.

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.

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.

Thanks Jon!!

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

Replies
0
Views
316
Replies
10
Views
403
Replies
3
Views
471
Replies
6
Views
3K
Replies
3
Views
274

1,218,625
Messages
6,143,576
Members
450,493
Latest member
Woejeber

### 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.

### Which adblocker are you using?

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

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