More unit testing with Excel Lambdas revisited

Like all programming there are many ways to solve any problem and I saw a comment that had a pretty neat, more Excelesque way (I think - I’m not an Excel user) of solving the problem. To drive home the benefit of testing this post adds a couple of tests to the current approach, then I’ll rewrite the solution entirely but keep the tests the same. If all goes well the formula will be completely different but my level of confidence in the calculation will still be high as it satisfies my assumptions that I encoded in some tests.

Let’s add some tests

For a post that was about testing I shockingly didn’t test the final formula 🤦🏼‍♂️, which currently looks like this

=Pair.First(REDUCE(PairMake(0,Data!A1),Data!A1:Data!A2000,NextPartialResult))

The PairMake function and NextPartialResult function had tests written but the whole thing did not. Therefore it’s not safe for me to just change the above formula until I get it behind some tests.

For testing this I’m going to use my current data set and pick different slices of it to produce different results from the formula:

With the above I can massage my original solution into a named function called SolveTask1 (naming is hard) that will make all the above tests pass.

SolveTask1 = LAMBDA(range,
    Pair.First(REDUCE(PairMake(0,OFFSET(range,0,0,1)),range,NextPartialResult))
);

Throw everything away and write again

With these tests in place I have a level of confidence that if I call SolveTask1 then it should behave as long as I keep the same tests. The solution I’m going to implement (there could be many more) is based on a comment from twobitshifter who suggested ={SUM(—-(A1:A1999<A2:2000)}.

If I rejig the suggested formula to work in a named function I come up with the following that also passes my tests

SolveTask1 = LAMBDA(range,
    SUM(--(OFFSET(range,0,0,ROWS(range) - 1)<OFFSET(range,1,0,ROWS(range) - 1)))
);

This is a real improvement as I’ve got my final calculation behind some tests that gives me confidence. It also means I can delete all the old tests from my previous post and remove the NextPartialResult helper function entirely - throwing code away is a good thing as less code = less bugs.

Of course we don’t have to stop right there, I might look at the solution above and not be happy about the duplication of ROWS(range) - 1. Because this is all tested I can very quickly introduce a LET and as all of my tests still pass I know I didn’t fudge something up whilst making the change.

SolveTask1 = LAMBDA(range,
    LET(
        sliceSize, ROWS(range) - 1,
        SUM(--(OFFSET(range,0,0,sliceSize)<OFFSET(range,1,0,sliceSize)))
    )
);

Conclusion

I was able to take a working formula and change it a few times without worrying if I was going to break anything. The first change was a complete rewrite and change of approach and the second change was more of an iterative improvement. Having the tests present gave me the confidence to make broad changes without needing to go and do a load of manual verification or to spend forever statically analysing the code to guess that it still works.