Excel BI Challenge 2 : The Power of First Principles in Problem Solving

I recently had a look at the answers to Excel BI Challenge #2. The challenge itself, at first glance, seems fairly straightforward. It’s not asking for much complexity, really, but the real value lies in how people approach it. Giles Male, for instance, recorded his live attempt, which I found particularly valuable. (See this post by Myles Arnott) Watching someone tackle a problem in real time reveals their thought process—a crucial aspect often missing from pre-prepared answers. So, hats off to you, Giles! Or, as I jest, I’d take my toupee off if I had one.

What astounds me about how many approach these Excel challenges is the overwhelming focus on Excel features themselves. They seem to dissect the problem through the lens of formulas, functions, or features within the software rather than the fundamental nature of the problem itself. This got me thinking: what if we approached these problems from first principles, or, as some might say, good old common sense?

First Principles: Breaking it Down

What does “first principles” mean? It’s a method where we aim to understand a problem without referencing anything else that might require its own explanation. In simpler terms, it’s about getting to the core of the problem in the most direct way, without layering in unnecessary complexity. Let’s consider the challenge at hand: That means, in this case, without knowing any formulas and how they work.

The task involves starting from the bottom of a list, accumulating values as we move upwards, but ignoring any zeros. The goal is to sum the first three non-zero values we encounter and stop once we’ve done so. That’s it! Notice, I haven’t mentioned any specific Excel formulas. It’s about understanding the problem logically, not with reference to any tool.

An Example in Plain Terms

Imagine explaining this to someone who knows nothing about Excel—or even a child. You could say:

1. Start at the last row.
2. If the number isn’t zero, add it to our running total.
3. Keep track of how many numbers you’ve added, and stop when you’ve added three.

There’s no need to reference Excel’s SUM or IF functions here. It’s about understanding the task at its core, not how we’ll implement it in software.

Can a Child of Seven Understand It?

It’s this simple.

Let’s say I say to a seven year old kid, ‘I’m going to throw you one of your coloured plastic balls. When you catch it I want you to put it in that basket. Unless it is black. If so, simply throw it over there – don’t put it in the basket. When you have put three balls in the basket, I want you to yell ‘stop’. Then I want you to bring me all the balls in the basket’.

In principle, that’s all this problem is.

Implementation: The Basics of Logic

Now, once we’ve understood the problem from first principles, we can move on to implementation. But before jumping into specific Excel functions or VBA code, let’s look at the building blocks of this logic.

1. Memory (accumulator): We need a place to store the running total. This is our “accumulator.”
2. Conditions (if statements): We only add values to the accumulator if they’re non-zero.
3. Loops: We keep checking the next value above until we’ve added three numbers.

These are basic programming concepts, not tied to any particular tool like Excel. This is how logic was simplified back in 1963 when John G. Kemeny and Thomas E. Kurtz developed the BASIC programming language. They wanted to make it easy for non-experts to implement logic without needing a degree in computer science. That’s exactly what we’re doing here—simplifying the logic so it’s easily understood and reusable. Excel gives us an implementation of BASIC so we can apply logic without a degree in computer science.

The Pitfall of Non-Scalable Solutions

Here’s where things get interesting. What if the problem changes? What if instead of just summing values in one column, you now need to do the same for multiple columns or across a grid of numbers? How does your method adapt?

The real test of a solution is whether it scales. If your logic can’t be applied to a larger problem without starting from scratch, then it’s not sustainable or teachable. The goal is to create reusable methodologies that can be easily adapted to new but similar challenges.

Let’s take a variation of this problem:

• You have a grid of numbers.
• In each column, you want to sum the first three non-zero values from the bottom up.
• You need to extend this logic across multiple columns.

If your solution to the original challenge can’t be adapted to this new problem, then the methodology has failed. We’re not just solving isolated problems; we’re developing solutions that can be scaled and reused.

An Even Tougher Challenge!

Let’s say the data we’re needing to apply this logic to is not a column, but a grid of numbers, even 100 by 100. And let’s say the logic is slightly changed. Say, we need to start at any point, not just the bottom. And instead of going up a column we need to go diagonally in any of the four directions – North West, North East, South East, South West.

We need the 4 answers, from any selected cell.

Will someone learning the logic of the simple version in the Excel BI No 2 Challenge be able to reuse what they learned in this variation?

And also, when confronted with this new altered requirement, will the learner identify it as a variation of the same type of problem? Or will they see this as a completely new problem?

If they do see it as a ‘different problem’ then they have not learned anything (useful) from the first one!

Conclusion: Reusable Logic

The key takeaway here is that when we approach problems from first principles, we create solutions that are simple, scalable, and reusable. These kinds of solutions aren’t limited by the specific tools we’re using, whether it’s Excel, Python, or any other platform. The logic remains the same, and that’s what matters.

And, we need to immediately identify a new problem as a variation of one that we have already encountered. For example, we know how to get on a No 27 bus. Now if we have to get on a No 254 bus, do we shake with trepidation that we might not know how to do it, or we are confident that the technique is similar? That we identify it as something we already know, have done, and can do again.

So next time you face a problem—whether it’s an Excel challenge or something else—try stripping it back to first principles. You might find that the solution is much simpler than it initially seemed. And, most importantly, you’ll be able to reuse that logic when the next, more complex problem inevitably comes along.

Let’s start thinking in ways that are reusable, scalable, and, above all, logical. Isn’t that what true problem-solving is all about?

Here is my Solution

iRow = 10

n = 0
Do Until n = 3

    If Cells(iRow, 1).Value <> 0 Then
        intSum = intSum + Cells(iRow, 1).Value
        n = n + 1
    End If

    iRow = iRow - 1

Loop

Range("C1").Value = intSum

TBA: I shall record a walk through of this code shortly.