alt Hacker NewsMy 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)
)
[0] Early shows how it was done: 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.
Replies
You can also use the =LET(...) formula to define named variables:
=LET(
filterCriteria, "Fred",
filteredRange, FILTER(A2:D8,A2:A8=filterCriteria),
IF(ISBLANK(filteredRange),"-", filteredRange)
)
The end result - as you see - is quite readable.
It could be that in a competitive context fussing with formatting would cost precious seconds. Great general tip for us mortals though.
Terr_'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.
Oh 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 + 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)
No 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.
> 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.)