Hello -
Please see attached XLS sample.
I have a table of data that shows teams, sprints, planned story points, and actual story points. I'm trying to create a formula to calculate the VARIANCE field.
Specifically, I need to compare what each team COMMITTED (planned) to deliver in Sprint T against what they actually COMPLETED in Sprint T-1 (i.e., the prior sprint) and also take into consideration whether or not they met their commitment in Sprint T-1.
So, there are only a few possibilities:
- Met commitment in Sprint 1
- Did not meet commitment in Sprint 1
- Planned more in Sprint 2 than they completed in Sprint 1
- Did not plan more in Sprint 2 than they completed in Sprint 1
If #2 is TRUE and #3 is TRUE then this team is playing "catch-up." That is, they are trying to deliver more points in Sprint 2 because they under delivered in Sprint 1.
I'm not sure how to compare a cell value in one column (e.g., Sprint 3 Commitment) against a different column in a different row (e.g., Sprint 2 Completed), especially when the rows are in unpredictable positions in the table.
See XLS for details.
Thanks
Hey Shawn!
I used a combination of VLOOKUP and INDEX/MATCH in your Variance column. So here's the formula I concocted and what it's doing:
=IFERROR([@[Committed THIS Sprint]]/(VLOOKUP([@Team]&" | "&INDEX(Table2[Sprint],MATCH($B13,Table2[Sprint],0)-1),Table1[[Unique Sprint]:[Completed THIS Sprint]],4,FALSE))-1,0)
Instead of hard coding the cell you are looking for the Unique sprint name by combining the team name of the row, the pipe, finding the previous sprint name, and then returning the number of sprints they completed for that previous sprint.
Really you could also just use an AND statement and find the team name and use the INDEX/MATCH formula to find the previous sprint name, that would work just as well.
Hope that's what you are looking for.
Thanks, Jessica -
I used your idea to craft a slightly different solution. My approach does require that the rows be in any particular sequence.
- I separate the sprint number
- =TRIM(MID([@SPRINT],FIND(".",[@SPRINT])+1,256))
- I figure out the previous sprint number
- =IF([@[SPRINT NUMERIC]]-1>0,[@[SPRINT NUMERIC]]-1,NA())
- I figure out if the previous sprint number is legit
- =[@[ART TEAM]] & " | S" & [@[PI NUMERIC]]& "." & [@[PREV SPRINT NUMERIC]]
- I get the commitment from the previous sprint
- =INDEX(Table_Velocity, MATCH([@[PREV UNIQUE SPRINT]],[ART TEAM SPRINT],0),5)
- I get the velocity (completed) from the previous sprint
- =INDEX(Table_Velocity, MATCH([@[PREV UNIQUE SPRINT]],[ART TEAM SPRINT],0),6)
- I calculate the ratio
- =IFERROR(NUMBERVALUE(([@COMMITTED] / [@[PREV SPRINT COMPLETED]]-1)),0)
Thanks for the great idea!
