top | item 31386351

(no title)

As I consultant that works extensively in Excel, I understand your point. The lack of decent syntax highlighting, auto-indentation or “IDE mode” instead of the formula bar can be painful. Especially when working with models developed by others.

On the other hand, you can see it as a feature. It forces you to keep your formulas short, break them down in helper columns and overall make your logic cleaner. If you are writing a 4 line long formula, you are doing something wrong. Sytax highlighting would still surely help, but it is not a replacement for clean modelling.

discuss

2b3a51|3 years ago

Amateur user of Excel/Calc/Google sheets here. My worst is...

    =CONCATENATE(
        IF(A1<0;"-";"+");
        TEXT(INT(ABS(A1));"00") ;"d";   
        IF((ABS(A1) - INT(ABS(A1)) - INT( (ABS(A1) - INT(ABS(A1)))*60 )/60) * 3600 > 59.5;
            TEXT(INT((ABS(A1) - INT(ABS(A1)))*60)+1;"00");
            TEXT(INT((ABS(A1) - INT(ABS(A1)))*60);"00")
        );"m";
        IF((ABS(A1) - INT(ABS(A1)) - INT( (ABS(A1) - INT(ABS(A1)))*60 )/60) *   3600 > 59.5;
    TEXT(0;"00");
            TEXT(ROUND(    (ABS(A1)-INT(ABS(A1))-INT((ABS(A1)-  INT(ABS(A1)))*60)/60)*3600;0    );"00")
        );"s" 
    )

...which should take a value in cell A1 that is in the range [0,360) and return the value in angle notation (degrees, minutes and seconds). I especially wanted this function all in one cell to save loads of helper cells (the spreadsheet calculates the position of the moon, sun, planets and a physical ephemeris for each planet so a lot of angles to display).

It is a pain to apply, I use a text editor to search and replace the cell reference for each angle. I'd settle for the formula bar display of the formula not reformatting the line breaks.

sdeframond|3 years ago

In Excel you could use =LET(a; A1; ...) to define a variable 'a' local to the formula. You would have to change A1 in one place only.

I'm not sure this works with LibreOffice though. Probably not.

NamTaf|3 years ago

I feel you can do something with floor and trunc to get the remainder, then using round to get the sig figs correct. You can make it abs of it at the start, and then only add a negative at the start of the concat if it's negative.

I rapidly tried and I get something a bit shorter (about half the length), but my rounding ends up with imperfect precision and sometimes I get 10m60s rather than 11m0s due to it. Appropriate rounding at the appropriate points would fix that.

ReactNative22|3 years ago

You could put that into a VBA formula, although it wouldn't be as fast unfortunately (as I learned when I tried simplifying excel formulae - which are _compiled_ whereas VBA I think is interpreted? - in any case, excel was like 50x faster for me).

I assume you have tried the '$' signs to see if they will help?

wintermutestwin|3 years ago

>I'd settle for the formula bar display of the formula not reformatting the line breaks.

I can't imagine how much time this one thing would have saved over my last 40 years of spreadsheeting.

lttlrck|3 years ago

greggsy|3 years ago

Wow that’s actually kind of cool.

snthpy|3 years ago

In the old Excel with only cell grid references I would agree. However in "new Excel" with LET expressions and LAMBDA it is now possible to write legible and self-documenting code where the lack of decent editing support is becoming a problem.

grvdrm|3 years ago

Wish I could +1 this 100x. Break into small clean chunks that are easy to follow.

unknown|3 years ago

[deleted]