News for hackers. Stuff that matters.
Re:The Tyranny of Spreadsheets by catherd on Friday July 23, 2021 @01:11AM (#27927443)
The thing that's always baffled me about Excel is why you must always work in "minified" mode when composing formulas.

It seems like just adding the ability to spread a calculation out over multiple lines and add some indentation would make the bugs everyone complains about go down by... a lot.

Re:The Tyranny of Spreadsheets by quietbritishjim on Friday July 23, 2021 @04:44AM (#27928563)
You can just spread the formula over multiple cells. Instead of

  A3: IF(<boolean>, <result if true>, <result if false>)
where each of the three parameters are complex formulae, you can do:

  A3: IF(B3, C3, D3)
  B3: <boolean>
  C3: <X>
  D3: <Y>
Not only is the formula now broken down into simpler chunks, you also get to inspect the component results (like watches in a breakpoint! sorta...). Then you can just hide the relevant columns if you like (B,C,D in this case). You can even use a separate sheet and hide the whole sheet if you wish.
Re:The Tyranny of Spreadsheets by DataGata on Tuesday July 27, 2021 @12:46AM (#27968740)
You say this but it still feels gross to do.
Re:The Tyranny of Spreadsheets by akho on Friday July 23, 2021 @05:33AM (#27928856)
No, it wouldn’t. Case in point: Excel has ability to spread a calculation over multiple lines, but the bugs are not down.

In fact, people who are aware of Alt-enter produce buggier code: they end up writing longer formulas, with fewer intermediate results displayed, and have less visibility of the functioning of their spreadsheets.

Write simpler formulas.

Re:The Tyranny of Spreadsheets by mkl on Friday July 23, 2021 @07:46AM (#27929613)
> Write simpler formulas.

Excel's formula language seems deliberately designed to prevent that.

Re:The Tyranny of Spreadsheets by quietbritishjim on Friday July 23, 2021 @12:17PM (#27932402)
They mean simpler formula per cell, by splitting the formula across multiple cells (as I mentioned in my sibling comment).
Re:The Tyranny of Spreadsheets by chrispsn on Friday July 23, 2021 @04:18AM (#27928429)
A nice shortcut is Ctrl-Shift-U to toggle single- or multi-line view.
Re:The Tyranny of Spreadsheets by FriedrichN on Friday July 23, 2021 @02:57AM (#27927956)
I'll give a free cookie to whoever gets this done.

Sometimes I'll get these spreadsheets with byzantine formulas that I have to copy it to a text editor and format myself to make sense of all the parenthesis.

Re:The Tyranny of Spreadsheets by reportgunner on Saturday July 24, 2021 @08:41AM (#27940415)
It's called "VBA Macros"
Re:The Tyranny of Spreadsheets by pjmlp on Monday July 26, 2021 @03:46AM (#27956766)
Extended with Lambda, PowerQuery and AddOns.
Re:The Tyranny of Spreadsheets by skipnup on Friday July 23, 2021 @02:52AM (#27927933)
But you can just drag the input field larger and add line breaks?
Re:The Tyranny of Spreadsheets by FriedrichN on Friday July 23, 2021 @03:01AM (#27927982)
You could, but when you finally press enter it's all compressed back to one line.
Re:The Tyranny of Spreadsheets by HPsquared on Friday July 23, 2021 @03:52AM (#27928285)
It doesn't get compressed back, I often break complex formulas into multiple lines with indentation.

The only disadvantage is if someone else isn't expecting the formulas to be like this, then gets confused when they can only see the first line.

Re:The Tyranny of Spreadsheets by catherd on Friday July 23, 2021 @04:46AM (#27928578)
Huh. Well, LibreOffice still can't preserve the formatting. Not sure if the last version of real-Excel I tried could.

Having the compose box fit itself to the formula size or give other indication that there is more to see is still a head-scratcher why they didn't do it.

Re:The Tyranny of Spreadsheets by CRConrad on Friday July 23, 2021 @06:30PM (#27936336)
> real-Excel

???

:-(