6

Based on most popular answer:

Tl;dr: to use the Swift language rules, add <!-- language-all: lang-swift --> to the top of your post, or use ```lang-swift at the beginning of each code block where you desire highlighting.


Highlight.js doesn't support the Excel formula language and I doubt SE would add it to the list of styles to support on the network.

What's a good alternative?

For example this code:

=LAMBDA
(
  vArg,
  LET
  (
    v,    INT(ABS(vArg)),
    ev,   ISEVEN(v),
    half, 1/2,
    high, INT(SQRT(v)+half),
    seqLimit, 2^20,
    IF
    (
      AND(ev, v<>2),
      0, 
      IF
      (
        high>seqLimit,
        -1, 
        LET
        (
          high,         INT(SQRT(v)+1/2),
          seq,          v/SEQUENCE(1,high,1,2),
          iSeq,         INT(seq),
          factors,      seq*(seq=iSeq),
          rFactors,     FILTER(factors,(factors>1)*(factors<v),0),
          sumFactors,   SUM(rFactors)
          --(sumFactors=0) 
        )
      ) 
    )
  )
)

is rendered using the tag based ( or ) or auto recognition syntax highlighting like this:

Rendered code which is not perfect as the --(sumFactors=0) line is rendered like a comment to me which is wrong, and formula names ISEVEN, FILTER etc. are highlighted inconsistently.

Any suggestions of similar supported languages that may look good?

5
  • 1
    This doesn't look like anything Inwould put in an excel cell.
    – Luuklag
    Mar 20 at 21:10
  • 2
    @Luuklag I just found thisLAMBDA function. It suggest to add the formula in a cell to test it before add it to the name manager.
    – Rubén
    Mar 20 at 22:12
  • @Luuklag, it's a new (beta channel) function so you may not have seen it before but it's nothing special, identical syntax to the more vanilla functions. Just been through a formula prettifier, but whitespace doesn't matter to Excel
    – Greedo
    Mar 20 at 23:26
  • @Greedo What tool do you use to format your formulas? Jun 14 at 18:32
  • 1
    @JimGrisham excelformulabeautifier.com
    – Greedo
    Jun 15 at 9:25
6

Try swift

=IF($A1>6,"Hello","Goodbye")

=LAMBDA
(
  vArg,
  LET
  (
    v,    INT(ABS(vArg)),
    ev,   ISEVEN(v),
    half, 1/2,
    high, INT(SQRT(v)+half),
    seqLimit, 2^20,
    IF
    (
      AND(ev, v<>2),
      0, 
      IF
      (
        high>seqLimit,
        -1, 
        LET
        (
          high,         INT(SQRT(v)+1/2),
          seq,          v/SEQUENCE(1,high,1,2),
          iSeq,         INT(seq),
          factors,      seq*(seq=iSeq),
          rFactors,     FILTER(factors,(factors>1)*(factors<v),0),
          sumFactors,   SUM(rFactors),
          --(sumFactors=0) 
        )
      ) 
    )
  )
)

highlight.js actually supports Excel (ref: Supported Languages) but it's not supported by the highlighter version of SE.

I'm not familiar with Excel Lambda functions but it looks that the problem is the formula, not the highlighting. I.E, the following formula taken from another post from Code Review, the highlight is applied correctly there

=IF(OR(ISBLANK(X2),ISBLANK(Y2)),0,X2/VLOOKUP(YEAR(Y2),$EU$3:$FH$57,14,FALSE))*$FH$57

Rendered version of the above formula taken from the source

NOTE: Using the inspect tool, the auto-detect function assigned swift to the above formula and sql to the formula in the question.

I just quickly scanned LAMBDA function. As far I can tell, it looks that the Lambda function in the question is not using the regular spreadsheet formula syntax as there is no separator before --(sumFactors=0). But that is not the unique problem b/c after adding the comma, Excel still shows:

P.S. looking at the source of the formula the OP mentioned that the formula doesn't work.

Related

6
  • Oh yeah, there should be a comma, it's not my code - I got it from here codereview.stackexchange.com/q/257331/146810. But I tried editing the question to add the comma and it still renderers wrong. Besides the inconsistent formula name highlighting is a bigger problem for readability.
    – Greedo
    Mar 20 at 23:20
  • 1
    @Greedo Please consider to add a better formula example
    – Rubén
    Mar 20 at 23:53
  • Unfortunately not imported by SO: dev.sstatic.net/js/highlightjs-loader.en.js does not reference any of the tags for excel/xls/xlsx (point 3 here). As for the formula, it's very new beta channel so may not be available to you, and requires an additional set of parens =LAMBDA(...)(...) to invoke it. But my question with correct syntax is highlighted wrong: [ 1 ], [ 2 ]. I like the example you give, different language tag maybe?
    – Greedo
    Mar 21 at 0:37
  • 1
    Thanks, swift looks good, even though its syntax looks totally different to Excel formulae, strange! I'll leave this unaccepted in case someone finds an even nicer looking highlighting tag
    – Greedo
    Mar 23 at 9:53
  • Thanks -- setting the language as Swift works great in both VSCode and Notepad++ (and likely other editors with Swift support)! Jun 14 at 18:21
  • 2
    Tl;dr: to use the Swift language rules on StackExchange, add "<!-- language-all: lang-swift -->" to the top of your post, or use "``` lang-swift" at the beginning of each code block where you desire highlighting. Jun 14 at 18:29
2

Try using scala

Example:

=LAMBDA
(
  vArg,
  LET
  (
    v,    INT(ABS(vArg)),
    ev,   ISEVEN(v),
    half, 1/2,
    high, INT(SQRT(v)+half),
    seqLimit, 2^20,
    IF
    (
      AND(ev, v<>2),
      0, 
      IF
      (
        high>seqLimit,
        -1, 
        LET
        (
          high,         INT(SQRT(v)+1/2),
          seq,          v/SEQUENCE(1,high,1,2),
          iSeq,         INT(seq),
          factors,      seq*(seq=iSeq),
          rFactors,     FILTER(factors,(factors>1)*(factors<v),0),
          sumFactors,   SUM(rFactors)
          --(sumFactors=0) 
        )
      ) 
    )
  )
)

As far as I can tell, this works well with minified formula.

Example for minified formula:

=LET(hello,"Hello World, ",hello & Sheet1!$A2 & "!")
2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .