On 10/12/2013 07:55 AM, Ady wrote:
Hello,
I've been having problems with certain type of *references* in Calc.
For example, the formula "sum(1:2)" works correctly in other
spreadsheet software, but it errors in Calc.
I was not aware that you could do that in any product. It is not
supported in Calc of which I am aware. Villeroy has a messy solution
(see post 6). Well, you probably should read all the posts...
http://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=9527
So, the ony soltions I have seen based on a quick search is to either
define a range, or to use offset.
The most problematic cases are related to functions such as
"indirect()", "address()" and the like. For example, the following
formula "should" tell whether a number in "A1" is prime or not:
=SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT("1:"&INT(A1^0.5))))=0))=1
but Calc doesn't seem to accept the type of reference
("ini_row:fin_row"). Instead I have to add a "column" to the
reference, as in:
=SUMPRODUCT(1*(MOD(A1,ROW(INDIRECT("a1:a"&INT(A1^0.5))))=0))=1
to make it work in Calc.
There are several settings that affect the way Calc parses and shows
some type of cell references, so I wonder whether there is some
setting that I should change for Calc to be able to accept this type
of references ("ini_row:fin_row"), or whether this is some kind of
bug or lack of a feature.
A similar issue happens with column-only references, such as
"sum(A:B)".
To be clear, I'm not saying that row-only (or column-only) references
are better in any way. I'm just trying to make them work as in (a.k.a
"compatible with") other spreadsheet software.
If there is a better place (mailing list, irc, forum,...) to ask this
question, please let me know.
TIA,
Ady.
--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
Info: http://www.pitonyak.org/oo.php
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted