new | ask | show | jobs
mmooss 2 days 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

[*] I think you can do it anywhere but I haven't tested anything crazy; mostly I just use them between expressions.

Terr_ a day 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.)

simonh a day ago  [ - ]

It could be that in a competitive context fussing with formatting would cost precious seconds. Great general tip for us mortals though.

mmooss 19 hours ago  [ - ]

Imagine 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.

mmooss a day ago  [ - ]

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.

stevesimmons 19 hours ago  [ - ]

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)
    )
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.

Dumblydorr a day ago  [ - ]

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.

orev 21 hours ago  [ - ]

They’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).

303uru 18 hours ago  [ - ]

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 +
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 16 hours ago  [ - ]

fyi: if you indent a couple of spaces, you get a <code> block.