[CALC] row_only and column_only cell references

users
#1

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.

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.

#2

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.

#3

Hi :slight_smile:  
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 :slight_smile:

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.

#4

Hi :slight_smile:  
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

The problem with this setting is that there is no explanation of what
it really means / implies (other than "=$Sheet2.C4" vs.
"=Sheet2!C4").

The difference between "R1C1" and "A1" is likely clear enough for
anyone that might want / need to use it. But what are the differences
between "Calc A1" and "Excel A1"? Are "row_only" references accepted
when setting it to "Excel A1"?

Even if I try and test the possible difference regarding "1:1" and
"A:A" references, I don't know what else could change. Without
documentation, I can't know of pros and cons, or compatibility or any
other implications of this particular setting.

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 :slight_smile:

FWIW, I should point out that Excel is no the only one accepting this
type of reference.

I'm not saying that Calc should promote "bad practices" or anything
like it. I'm not saying that using row_only references is better in
any way. I am not promoting its use.

But when receiving a file from other sources and it errors, it is not
always easy to track down the problem or the incompatibility. And
even then, it is not always possible for one user to edit and share
the file with others.

Additionally, if a user of Calc searches for some method or formula
to achieve something he doesn't know how to do it by himself, it is
common to find such solution expressed in the context of other
spreadsheet software (typically, Excel).

From a final user's perspective, I don't see a reason not to support

it. Of course there might be technical / development reasons, but
this is a *users* mailing list :).

So, I left with several questions here. Hopefully, someone has some
answer(s) or can point into the right direction?

TIA,
Ady.

#5

> 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

Quoting from "CG4107-FormulasAndFunctions.pdf" :
"Calc can not reference a whole column of unspecified length using
which you might be familiar with in other spreadsheet programs."

This piece of info was not so easy to find. Since other spreadsheet
software (in fact, more than one) indeed supports "A:A: and "1:1"
references, I thought that either Calc would support it too, or at
least would be easier to find that it doesn't.

For example, using Calc to open some xls file with such type of
references would end up with errors. The xls file itself would open,
but there would be no hint about the reason for the "#ref" or "#name"
errors (among the possible errors that this type of cell reference
would end up with).

Regarding a workaround, once the user finds out that the problem is
in the type of reference (which is not always evident), then by
adding some column (or some row) to the reference would probably
avoid the error. Other methods (e.g offset, range names) would be
more close to the original range (complete row / column). This
workaround is of course not always available, specially when
receiving a spreadsheet file from someone else (originally prepared
and saved with other programs).

I am still wondering if some setting in Calc would (or should) allow
for this type of cell reference. For example, by using "Excel A1"
instead of "Calc A1" in Calc -> Tools -> Options -> LibreOffice Calc
-> Formula -> Formula syntax.

There are many different settings that might influence the accepted
behavior, and I really don't know / understand the effect of each one
(I didn't find any explanation of what each setting really means), so
instead of "playing" with each possible combination, I decided to ask
here in the ML (and not all of those settings are located in the same
place).

If this type of cell reference is _completely_ unsupported, perhaps
it should be mentioned in the help files (?). I would agree that
referencing complete columns or complete rows should be rarely used,
and discouraged, but perhaps supporting a formula that already uses
it (specially when using or receiving files originally saved with
other file formats) is not such a crazy idea.

So, is there any combination of settings that would help in this type
of cases?

[off-topic]
BTW, if I may... Having separated mailing lists according to the
program (Writer / Calc / Impress / ...) might be more adequate for
users, specially since other methods of communication with
LibreOffice / TDF are not really
effectively working.
[/off-topic]

TIA,
Ady.