Hi :) Is it possible to change the way Calc handles it's formulas by doing
Tools - Options - Calc - Formula and then use the drop-down to make them use MS Excel ways. I can't help thinking that trying to copy MS Excel at a time when they have finally admitted, and corrected, some very fundamental errors in their formulas Regards from Tom :) On Saturday, 12 October 2013, 14:20, Andrew Douglas Pitonyak <and...@pitonyak.org> wrote: 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 -- 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
