Monday Morning Mulling: February 2024 Challenge

On the final Friday of each month, set an Excel for you to puzzle over for the weekend.  On the Monday, we publish one suggested solution.  No-one is stating this is the best approach, it’s just the one we selected.  If you don’t like it, lump it – or contact us with your preferred solution.

The Challenge

On Friday, we asked you to imagine you had a Table named Data in Excel, containing a list of names under the column Name and corresponding numerical data under the column Grade as follows:

Your task was to write a single Excel formula that summed the Grade data between two [2] names exclusively, which we referred to as Name_1 (Cell G28) and Name_2 (Cell G29).  These names were inputs that could be changed, and the sum was to dynamically update to reflect the range of data between these two [2] names in the Data table.  You could download the original question filehere.

As always, there were some requirements:

• the formula needs to be within just one [1] column (no “helper” cells)
• the solution must work even if the order of Name_1 and Name_2 is swapped in the list
• the formula should be dynamic so that it updates when a new entry is added.

Suggested Solution for Modern Excel Users (Excel 365 and Later Versions)

You can find our Excel file here, which shows our suggested solution.  The steps are detailed below.

Crafting a Data Range with XLOOKUP and Colon

We begin our formula with the XLOOKUP function, deploying it twice to accurately retrieve the grades corresponding to Name_1 and Name_2.

This will return us the respective grades for Name_1 and Name_2:

The real magic happens when we introduce a colon ‘:’ between these two [2] XLOOKUP functions.  This action forms a dynamic array that spans from the grade of Name_1 to that of Name_2.

Even if the names selected from the dropdown are not in sequential order, this formula dynamically adjusts, ensuring the correct range is captured:

Refining the Range with DROP

At this stage, while we could directly sum the array and then subtract the two [2] values of the lookup, we opt for a more refined approach by incorporating the DROP function.  This function is instrumental in sculpting our dynamic range further before performing the summation.

We can use:

and

to remove the first and last entries in our range, the grades directly associated with Name_1 and Name_2.

Let’s combine all of that in this formula:

This formula removes the first and last row of our Range.  When we substitute Range with our earlier XLOOKUP array, this returns the exact range we wish to sum:

SUM and Error Trapping

The penultimate step here is to SUM, hence we quickly add the SUM function here:

However, we must consider edge cases, such as the non-selection of names or the selection of adjacent names, which could lead to errors in our formula:  

To address this, we wrap our summation formula within an IFERROR statement.

This formula not only performs the desired summation but also ensures that in cases where our formula would return an error, it returns a zero [0] instead.

We have created a resilient, dynamic and accurate formula that sums the data between the specified names whilst avoiding any errors.

Suggested Solution for Legacy Excel Users (Older Versions of Excel)

You can find our Excel file here, which shows our suggested solution.  The steps are detailed below.

Using INDEX MATCH and Colon to Create a Data Range

In a manner akin to the approach for modern Excel users, we will use the INDEX MATCH partnership combined with a colon to define the data range.  This method uses

to locate the grade for Name_1.  Similarly,

The above formula is employed to find the grade for Name_2.

Placing a colon between these two [2] formulae creates a range spanning from the grade of Name_1 to Name_2, viz.

Making it Exclusive

To exclusively select the data between Name_1 and Name_2, we introduce the MAX and MIN functions.  These functions are employed to determine the endpoints of our data range:

This formula is used to find the last position in the data range.

Similarly, this above formula identifies the first position.

Subsequently, we modify our original INDEX:INDEX formula to replicate the effect of the DROP function used in the modern Excel solution:

Addressing Specific Selection Scenarios

While effective, our method may not be perfect in all cases.  For instance, if Name_1 and Name_2 are adjacent, the same name is selected twice or the input cells are left empty, the formula might produce an incorrect range or result in an error.

To address this, we implement the following formula:

This formula utilises ABS and MATCH functions to detect if Name_1 and Name_2 are adjacent or identical.  Additionally, the IFERROR function will intercept any #N/A errors from the MATCH function being unable to match any name and then convert them to zero [0].  The subsequent comparison (<=1) checks if the range is suitable for summation.  We encapsulate this logic within a comprehensive IF statement:

This approach ensures an accurate and exclusive summation between the selected names, catering to the nuances of legacy Excel.

Word to the Wise

We appreciate there are many, many ways this could have been achieved.  If you have come up with an alternative, radically different approach, congratulations – that’s half the fun of Excel!

The Final Friday Fix will return on Friday 29 March 2024 with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.