Re: [libreoffice-users] [CALC] row_only and column_only cell references

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