Connect with us

Format complex cell in OpenOffice?

Discussion in 'Electronic Design' started by Joerg, May 23, 2013.

Scroll to continue with content
  1. Joerg

    Joerg Guest


    Unfortunately spreadsheet programs store complex numbers as text,
    meaning one can't truncate to a reasonable length and so the cells all
    overflow. For Excel there is some lengthy Basic script to fix this but
    not for OO.

    Anybody know a trick how to get them into scientific notation with, say,
    four decimal points plus the exponent, for the real and for the
    imaginary part?
  2. Joerg

    Joerg Guest

    That's the function I am using but it will not react to any cell
    formatting attempt.

    When I revisit the cell after a formatting attempt it says that it is
    scientific but in reality it is not. I still see -7582.6508947528j in
    there just like before and this spills over into the neighbor cell.

    Thing is, I can probably fix this in Excel but these days many people
    aren't using that anymore and I need to keep things compatible. Which is
    a real pain because OpenOffice can't do a lot of the things, for example
    there's no VBA.
  3. Tim Williams

    Tim Williams Guest

    Doing complex in *spreadsheets*?


    If you "simply cannot" do it in any other scientific scripting language,
    say MATLAB/Octave, or just write it out in any programming or scripting
    language, like BASIC (or, say, VBScript), Java, Python..), then...

    Why not do all the ugly calculation stuff on a separate sheet, and
    pretty-print it for human eyes on a different one? I'd suggest REAL(x)
    and IMAG(x), in adjacent cells, with your desired formatting, including --
    which I note you didn't press your luck to ask :) -- powers-of-10^3
    engineering notation, which was more-or-less solved recently I believe.

  4. whit3rd

    whit3rd Guest

    Well, I've considered using the matrix formulation for complex numbers, i.e.

    Z = A + Bj = ( A B )
    (-B A )

    and doing add/subtract through matrix addition, multiply with matrix multiplication,
    and division by matrix inversion followed by multiplication.
    That way, you just have the result cells in floating point (and use
    the usual display-as-fixed to get a fixed decimal point representation).

    Can't you just extract the real and imaginary parts, to a pair of display-only cells?
  5. Joerg

    Joerg Guest

    I know engineers who successfully and efficiently simulate large chunks
    of engines using nothing but Excel and VBA. The output nearly has
    scientific publication quality.

    I have Mathcad but the problem is nobody else in the group does. Same
    with the others. And I am not a programmer.

    That is the ugly path which I am taking right now, in order to get on
    with the calcs. But it isn't the ultimate cat's meouw because nobody can
    then edit in the "pretty sheet".

    For power of 03 there is a bug in my OO version, it won't do that
    either. Maybe a new version would but that doesn't bother me much.
  6. Joerg

    Joerg Guest

    Could be done that way. But why does OO have all these nice functions to
    handle all sorts of complex number math, down to calculating the
    hyperbolic secant and whatnot ...

    .... and then can't do the rather simple thing of formatting the cell?

    Right now I am doing a display-only page. But that makes it tough for
    others to check and maybe edit my work.
  7. Joerg

    Joerg Guest

    Methinks LibreOffice is actually OpenOffice. If I key in a number like
    you did it comes out the same way. But if the numbers inside the
    brackets are actually results from other cells with full length it blows

    You can do the test: Key in "=COMPLEX(123.123456,-456.789;"j") and then
    format cell A1 to only display two positions after the decimal point. Or
    scientific, or another style.
  8. Scilab is another free application that could work for you:
  9. Joerg

    Joerg Guest

    Yup. Which means it is fairly useless. I have no idea why the team put
    so much effort into all the complex math libraries, considering that
    results cannot be displayed in a consistently clean fashion.

    Yes :-(
  10. While there is no VBA in OO, VBA macros written in Excel will run on an
    OO spreadsheet, a bit slow, but they do work, at least in OO version 3.
  11. Correction:

    Actually I meant Libre Office 3.3, not the older Open Office.
  12. Joerg

    Joerg Guest

    Thanks, Adrian. Maybe it's time to update then, VBA is very useful.
  13. josephkk

    josephkk Guest

    Mighty close, it is a fork in the development. IT looks like it may
    re-merge someday soon as well, since OO got moved over to

  14. Fred Abse

    Fred Abse Guest

    Don't you mean:

    A -B
    B A

  15. Jasen Betts

    Jasen Betts Guest

    becuause spreadsheet cells can't hold complex numbers, only floats,
    expresssions, and text.

    and the text formatting isn't smart enough to convert complex-number-text to
    fit cell width.
    checking spreadsheets is already too tough to rely on.
  16. Jasen Betts

    Jasen Betts Guest

    Mostly, except Oracle doesn't constain libreoffice development.
  17. Jasen Betts

    Jasen Betts Guest

    It doesn't make any difference to the result.
  18. Fred Abse

    Fred Abse Guest

    True, but it's conventional the other way. Probably because of the way the
    formal derivation has been taught.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Electronics Point Logo
Continue to site
Quote of the day