Format complex cell in OpenOffice?

[Joerg]

Folks,

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?

 

[Joerg]

<http://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_Complex_Number_functions>
<http://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_COMPLEX_function>

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

I haven't tried it but the text seems to indicate that it switches to
scientific notation as needed.

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.

 

[Tim Williams]

Doing complex in *spreadsheets*?

Euuhgh...

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.

Tim

 

[whit3rd]

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?

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?

 

[Joerg]

Doing complex in *spreadsheets*?

Euuhgh...

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.

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

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

Why not do all the ugly calculation stuff on a separate sheet, and
pretty-print it for human eyes on a different one? ...

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

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

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.

 

[Joerg]

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

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

http://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_Complex_Number_functions

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

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

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

 

[Joerg]

Works as expected in Libra Office:
<http://802.11junk.com/jeffl/crud/Complex-LibreOffice.jpg>
including formatting the text. I can't split the result into real and
imaginary to make it look pretty but good enough methinks.

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

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.

 

[Spehro Pefhany]

Doing complex in *spreadsheets*?

Euuhgh...

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.

Tim

Scilab is another free application that could work for you:
http://csserver.evansville.edu/~hwang/f07-courses/ee210/scilab/scilab_complex.html

 

[Joerg]

It's based on OpenOffice.


Argh. It doesn't work. No matter what formatting, including a custom
format, it shows the full complex number. I can change the font size
and style, but nothing else.

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.

Looks like grinding the numbers on a seperate "sheet", splitting the
result between real and imaginary, and displaying it in two seperate
cells, is the only way to format complex numbers.

Yes :-(

 

[Adrian Jansen]

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.

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.

 

[Adrian Jansen]

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.

Correction:

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

 

[Joerg]

Correction:

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

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

 

[josephkk]

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

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 Apache.org.

?-)

 

[Fred Abse]

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

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

Don't you mean:

A -B
B A

??

 

[Jasen Betts]

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

http://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_Complex_Number_functions

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

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.

Right now I am doing a display-only page. But that makes it tough for

others to check and maybe edit my work.

checking spreadsheets is already too tough to rely on.

 

[Jasen Betts]

Methinks LibreOffice is actually OpenOffice.

Mostly, except Oracle doesn't constain libreoffice development.

 

[Jasen Betts]

Don't you mean:

A -B
B A

??

It doesn't make any difference to the result.

 

[Fred Abse]

It doesn't make any difference to the result.

True, but it's conventional the other way. Probably because of the way the
formal derivation has been taught.