Sort and sub-sort a named range|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Absolutely. Sorry not to have done that originally. I will do that now. There are actually THREE challenges I'm facing. Since they are all related to the same fairly complicated workbook, I'm hoping I can raise them all in this one post.  

As you may notice, I used your clever technique to create set the data source for my charts as named ranges. I've done my best to be clever, but, apparently, I still have much to learn.

CHALLENGE #1

Worksheet: "ReportPPM"

Purpose: This chart shows the SAFe (Scaled Agile Framework) "Program Predictability Measure" (PPM) for an Agile Release Train (ART). As you may know, an ART is simply a team of Agile teams working together toward a common goal over the course of a Program Increment (PI). A PI is a mid-length timebox (e.g., calendar quarter) - a collection of Scrum sprints. The chart visualizes the "Actual Business Value" (ABV) of planned objectives that the team delivered within the PI (scored using a range from 0 to 10) versus the Planned Business Value (PBV) of those objectives (scored using a range of 1 to 10), expressed as a percentage. Each ART gets one PPM score for each PI. That PPM score is the aggregation of each team's individual TPM scores (the same arithmetic, but at the team level - as depicted on the "ReportTPM" worksheet). The chart shows that ratio as a line graph over a column chart that depicts the 80%-100% target range. 

Observed Behavior: The numbers on the graph appear to be accurate. However, the team names - which are not relevant at the ART level - show up on the horizontal axis even though the team names are not in the pivot table that informs the chart. This must have something to do with the pivot table and/or the "Chart_PPM_Axis" formula used in the charts "Select Data" for the axis.    

Desired Behavior: We should never see the names of the teams that comprise the ART on this chart. I'd like the horizontal axis to show two levels. First is the ART (e.g., "Momentum" and "GBI"). Then for each ART we should see the PIs within that art (e.g., "PI-1", "PI-2", and so on). Then for each PI we should have a data point. 

CHALLENGE #2

Worksheet: "Enter Velocity"

Purpose: This is where the user provides the performance data for each team for each Scrum sprint, including the number of Story Points the team planned ("Committed") and the number of Story Points they actually got done ("Completed").

Desired Behavior: I sense a trend in the data and I'm looking for a way to visualize it. To do that, first I need to isolate the relevant rows. I think what's happening is that teams have a tendency to try to play "catch up." For example, if they plan 20 points but only deliver 15 points in sprint 1 (S1.1), I want to know how often they then plan 25 points in sprint 2 (S1.2) - trying to catch on the 5 points they didn't get done in the first sprint. Ideally, all things being equal, a team should plan into each sprint a number of points fairly close to the number they actually completed in the previous sprint. They should never play catch-up, because that's a losing game. So, I'm looking to visualize the frequency with which teams plan more than they previously delivered - and whether they then failed to deliver (again).

Problem: The table includes data from many teams and the sequence of the data cannot be relied upon to be chronologically accurate. So, I need a way to:

1. Separate out the data for each team
2. Sequence the sprints by their dates (not their names)
3. Determine whether a sprint in which Completed > Committed is then immediately followed by a sprint in which Committed > previous sprint's Completed value 

CHALLENGE #3

Worksheet: "Setup Timeboxes"

Purpose: This is where the user creates sprints.

Problem:

• First, and most importantly, I'd like to warn the user if any of the timebox dates overlap, even by one day. For example, if S1.1 runs from 1/1/2022 to 1/15/2022 and S1.2 runs from 1/15/2022 to 1/30/2022, then I'd like to caution that S1.1 and S1.2 overlap by one day.
• Second, I'd like to caution if the name given to a sprint is not unique in the list. Even better if I can prevent this.

 I have tried literally for weeks to solve the problems, but I'm stumped! I hope someone can help. Many thanks in advance.

Thanks, Mynda -

CHALLENGE #1: CHART AXIS

The Refresh Chart macro only runs when that button is pushed and only performs two steps. It refreshes all workbook connections and all pivot caches. It does not set the scope of the charts and so cannot be the culprit. I'm pretty sure the culprit is somewhere in the formula for the axis, but I can't figure it out. Would you mind terribly taking one more look? It's important that I find a way to aggregate to the ART level and leave the teams out of the report.

CHALLENGE #2: CATCH TEAMS TRYING TO CATCH-UP

Your example does a good job of visualizing whether a team's Completed values are increasing from sprint to sprint. However, that's not quite the problem I'm trying to solve. I'm trying to compare Committed in Sprint T against Completed in Sprint T-1. What I need is in the table below, but I cannot figure out how to compare the value in one column against the value in another column in another row.

By the way, even this assumes that the previous row is the correct row to compare against, which may not be the case, since the table may not be sorted chronologically. Maybe I can do a lookup of each sprint's start and end dates. This quickly gets complicated!  

Challenge #3

Your proposed formula is simple and clever, but it assumes the sprints are sorted chronologically (and so each row can compare to the previous row), which may not be the case. What I am trying to do is check whether Sprint Start or Sprint End for a row falls between any of the other timeboxes in the table. I feel like I almost have to loop through every row, but that could tax the system (although the table will never be enormous). 

Hi,Mynda and Velouria -

I sincerely appreciate the time you're both investing in this, and hope others find the topics and analysis of interest and value.

When I right-click the charts on the Velocity, TPM, and PPM report sheets and then choose Select Data, I see only references to the current (active) workbook; the one dated "2022-04-19B". I'm not sure why you'd be seeing something different. And they all point to the current formulas. When I look at the Name Manager, all the formulas seem to be correct (although the axis formulas are the original, less efficient ones).

What am I missing here? Is there a way to use your more efficient formula for the axes?  

With respect to the limitations of Bar charts... I guess I'm not quite understanding. When more than one team is selected in the slicer, then the chart shows TWO levels on the axis - ART and Team. When only one team is selected, the axis only shows the ART, not the Team. In this scenario, however, the ART is not useful. It's like having a report specifically about Apples but having the axis say "Fruits." Unless you look at the slicer (or my dynamic chart title) you couldn't tell it was about apples (i.e., a particular team).  

Finally, with respect to the Power Query... I am prepared to teach myself how to use this tools but I get a lot of errors right now. See pics.

Thanks again. I can't express my gratitude enough.

Mynda and Velouria -

You are great teachers.

I made the suggested formula change on both the ReportBurndown and ReportConfidence axes and both work perfectly now. Thank you.

I will open two separate threads. One for my Power Query question (teams playing "catch-up") and the other for detecting overlapping sprints without Office 365 SEQUENCE formula.

I cannot thank you enough for your help. I've learned a lot in just a few days.