You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by Gary Aitken <ah...@dreamchaser.org> on 2015/02/16 06:20:47 UTC
calc reference relative to named range
Hi all,
I can't find how to do this in the docs; not even sure it's possible but
seems like it should be.
Assume column A is named "foo" (select column, insert/name/define)
In column B I can use the corresponding element in the same row in A in a
formula by using its name, e.g.
=foo*3
Is there a way to reference a cell in column "foo" (A) relative to the
row in which the formula is being defined?
What I want to do is subtract the previous row value from the current row one:
=foo - [foo-1]
How do I say that?
Thanks,
Gary
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: calc reference relative to named range
Posted by Gary Aitken <op...@dreamchaser.org>.
On 02/16/15 00:35, Brian Barker wrote:
> At 22:20 15/02/2015 -0700, Gary Aitken wrote:
>> I can't find how to do this in the docs; not even sure it's
>> possible but seems like it should be. Assume column A is named
>> "foo" (select column, insert/name/define). In column B I can use
>> the corresponding element in the same row in A in a formula by
>> using its name, e.g. =foo*3
>>
>> Is there a way to reference a cell in column "foo" (A) relative to
>> the row in which the formula is being defined? What I want to do is
>> subtract the previous row value from the current row one: =foo -
>> [foo-1] How do I say that?
>
> =foo-INDEX(foo;ROW()-1)
Thanks; that was what I needed.
> Mind you, I'm not at all sure why you would want to do this. Column A
> already has a perfectly serviceable name: "A"! Why not have in B2
> just =A2-A1 and so on?
Because formulas are much more meaningful when the names used in them
have some link to the reality they represent.
NetUse = NetDeliveredToUs - NetFedToGrid
makes much more sense than "G2 = E2 - F2"
In the example above,
NetDeliveredToUs = KWHDeliveredToUs - INDEX(KWHDeliveredToUS;ROW()-1)
Gary
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: calc reference relative to named range
Posted by Brian Barker <b....@btinternet.com>.
At 22:20 15/02/2015 -0700, Gary Aitken wrote:
>I can't find how to do this in the docs; not even sure it's possible
>but seems like it should be. Assume column A is named "foo" (select
>column, insert/name/define). In column B I can use the corresponding
>element in the same row in A in a formula by using its name, e.g.
> =foo*3
>
>Is there a way to reference a cell in column "foo" (A) relative to
>the row in which the formula is being defined? What I want to do is
>subtract the previous row value from the current row one:
> =foo - [foo-1]
>How do I say that?
=foo-INDEX(foo;ROW()-1)
Mind you, I'm not at all sure why you would want to do this. Column A
already has a perfectly serviceable name: "A"! Why not have in B2 just
=A2-A1
and so on?
Named ranges have their uses, such as referring to a table of values
elsewhere in a document, but I don't think this is one. If you do
want your "foo" column elsewhere, you could construct a parallel
column of differences there too and then name that range instead (or as well).
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: calc reference relative to named range
Posted by Gary Aitken <op...@dreamchaser.org>.
On 02/16/15 02:46, Josiane (telenet) wrote:
> I think I know why you want to name a column. Do you know that there
> is a handling that you can do to have an entire column do a formula?
> If you make a formula in C1. For example =B1-A1 Then you can make
> sure that this formula is made in the entire column without typing it
> in every cell. There is a point right below the cell (if it is
> selected) (see the attachment). If you set the cursor on that point,
> then you can pull this below. And all the cells below C1 will get
> this formula. If you do that then you will not need to name a
> column.
Thanks, I'm aware of that.
As stated below, the purpose in naming the column is not to allow some
operation that is not otherwise possible; it's to make the entire
spreadsheet more manageable, particularly over time, when one has
difficulty remembering what a formula actually means.
> Op Mon, 16 Feb 2015 08:35:34 +0100 schreef Brian Barker
> <b....@btinternet.com>:
>
>> At 22:20 15/02/2015 -0700, Gary Aitken wrote:
>>> I can't find how to do this in the docs; not even sure it's
>>> possible but seems like it should be. Assume column A is named
>>> "foo" (select column, insert/name/define). In column B I can use
>>> the corresponding element in the same row in A in a formula by
>>> using its name, e.g. =foo*3
>>>
>>> Is there a way to reference a cell in column "foo" (A) relative
>>> to the row in which the formula is being defined? What I want to
>>> do is subtract the previous row value from the current row one:
>>> =foo - [foo-1] How do I say that?
>>
>> =foo-INDEX(foo;ROW()-1)
>>
>> Mind you, I'm not at all sure why you would want to do this. Column
>> A already has a perfectly serviceable name: "A"! Why not have in B2
>> just =A2-A1 and so on?
>>
>> Named ranges have their uses, such as referring to a table of
>> values elsewhere in a document, but I don't think this is one. If
>> you do want your "foo" column elsewhere, you could construct a
>> parallel column of differences there too and then name that range
>> instead (or as well).
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: calc reference relative to named range
Posted by "Josiane (telenet)" <jo...@telenet.be>.
Hello
I think I know why you want to name a column.
Do you know that there is a handling that you can do to have an entire
column do a formula?
If you make a formula in C1. For example =B1-A1
Then you can make sure that this formula is made in the entire column
without typing it in every cell.
There is a point right below the cell (if it is selected) (see the
attachment). If you set the cursor on that point, then you can pull this
below.
And all the cells below C1 will get this formula.
If you do that then you will not need to name a column.
Does this solve youre problem?
Regards
Josiane Claesen
Op Mon, 16 Feb 2015 08:35:34 +0100 schreef Brian Barker
<b....@btinternet.com>:
> At 22:20 15/02/2015 -0700, Gary Aitken wrote:
>> I can't find how to do this in the docs; not even sure it's possible
>> but seems like it should be. Assume column A is named "foo" (select
>> column, insert/name/define). In column B I can use the corresponding
>> element in the same row in A in a formula by using its name, e.g.
>> =foo*3
>>
>> Is there a way to reference a cell in column "foo" (A) relative to the
>> row in which the formula is being defined? What I want to do is
>> subtract the previous row value from the current row one:
>> =foo - [foo-1]
>> How do I say that?
>
> =foo-INDEX(foo;ROW()-1)
>
> Mind you, I'm not at all sure why you would want to do this. Column A
> already has a perfectly serviceable name: "A"! Why not have in B2 just
> =A2-A1
> and so on?
>
> Named ranges have their uses, such as referring to a table of values
> elsewhere in a document, but I don't think this is one. If you do want
> your "foo" column elsewhere, you could construct a parallel column of
> differences there too and then name that range instead (or as well).
>
> I trust this helps.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org