Burn down Chart

Hi community,

everyone who has worked in a scrum project or something similar knows it. The burn down chart. I am currently trying to create one with excel and as long as everything goes as planned (when has that ever happened?!) it works. If at any point the sum increases, the burn down is not accurate anymore until every column has been filled.

Burn down Chart.JPG

Some explanations to the rows:

row 7/Ideal Burn-Down: What should be left of the overall work?
row 8/Remaining Effort: What is actually left of the overall work?
row 9/Delta: The difference between the two
row 10: Calculation help. 1 for every day of the sprint except the first two, as there are usually a lot of ceremonies, preventing actual work.

As you can see in the screenshot and the file, the ideal burn-down does not compute as i want it, as soon as some work get's added down the line.

Now to my actual question: I would like to adjust the formula in row 7 (example K7:

Please Login or Register  to view this content.

) to get rid of the enclosing IF and make it so that the two appearances of K6 should be K6 OR J6 OR I6 OR... until the first cell with a value <> 0 has been reached. Is there an easy way to achieve this without having to add numerous IF clauses and making the formula terribly long or using VBA?

Regards,
Karl

I assume no one has an idea? Can you point me to another forum where I might find additional expertise?

Nice solution! Thanks a lot. Just one question: Why the -column($A6) instead of -1?

Originally Posted by JeteMc

Good question, I use it because if the table of data is moved the formula adjusts.

I see. As I don't intend to ever move the table it feels kind of redundant, but for future uses I'm probably going to leave it in anyways.

Originally Posted by JeteMc

Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

I would like to assist, but I only have "Show Printable Version", "Email this Page...", "Unsubscribe from this Thread" and "Add a Poll to this Thread..."