On Wed, Jun 6, 2012 at 10:35 PM, Shan Zhu <[email protected]> wrote: > Getting 9 as a result, it causes by incorrect parameter format in your > formula. > The complex number should be put in a pair of double quotation marks, like > this:=IMPRODUCT("3+4i";"3-4i") > The formula =IMPRODUCT(3+i4,3-i4) will be regarded as (3+ (value in cell > i4))*(3-(value in cell i4)), so it returns 9. > The formula =IMPRODUCT(2+i2,0.5-i.5), i.5 is not a valid cell reference, so > #REF returned. >
Complex number support in spreadsheets is an afterthought. It doesn't work very well. This is not just AOO, but all spreadsheets. (Or does Gnumeric do something better? They seem to have more of a scientific/engineering focus.) Much of the disconnect comes from ambiguities with cell references and range names. i4 -- is that cell i4? a range name called i4? Or 4 * sqrt(-1)? At the file format level we have ways of making this clear. But at the UI level it is still a mess, and we basically have two choices: 1) Do it the way Microsoft Excel does the UI, so user's can reuse what they already know about spreadsheets when they move to Calc or 2) So something new and consistent, where complex numbers are first class citizens, where booleans are not just integers, where dates obey leap year rules and are not just numbers reformatted, where "1%%" is an error and not just treated as 1% of 1%, where numeric calculations follow IEEE rules related to overflow, underflow and NaN, where blanks and missing values are treated consistently and not just sometimes treated as zeros, etc. There are a lot of ways in which spreadsheets formulas could be made more logical and consistent. But so far, the needs of legacy compatibility has been the predominant force influencing how formulas in Calc (and in ODF) work. -Rob > Regards, Shan Zhu > > > 2012/6/7 Dan Lewis <[email protected]> > >> Easton, William wrote: >> >>> I am having difficulties trying to do complex number calculations. >>> >>> First, it is a real pain to have to use functions for ordinary >>> operations. I would like to propose a simple switch that might pop up if a >>> complex number were detected substituting the complex versions of *,/,+,- >>> for the real counterparts. Even more radical would be to do the same for >>> functions, but one might need a compatibility mode. >>> >>> However, I am finding that things like improduct(1+2j;3-4j) do not work. >>> >>> Advice? Thoughts? >>> >>> Thank you. >>> >>> Jim Easton >>> 858-527-0240 >>> >> I would not trust IMPRODUCT() to do calculations: they are not >> correct. >> For example, I made this calculation: =IMPRODUCT(3+i4,3-i4). The answer >> given was 9. But this is just the product of the real numbers. The correct >> answer is 25 (3*3 +3*i4 -3*i4 +4*4). >> I also discovered that it may not work with fractions: >> =IMPRODUCT(2+i2,0.5-i.5). Calc gives an error: #REF. >> >> --Dan >> >> >> ------------------------------**------------------------------**--------- >> To unsubscribe, e-mail: >> ooo-users-unsubscribe@**incubator.apache.org<[email protected]> >> For additional commands, e-mail: >> ooo-users-help@incubator.**apache.org<[email protected]> >> >> --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
