Eike Rathke wrote:
On Friday, 2008-01-11 15:41:17 -0500, Joe Smith wrote:
I recently came across this formula in a tip for Excel[1]:
=2^(ROW(INDIRECT("1:8")))
which (in an array context) is a convenient, if ugly, way of generating
2^{1;2;3;4;5;6;7;8} --> (2, 4, 8, ... 256)
Without having to write it all out.
[...]
[1] It works in Calc if you use "A1:A8".
It doesn't need the INDIRECT call, you can write =2^ROW(A1:A8) in
array context. Same with COLUMN(
The use of INDIRECT prevents unintended changes to the formula if the
A1:A8 range is moved (or rows inserted, etc). The downside of using
INDIRECT is that the formula has to be recalculated after any change to
the document. If you don't need a dynamic upper limit, you could use a
named range, like "onetoeight" for "ROW($A$1:$A$8)" (note the absence of
a sheet reference), which causes recalculation only for changes in
A1:A8, and is also somewhat more readable. Still just a dirty trick, of
course.
Is it worth thinking about extending the array constant syntax to allow a
simple series to be written clearly and easily? Something like:
{1..8} --> (1, 2, 3, 4, 5, 6, 7, 8)
Not really. It would add just a small benefit serving a very specialized
purpose, would complicate parsing, and no other application supports it.
I'd say we got more important things to do.
A new syntax isn't needed, a new function would be enough. For now,
anyone can create one for himself with an add-in (you could also use a
Basic function, but these days, with the NetBeans wizard, an add-in in
Java is written just as easily, and it's more flexible).
Niklas
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
