3 ms·
My best Excel trick, which reveals how little I know, and yet Early [0] doesn't use it (or maybe doesn't need it, but that's hard to believe): 1. You can drag
by mmooss 11mo ago
My best Excel trick, which reveals how little I know, and yet Early [0] doesn't use it (or maybe doesn't need it, but that's hard to believe):
1. You can drag down the bottom of the formula bar/field and make it multi-line
2. You can insert arbitrary[*] newlines in an Excel formula
Combining those, you can turn the absurd default format of single-line-of-code functions into something readable and manageable. Here's a simple one from a spreadsheet I have open:
=INDEX(
$C$17:$S$24,
MATCH(A6,$A$17:$A$24,0),
MATCH(C6,$C$15:$S$15,0)
)
And just think of highly nested functions. Once you know it, writing single-line functions of any complexity is absurd, as absurd as writing 'real' code that way.
[0] Early shows how it was done: https://news.ycombinator.com/item?id=46340638 https://news.ycombinator.com/item?id=46340638
[*] I think you can do it anywhere but I haven't tested anything crazy; mostly I just use them between expressions.
- Terr_ 11mo ago> You can drag down the bottom of the formula bar/field and make it multi-line For folks on LibreOffice (currently v24.2): * There's an downward-pointing "expand" triangle to the far-right of the formula input line. * That button toggles the formula input area between 1-line vs 6-lines with scrolling. * Newlines can inserted by shift-enter. * If there are additional formula lines lines outside the viewable line(s), then a dashed line on the relevant border will be shown. (Plus the regular scrollbar, in expanded mode.)
- mmooss 11mo agoTerr_'s comment reminds me and I'm too late to edit the parent: In Excel's formula bar/field, insert newlines by pressing Alt+Enter.
- simonh 11mo agoIt could be that in a competitive context fussing with formatting would cost precious seconds. Great general tip for us mortals though.
- mmooss 11mo agoImagine a coding competition: Would you forgo newlines and write everything on one long line? Would that save time? Perfect style guide format does consume time, but pressing Alt+Enter a few times would seem to reduce errors at essentially no cost.
- Dumblydorr 11mo agoNo need to drag the bottom of the cell to expand function down. Just double click the bottom of the function cell, it’ll expand down automatically.
- orev 11mo agoThey’re referring to the formula bar at the top of the screen (under the ribbon), not the cell itself (which are located within the grid).
- stevesimmons 11mo agoYou can also use the =LET(...) formula to define named variables: =LET( filterCriteria, "Fred", filteredRange, FILTER(A2:D8,A2:A8=filterCriteria), IF(ISBLANK(filteredRange),"-", filteredRange) ) There must be an odd number 2D + 1 of arguments. The first 2D are D name-expression pairs and the final one is the expression whose value is returned. The end result - as you see - is quite readable.
- 303uru 11mo agoOh yes indeed. For example, here's something I was just working on: =LET( h, ROWS(A2#), names, A2#, vals, K2:INDEX(K:K, h+1), denoms, J2:INDEX(J:J, h+1), k, 20, groupAvg, SUMPRODUCT(vals, denoms) / SUM(denoms), adj, (denoms/(denoms + k))*vals + (k/(denoms + k))groupAvg, inc, (names <> "") \* ISNUMBER(vals), namesF, FILTER(names, inc), valsF, FILTER(vals, inc), denomsF, FILTER(denoms, inc), adjF, FILTER(adj, inc), r, ROWS(namesF), nShow, MIN(10, r), sorted, SORTBY(HSTACK(namesF, valsF, denomsF), adjF, -1), TAKE(sorted, nShow) )
- mmooss 11mo agofyi: if you indent a couple of spaces, you get a <code> block.