Hjelp til Excel-formel

[Jakke]

Hei, skal lage en burnup-chart, og der har vi en linje som er mål, en ideell linje som går fra 0 arbeidstimer og til målet, og en som viser faktisk arbeid gjort per dag.

Dette er gjort i Google Sheets, men mesteparten om ikke alt er identisk i Google Sheets mtp formler.

Saken er at vi kan endre målet midt i løpet, slik at det går fra 61 til 81 f.eks, men det kan også endres to ganger på denne perioden. Hver gang det er en endring, så vil jeg at det skal bli en knekk på den ideelle linja, slik at om det er 61 i tre dager, så legger vi på 20, så skal snittet på de tre første dagene tilsvare økningen på 61/14 dager, mens fra dag 4 når det er 81, skal økningen per dag tilsvare dette til neste endring av mål.

Her er ved ingen endring av arbeidstimer (story points)

Her er slik det skal se ut om det er kun en endring i løpet av perioden.

Her er hva som skjer om jeg endrer dette to ganger, da blir det en differanse mellom Mål og Ideell linje siden den kun endrer seg en gang.

Jeg klarer ikke finne ut hvordan jeg skal regne ut dette slik at det er dynamisk. Vi har også noen perioder som er på 21 dager istedet for 14, så det er det å ikke låse seg til 14 dager.

 

[Jakke]

Her er selve tabellen jeg bruker, det er ikke riktige tall på Ideelt, da jeg har forsket en hel masse på det og ikke fått til. Man kan også se hvor jeg legger inn endringen slik at målet endrer seg. Dette kan skje en gang eller 10 ganger (dog helst ikke).

[Phantom Software]

Så sier chatgpt:
 

To create a burn-up chart of story points in Excel that can handle different time frames and changing target story points, you can follow these steps:

1. Create a table in Excel with the following columns: Date, Ideal Story Points, Completed Story Points, and Target Story Points.

2. Enter the dates for the time frame you want to track in the Date column. For example, if you are tracking a sprint that lasts 14 days, you would enter 14 dates starting from the first day of the sprint.

3. Enter the Ideal Story Points column. This column represents the ideal amount of story points that should be completed each day to reach the target on time. To calculate this, divide the total number of story points by the number of days in the sprint. For example, if the target is 100 story points and the sprint is 14 days, the ideal story points would be 7.14 per day.

4. Enter the Completed Story Points column. This column represents the actual amount of story points completed each day. As work is completed, add up the total story points completed so far and enter it in this column.

5. Enter the Target Story Points column. This column represents the target amount of story points to complete by the end of the sprint. If the target changes during the sprint, update this column accordingly.

6. Create a line graph in Excel and select the Date column as the x-axis and the Ideal Story Points, Completed Story Points, and Target Story Points columns as the y-axis.

7. Format the Ideal Story Points line to be a dashed line to differentiate it from the other two lines.

8. Format the graph to display the data in the way you prefer, such as showing the axis titles, setting the axis scales, and changing the colors of the lines.

As the target story points and time frames change, update the data in the table and the graph will automatically adjust to reflect the changes.

 

For 3) it needs to handle changing the target at any time. What should the excel formula behind the cells in this row be so that the ideal line graph is correct and changed when target change for example on day 3 and on day 7?

 

 

To handle changing targets in the middle of the sprint, you will need to use the IF function in Excel to adjust the Ideal Story Points column based on the target changes.

Assuming the target story points are entered in column D and the ideal story points are calculated in column B, you can use the following formula in cell B2 to calculate the ideal story points:

= = D2 / COUNTA($A$2:$A$15)

This formula divides the target story points in cell D2 by the number of days in the sprint. The COUNTA function counts the number of non-empty cells in the date range $A$2:$A$15. You can adjust the range to match the number of days in your sprint.

To handle changing targets, you can use the following formula in cell B3 and drag it down to fill the rest of the column.

 

= = IF(ROW()<=MATCH(TODAY(),$A$2:$A$15,1), D2 / COUNTA($A$2:$A$15), D3 / (COUNTA($A$2:$A$15)-ROW()+MATCH(TODAY(),$A$2:$A$15,1)))
 

This formula checks the current row number using the ROW function and compares it to the row number of the current date using the MATCH function. If the current row is less than or equal to the row number of the current date, it uses the original target story points and divides it by the number of days in the sprint. If the current row is greater than the row number of the current date, it uses the updated target story points and divides it by the remaining number of days in the sprint.

Note that this formula assumes that the updated target story points are entered in the row corresponding to the date when the change was made. If you want to update the target story points in a separate row, you will need to modify the formula to reference the correct row.

[Jakke]

1 hour ago, Phantom Software said:

Så sier chatgpt:
 

To create a burn-up chart of story points in Excel that can handle different time frames and changing target story points, you can follow these steps:

1. Create a table in Excel with the following columns: Date, Ideal Story Points, Completed Story Points, and Target Story Points.

2. Enter the dates for the time frame you want to track in the Date column. For example, if you are tracking a sprint that lasts 14 days, you would enter 14 dates starting from the first day of the sprint.

3. Enter the Ideal Story Points column. This column represents the ideal amount of story points that should be completed each day to reach the target on time. To calculate this, divide the total number of story points by the number of days in the sprint. For example, if the target is 100 story points and the sprint is 14 days, the ideal story points would be 7.14 per day.

4. Enter the Completed Story Points column. This column represents the actual amount of story points completed each day. As work is completed, add up the total story points completed so far and enter it in this column.

5. Enter the Target Story Points column. This column represents the target amount of story points to complete by the end of the sprint. If the target changes during the sprint, update this column accordingly.

6. Create a line graph in Excel and select the Date column as the x-axis and the Ideal Story Points, Completed Story Points, and Target Story Points columns as the y-axis.

7. Format the Ideal Story Points line to be a dashed line to differentiate it from the other two lines.

8. Format the graph to display the data in the way you prefer, such as showing the axis titles, setting the axis scales, and changing the colors of the lines.

As the target story points and time frames change, update the data in the table and the graph will automatically adjust to reflect the changes.

 

 

For 3) it needs to handle changing the target at any time. What should the excel formula behind the cells in this row be so that the ideal line graph is correct and changed when target change for example on day 3 and on day 7?

 

 

 

To handle changing targets in the middle of the sprint, you will need to use the IF function in Excel to adjust the Ideal Story Points column based on the target changes.

Assuming the target story points are entered in column D and the ideal story points are calculated in column B, you can use the following formula in cell B2 to calculate the ideal story points:

= = D2 / COUNTA($A$2:$A$15)

This formula divides the target story points in cell D2 by the number of days in the sprint. The COUNTA function counts the number of non-empty cells in the date range $A$2:$A$15. You can adjust the range to match the number of days in your sprint.

To handle changing targets, you can use the following formula in cell B3 and drag it down to fill the rest of the column.

 

= = IF(ROW()<=MATCH(TODAY(),$A$2:$A$15,1), D2 / COUNTA($A$2:$A$15), D3 / (COUNTA($A$2:$A$15)-ROW()+MATCH(TODAY(),$A$2:$A$15,1)))
 

This formula checks the current row number using the ROW function and compares it to the row number of the current date using the MATCH function. If the current row is less than or equal to the row number of the current date, it uses the original target story points and divides it by the number of days in the sprint. If the current row is greater than the row number of the current date, it uses the updated target story points and divides it by the remaining number of days in the sprint.

Note that this formula assumes that the updated target story points are entered in the row corresponding to the date when the change was made. If you want to update the target story points in a separate row, you will need to modify the formula to reference the correct row.

Det der funker nok ikke, det var ganske på bærtur faktisk. Har prøvd meg på ChatGPT før, den er ganske ubrukelig i mer avansert Excel, programmering ol. Bra til språk, bra til eksempler for APIer f.eks, men ellers, nei. Da vaser den alt for mye, dessverre.

La inn alt akkurat som den sa, og med en endring fra 100 til 120 på dag 4, så blir ideal story points for mye totalt, og den har også et lite hopp på slutten, det skal jo bli en helt rett og jevn linje fra endring til endring.

[Phantom Software]

Det var litt morsomt å prøve, men virket unødvendig kompleks. 
 

Du kan vel bare lage to hjelperader: En som holder antall dager igjen i perioden, og en som holder antall "ideelle" story points som er igjen i perioden. Så deler du den ene med den andre for å få punktene til den ideelle linjen din?

[NoBo]

Fant artikkelen How to Create a Burn-up Chart in Excel (With Easy Steps) men det blir kanskje for Excel-spesifikt 🤔

Hva med This is a set of functions and macros for Google Sheets to allow easier creation of burn-up charts.(?) Tilgørende mal: take a copy and start making your own burn-up charts very easily. Instructions are included.