+ Reply to Thread
Results 1 to 7 of 7

Burn down Chart

  1. #1
    Registered User
    Join Date
    10-20-2020
    Location
    Germany
    MS-Off Ver
    Office365
    Posts
    6

    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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-20-2020
    Location
    Germany
    MS-Off Ver
    Office365
    Posts
    6

    Re: Burn down Chart

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

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,275

    Re: Burn down Chart

    Try pasting the following into cell C7 and then dragging the fill handle across to cell K7:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Registered User
    Join Date
    10-20-2020
    Location
    Germany
    MS-Off Ver
    Office365
    Posts
    6

    Re: Burn down Chart

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

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,275

    Re: Burn down Chart

    Good question, I use it because if the table of data is moved the formula adjusts. For instance select column A and then press the Ctrl, Shift and + keys. That will have moved the "Day" column over to column B. Now check the formula and you'll see that it reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If I had used -1 instead it would read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and produce errors/incorrect results.
    You're Welcome and thank you for the feedback. 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.

  6. #6
    Registered User
    Join Date
    10-20-2020
    Location
    Germany
    MS-Off Ver
    Office365
    Posts
    6

    Re: Burn down Chart

    Quote Originally Posted by JeteMc View Post
    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.

    Quote Originally Posted by JeteMc View Post
    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..."

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,275

    Re: Burn down Chart

    I appreciate that you made an attempt. I think that the site's owner may be doing some maintenance which has changed some features of the forum. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Fuel Burn Adjustment Formula
    By ChrisHook in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-06-2020, 03:55 AM
  2. [SOLVED] fixing a burn rate calculator
    By canadianjameson in forum Excel General
    Replies: 7
    Last Post: 01-03-2020, 02:23 AM
  3. Fuel Burn Formula
    By guitarist00 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-14-2014, 04:46 AM
  4. Hi from a budding excel crash & burn dummy
    By warn1936 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 11-10-2012, 10:00 AM
  5. [SOLVED] How to calculate fuel burn in litres per H:MM
    By nje in forum Excel General
    Replies: 3
    Last Post: 09-17-2012, 07:51 AM
  6. [SOLVED] How do I burn a cd with an excel spreadsheet?
    By laxman in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 05-13-2006, 04:50 PM
  7. [SOLVED] Burn-down chart
    By Gail in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-21-2005, 09:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1