More unit testing with Excel Lambdas | Hacker News Reader

More unit testing with Excel Lambdas

paul-samuels.com

48 pointsingve8 comments

It’s fun to learn about all these new formulas, and the testing in excel is great for long term spreadsheets that are reused, but I wished it was mentioned how this task is so dead easy in Excel.

Assuming column A has the values and B1 is set to 0. You just need in B2… =IF(A2>A1,B1+1,B1)

And then fill down. If you want the total, take the max of column B. Count is just =ROW() in this simple case or use the count function if you think someone will insert rows above.

If the goal is just one cell, then use sum as an array formula

={SUM(—-(A1:A1999<A2:2000)}

Or use IF in a similar manner, there’s probably dozens of ways to do this. If it were more complicated, sumproduct could be used which has a summing reduce built in. And I’m sure that excel’s Arrays could serve the purpose of the Pair that was invented here, if that is really needed.

My point here isn’t to poke holes in the fun exercise but to emphasize how excel’s dynamic programming makes these problems easy solvable by just about anyone. I don’t see a future where everyone is complaining about their coworker not using currying in their latest spreadsheet.

I've chucked an update post here that uses a variation on your suggested approach https://paul-samuels.com/blog/2022/04/27/more-unit-testing-w...

I did it using the same focus as the original post of trying to show the value of getting a formula behind some tests. In the post having this completely different more Excel like approach helps showcase the benefit of tests, where I can completely change the formula and still be confident things work because tests verify it does.

Cheers for the formula guidance

You have a well-defined test, {SUM(—-(A1:A1999<A2:2000)}, but it is unreadable and does not reference a well-defined unit of logic that is being tested.

The unreadability of the test and the lack of a referenceable unit of logic are directly related. What kind of variable name is A1:A1999? What on earth is the significance of row 1999? I don't even need to elaborate on the inadvisability of the hardcoded "1999." What is column A? A programmer would be ashamed to use a variable named "A" even for a temporary or scientific variable. Is it something so unimportant that it does even bother being named? And yet it could refer to a column concerning millions, billions or even trillions of dollars and critical decisions real world decisions being made on the basis of this comically unreadable code.

I am not being critical of your suggestion, I am being critical of Excel itself.

click Insert > Table. Voila! Proper column names. No hardcoded row numbers.

If you're going to be critical of Excel, at least be familiar enough with the product to know whether those criticisms are valid.

incompatible though with dynamic "spill" arrays..

maybe 'named ranges' solves the problem? but neither those nor lambdas are available on excel web atm iirc

Of course Excel tables are incompatible with dynamic arrays. Does PostgreSQL or SQLite or any RDBMS allow you to have columns with arbitrary and varying sizes in the same table? It doesn't make sense.

Web Excel is not Excel, not even going to go there.

I tried google sheets(excels with lambda) for Aoc 2021 until like day 6 or 7, if I have lambdas it seems that I could move further though.

As a point of comparison, consider an idiomatic approach in the vector-oriented language K. Given a vector t:

    t:199 200 208 210 200 207 240 269 260 263

The "eachprior" adverb ': captures the pattern of applying each value in a sequence to its predecessor through a verb (in this case >), giving a boolean vector:

     >':t
    0 1 1 1 0 1 1 1 0 1

Then take a sum reduction:

      +/>':t
    7

Seems like the same decomposition would be quite easy with beginner-level Excel, provided an extra "working" column.

Reply on news.ycombinator.com