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?

--
Regards, Joerg

http://www.analogconsultants.com/

[Jeff Liebermann]

On Wed, 22 May 2013 16:57:19 -0700, Joerg <inv...@invalid.invalid>
wrote:

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

<http://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_Complex_Number_functions>
<http://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_COMPLEX_function>
I haven't tried it but the text seems to indicate that it switches to
scientific notation as needed.

--
Jeff Liebermann je...@cruzio.com
150 Felker St #D http://www.LearnByDestroying.com
Santa Cruz CA 95060 http://802.11junk.com
Skype: JeffLiebermann AE6KS 831-336-2558

[Joerg]

Jeff Liebermann wrote:
> On Wed, 22 May 2013 16:57:19 -0700, Joerg <inv...@invalid.invalid>
> wrote:
>
>> 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?
>
> <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

--
Deep Friar: a very philosophical monk.
Website: http://seventransistorlabs.com

"Joerg" <inv...@invalid.invalid> wrote in message
news:b054b8...@mid.individual.net...

[whit3rd]

On Wednesday, May 22, 2013 4:57:19 PM UTC-7, Joerg wrote:

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

Tim Williams wrote:
> 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.

[Jeff Liebermann]

On Wed, 22 May 2013 17:58:37 -0700, Joerg <inv...@invalid.invalid>
wrote:

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

Perhaps try LibreOffice instead?
<http://www.libreoffice.org>

[Jeff Liebermann]

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.

[Joerg]

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]

Jeff Liebermann wrote:
> 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.

[Jeff Liebermann]

On Thu, 23 May 2013 06:52:56 -0700, Joerg <inv...@invalid.invalid>
wrote:

>Jeff Liebermann wrote:
>> 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.

It's based on 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.

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.

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.

[Spehro Pefhany]

On Wed, 22 May 2013 19:34:40 -0500, "Tim Williams"
<tmor...@charter.net> wrote:

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

Jeff Liebermann wrote:
> On Thu, 23 May 2013 06:52:56 -0700, Joerg <inv...@invalid.invalid>
> wrote:
>
>> Jeff Liebermann wrote:
>>> 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.
>
> It's based on 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.
>
> 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]

On 23/5/2013 10:34 AM, Joerg wrote:

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

--
Regards,

Adrian Jansen adrianjansen at internode dot on dot net
Note reply address is invalid, convert address above to machine form.

[Adrian Jansen]

On 24/5/2013 8:05 AM, Adrian Jansen wrote:
>
> On 23/5/2013 10:34 AM, Joerg wrote:
>
>>
>> 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.
>

Correction:

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

[Joerg]

Adrian Jansen wrote:
>
> On 24/5/2013 8:05 AM, Adrian Jansen wrote:
>>
>> On 23/5/2013 10:34 AM, Joerg wrote:
>>
>>>
>>> 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.
>>
>
> 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]

On Thu, 23 May 2013 06:52:56 -0700, Joerg <inv...@invalid.invalid> wrote:

>

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

?-)

[Jasen Betts]

On 2013-05-23, Joerg <inv...@invalid.invalid> wrote:

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

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

checking spreadsheets is already too tough to rely on.

--
⚂⚃ 100% natural

--- news://freenews.netfront.net/ - complaints: ne...@netfront.net ---

[Jasen Betts]

On 2013-05-23, Joerg <inv...@invalid.invalid> wrote:

> Jeff Liebermann wrote:
>> 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.

Mostly, except Oracle doesn't constain libreoffice development.

[Jasen Betts]

On 2013-05-25, Fred Abse <excret...@invalid.invalid> wrote:

> On Wed, 22 May 2013 17:58:11 -0700, whit3rd wrote:
>
>> 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
>
> ??

It doesn't make any difference to the result.