Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index


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 <andrew@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+unsubscribe@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+unsubscribe@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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.