Making a dynamic burn-up chart with a changing ideal story points line (Scrum/agile dev)

[JakkeJakobsen]

Hi! I am trying to make a dynamic burn-up chart so that we can add story points in the middle of a sprint. If there is no change, the calculation is easy, it's just the total story points (say 100) divided by the number of days, and you add these per day down the rows. But if there is a need to add something to the sprint backlog, it will also add more story points to the total, and the goal line will make a jump from 100 to say 150 (big increase I know, just an example). The day there is an increase, the line has to make a turn and then continue up to the now higher goal line of 150. I made a mockup-table below to show how it looks

Day	Ideal Story Points	Completed Story Points	Total Story Points	Story Points Increase
1		Not relevant for calculations	100
2		-	100
3		-	100
4		-	150	50
5		-	150
6		-	150
7		-	200	50
8		-	200

This is how it would look if the goal never changed (the orange line), grey is the ideal line here, don't mind the names of the lines, they are screwed after I have tested A LOT. And in Norwegian...

This is how one change should look, again, ignore which line is which, here the grey is the total/goal and the blue is the ideal line.

But if I had more changes, it won't work of course, because I need to check for a change both below and above, and only calculate between those. Also not sure what to actually calculate.

Any help is greatly appreciated!

 

[JakkeJakobsen]

Bump, anyone got an idea?

 

[awoohaw]

@JakkeJakobsen , at least one person, unfortunately me, does not understand agile/burn up terminology.
But, i am pretty good with charts. Can you post a mini workbook with your data (anonymized) using the xl2bb add in?

I don't see a calculation column where you are dividing the story total by days... is there supposed to be a line with that value?

 

[JakkeJakobsen]

@JakkeJakobsen , at least one person, unfortunately me, does not understand agile/burn up terminology.
But, i am pretty good with charts. Can you post a mini workbook with your data (anonymized) using the xl2bb add in?

I don't see a calculation column where you are dividing the story total by days... is there supposed to be a line with that value?

Heya, right now I don't have anything really useful tbh. Instead of 100, 150 and 200 story points, we were at 45 and increased to 61 this week, so we don't have a real-life example where it would increase twice in a sprint (which for us is two or three weeks). Not sure how I use that addin here tbh, not too active.
The calculation would be simple though, 61/14, ish 4.36 per day. When this is used in "production" (we're in uni, not professionals), I will probably merge formulas to make it simpler for those in my group who don't do Excel at all.

EDIT: Merge as in having a formula in a column in the formula of another column to avoid helper columns if I can. KISS and all that.

 

[JakkeJakobsen]

=iferror((G37-F35)/(count(G$1:$AA$1)) + F35,"")

works!

 

[awoohaw]

i'm happy you found your solution!

 

[JakkeJakobsen]

=iferror((G37-F35)/(count(G$1:$AA$1)) + F35,"")

works!

I can add this is done in G35, F35 is the previous, G37 is the total story points at the current day, count is counting number of days in the sprint. If anyone ever needs this calculation. Credit to my nephew for this solution!