5 ms·

If the functional programming language requires that each expression must be exactly one line with no comments, I'll pass thanks. Long Excel formulas make Rege

by Pxtl 6y ago

If the functional programming language requires that each expression must be exactly one line with no comments, I'll pass thanks. Long Excel formulas make Regex look like poetry.

xiaq 6y ago
If you have a long expression, you can break it into multiple ones by storing some intermediate results in another cell. You can also hide such cells or put them in another sheet. This might sound like a hack, but being able to see intermediate results makes debugging complex expressions easier too, so in a sense it is working as intended.
- darkerside 6y ago
  3D Excel would be intriguing. A dimension on which to store intermediate calculations.
  - phonon 6y ago
    Sounds like https://en.wikipedia.org/wiki/Lotus_Improv https://en.wikipedia.org/wiki/Lotus_Improv
  - davchana 6y ago
    I would love just getting the ability to resize the Evaluate Formula dialogue box text area.
- DavidPeiffer 6y ago
  Formula -> Trace Dependencies/Precedents is also super handy! I have a short VBA loop that applies it to all selected cells. It makes it very easy to visually spot one cell reference that's wrong, because 20 cells connect to corresponding cells with parallel lines, then one isn't parallel. Ctrl+Tilda toggles show/hide formulas, which helps identify input cells when you inherit a messy spreadsheet.
rtsil 6y ago
The most painful thing for me, but not to ordinary users, is that the formulas are localized in the user's language.
- rwoerz 6y ago
  +1 The devil has many names. VLOOKUP and SVERWEIS (German) are two of them.
- Seanambers 6y ago
  This is a big one, especially if your edition isn't English/US. Also the number formats and locale settings can cause quite a bit of trouble, if its not English/US. Alot of data warehouse setups output files with U.S formats even in Europe.
freeone3000 6y ago
You can put the comments in any of the plethora of cells you're not using, or in a Note or a Comment.
DavidPeiffer 6y ago
On longer formulas, I do Alt+Enter while editing the formula to get a new line, then 4 spaces for an indentation. Just like that, you can keep track of start/end parenthesis. It's not perfect, I wish it would automatically format that way, but it certainly makes maintenance easier.
pony_sheared 6y ago
The new Let() function gives you multi row formulas, with local variables. https://insider.office.com/en-us/blog/let-names-in-formulas-for-excel https://insider.office.com/en-us/blog/let-names-in-formulas-... And it's a hack, but you can put text comments inside an N() function and add them into your formulas. https://support.microsoft.com/en-us/office/n-function-a624cad1-3635-4208-b54a-29733d1278c9 https://support.microsoft.com/en-us/office/n-function-a624ca...

by Pxtl 6y ago

If the functional programming language requires that each expression must be exactly one line with no comments, I'll pass thanks. Long Excel formulas make Regex look like poetry.

xiaq 6y ago

If you have a long expression, you can break it into multiple ones by storing some intermediate results in another cell. You can also hide such cells or put them in another sheet. This might sound like a hack, but being able to see intermediate results makes debugging complex expressions easier too, so in a sense it is working as intended.

darkerside 6y ago
3D Excel would be intriguing. A dimension on which to store intermediate calculations.
- phonon 6y ago
  Sounds like https://en.wikipedia.org/wiki/Lotus_Improv https://en.wikipedia.org/wiki/Lotus_Improv
- davchana 6y ago
  I would love just getting the ability to resize the Evaluate Formula dialogue box text area.
DavidPeiffer 6y ago
Formula -> Trace Dependencies/Precedents is also super handy! I have a short VBA loop that applies it to all selected cells. It makes it very easy to visually spot one cell reference that's wrong, because 20 cells connect to corresponding cells with parallel lines, then one isn't parallel. Ctrl+Tilda toggles show/hide formulas, which helps identify input cells when you inherit a messy spreadsheet.

rtsil 6y ago

The most painful thing for me, but not to ordinary users, is that the formulas are localized in the user's language.

rwoerz 6y ago
+1 The devil has many names. VLOOKUP and SVERWEIS (German) are two of them.
Seanambers 6y ago
This is a big one, especially if your edition isn't English/US. Also the number formats and locale settings can cause quite a bit of trouble, if its not English/US. Alot of data warehouse setups output files with U.S formats even in Europe.

freeone3000 6y ago

You can put the comments in any of the plethora of cells you're not using, or in a Note or a Comment.

DavidPeiffer 6y ago

On longer formulas, I do Alt+Enter while editing the formula to get a new line, then 4 spaces for an indentation. Just like that, you can keep track of start/end parenthesis. It's not perfect, I wish it would automatically format that way, but it certainly makes maintenance easier.

pony_sheared 6y ago

The new Let() function gives you multi row formulas, with local variables. https://insider.office.com/en-us/blog/let-names-in-formulas-for-excel https://insider.office.com/en-us/blog/let-names-in-formulas-... And it's a hack, but you can put text comments inside an N() function and add them into your formulas. https://support.microsoft.com/en-us/office/n-function-a624cad1-3635-4208-b54a-29733d1278c9 https://support.microsoft.com/en-us/office/n-function-a624ca...