4 ms·

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

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.

zzzonked 5y ago
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-with-excel-lambdas-revisited/ 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
cttet 5y ago
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.
riskneutral 5y ago
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.
- listenallyall 5y ago
  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.
  - smegsicle 5y ago
    incompatible though with dynamic "spill" arrays.. maybe 'named ranges' solves the problem? but neither those nor lambdas are available on excel web atm iirc
    - listenallyall 5y ago
      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.

zzzonked 5y ago

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-with-excel-lambdas-revisited/ 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

cttet 5y ago

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.

riskneutral 5y ago

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.

listenallyall 5y ago
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.
- smegsicle 5y ago
  incompatible though with dynamic "spill" arrays.. maybe 'named ranges' solves the problem? but neither those nor lambdas are available on excel web atm iirc
  - listenallyall 5y ago
    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.