Hacker News Reader

Comment by DavidPeiffer | Hacker News Reader

As a frequent user of Excel, I very much welcome this. Being able to build a function library beyond User Defined (UDF, VBA functions that can be called from a formula) and a Personal.xlsb file (VBA that's available in any open Excel file)

One common use case this helps takes the general form "If A2+B2 > 10,then A2+B2, else 10". "A2+B2" needs to be updated, it has to be updated twice. Alternately, you could have a "helper column" C2=A2+B2, but this adds clutter to the whole spreadsheet.

I have a number of UDF's which lend themselves well to this, such as a triangular distribution calculator. Execution through Lambda should allow the undo stack to continue working (normally ditched by executing VBA) and hopefully give a performance boost.

View on HN

For that type of pattern, you can also use the new LET function:

    LET(myval, A2+B2, IF(myval>10, myval, 10))

https://support.microsoft.com/en-us/office/let-function-3484...

Thanks! Haven't sunk my teeth into the new round of features yet, but have found great utility from the last batch (unique, filter, sort) and the prior batch.

Isn’t that just =MAX(A2+B2,10)? Anyway I know what you mean. It seems fine to make a sheet that behaves as variables. Ie a sheet that is two columns, the comment for an intermediate and its values. Reference it elsewhere. Re-engineering the whole Excel as a website, with its attendant sandboxing, seems to be the future.

This reminds me of the IFERROR().

Without IFERROR, a common pattern would be IF(ISERROR(A2+B2),0,A2+B2).

With IFERROR, you can just do IFERROR(A2+B2,0)

And IFERROR() wasn't added until Excel 2007. The other way to make that operation less unwieldy if the expression was complicated, to avoid repeating it I'd put that inside another cell, and that way it would be =IF(ISERROR(C2),0,C2), but IFERROR is a much better solution.

Excel is such a good tool in many ways, and such a bad tool in many ways. Really experienced power users can follow Excel formulas much easier than blocks of imperative code. But the more complicated it gets, the harder it can be to follow it all. But that also goes the same for software, too. I do think that there is something powerful about the "debugging" you always have turned on in Excel, in that you always know what value a formula has produced, even after it has run. And you can (usually) easily see at what point an error started in your calculations.

For the programmers out there who aren't fans of Excel, or aren't super familiar with it, if you haven't seen "You Suck at Excel with Joel Spolsky" [0] you might be pretty amazed at what you can do with Excel at an intermediate/advanced level.

[0] https://www.youtube.com/watch?v=0nbkaYsR94c

"to avoid repeating it I'd put that inside another cell"

100%! I like to do that in other cases as well, just to keep formulae simple enough that someone else can easily audit the whole spreadsheet.

I'd rather have 5 extra columns in a calculation, then have a huge formula in a single column. This habit is so strong that I often do the same thing with Pandas: adding extra columns to a dataframe for intermediate calculations, when it would be better to write a larger function and .apply() it all at once.

Yep. Exactly the same reason we break up procedures and functions into smaller units in procedural code, to be able to reason about individual pieces, and understand each step.

I've never used Pandas, but I imagine I'd have the exact same instincts to break calculations up as you do, since Excel was my "first programming language" that I was first exposed to in the 4th grade, haha. I certainly didn't learn much advanced stuff at that point, mostly because of the time period and being in a rural Midwest area there weren't a lot of programmers around to learn from and the internet was rather different in the mid 90's. :) But Excel planted the seed of programming in my mind, even though I didn't know what "programming" was.

Yes, that was a max function. I was trying to keep it simple for people not familiar with the syntax.

I commonly use a lookup function in that context (vlookup, xlookup, or index/match).

Reply on news.ycombinator.com