Labelling a row so it can be used as an offset for sum, difference or range

[Martin Underwood]

I'm keeping a spreadsheet of my usage of electicity, gas and water by taking
meter readings every week or so. The table gradually acquires more rows over
time, as I take successive reading.

I display the total usage to date as a series of values at the bottom of
each column:

1 A B C D
3 Date Elec Gas Wat
4 1 Jan 05 123 345 34
5 1 Feb 05 127 350 35
6 15 Feb 05 138 355 39
7
8 Totals 15 10 9


Where B8 is B6-B4, C8 is C6-C4 etc.


Each time I add a new row (the next one would be below row 6) I need to
modify the formulae: for example B9 (which was B8 until I inserted the row)
needs to have its formula changed from B6-B4 to B7-B4 to include the row
I've just added. This is a tedious process.

I can label a cell but then I'd like to be able to use the row of that
labelled cell as a limit in a formula? I'd like to be able to label B8 as
"electricity_total" so I can set its formula to
"(col(electricity_total),row(electricity_total)-2)-B4" such that the
row/column of the lowest cell are calculated rather than being literals.
This way, every time I insert a row, I always include all rows up to the
n-2'th row where n is the row of the cell that contains the formula.


Similarly, it would be nice if I could do the same thing in the Source Data
fields of an X-Y graph (X=date, Y=daily usage) so I don't need to make
corresponding changes as I add more rows (eg changing "=$A$7:$A$100" to
"=$A$7:$A$101"). Given that I'm plotting three lines (electicity, gas,
water) and each has two values (X and Y) that's 6 ranges that I need to
change every time I add a new row.


I'm sure this must be possible, but I have the usual problem with online
help: trying to find a suitable phrase to search for in the index!

Defining the name for the cell is the easy bit (Insert | Names | Define) -
it's using that name in row() function and referring to a cell whose row
and column are calculated not literals that has got me baffled.

 

[Jason Morin]

Copy this into B8 and fill across to D8:

=INDEX(B$4:INDIRECT("R[-1]C",0),COUNTA(A:A)-2)-B$4

HTH
Jason
Atlanta, GA

 

[Jason Morin]

Change

COUNTA(A:A)

to

COUNTA($A:$A)

in the formula.

Jason

-----Original Message-----
Copy this into B8 and fill across to D8:

=INDEX(B$4:INDIRECT("R[-1]C",0),COUNTA(A:A)-2)-B$4

HTH
Jason
Atlanta, GA

-----Original Message-----
I'm keeping a spreadsheet of my usage of electicity,

gas
and water by taking

meter readings every week or so. The table gradually acquires more rows over
time, as I take successive reading.

I display the total usage to date as a series of values at the bottom of
each column:

1 A B C D
3 Date Elec Gas Wat
4 1 Jan 05 123 345 34
5 1 Feb 05 127 350 35
6 15 Feb 05 138 355 39
7
8 Totals 15 10 9


Where B8 is B6-B4, C8 is C6-C4 etc.


Each time I add a new row (the next one would be below row 6) I need to
modify the formulae: for example B9 (which was B8 until I inserted the row)
needs to have its formula changed from B6-B4 to B7-B4

to
include the row

I've just added. This is a tedious process.

I can label a cell but then I'd like to be able to use the row of that
labelled cell as a limit in a formula? I'd like to be able to label B8 as
"electricity_total" so I can set its formula to
"(col(electricity_total),row(electricity_total)-2)-B4" such that the
row/column of the lowest cell are calculated rather

than
being literals.

This way, every time I insert a row, I always include all rows up to the
n-2'th row where n is the row of the cell that contains the formula.


Similarly, it would be nice if I could do the same

thing
in the Source Data

fields of an X-Y graph (X=date, Y=daily usage) so I don't need to make
corresponding changes as I add more rows (eg changing "=$A$7:$A$100" to
"=$A$7:$A$101"). Given that I'm plotting three lines (electicity, gas,
water) and each has two values (X and Y) that's 6

ranges
that I need to

change every time I add a new row.


I'm sure this must be possible, but I have the usual problem with online
help: trying to find a suitable phrase to search for in the index!

Defining the name for the cell is the easy bit (Insert

|
Names | Define) -

it's using that name in row() function and referring

to
a cell whose row

and column are calculated not literals that has got me baffled.






.

.

 

[Martin Underwood]

Copy this into B8 and fill across to D8:

=INDEX(B$4:INDIRECT("R[-1]C",0),COUNTA(A:A)-2)-B$4

---

Change

COUNTA(A:A)

to

COUNTA($A:$A)

in the formula.

Thanks for that. After I'd asked the question I had a search around and
found something similar: the crucial thing that I was missing was the
INDIRECT function (I looked for obvious function names like VALUE - INDIRECT
is not quite such an obvious name). I ended up with a formula such as
=INDIRECT(ADDRESS(ROW()-3,COLUMN()))-start_value (where "start_value" is a
label attached to the first cell in the sequence.

So I've solved the problem of the totals within the worksheet.

I'm still left with the problem of doing something similar for the ranges
used by the chart.

At present I have X and Y values of the form "='Sheet1'!$A$7:$A$100" under
the Source Data | Series tabsheet.

I've found that the 'Sheet1'! prefix is essential: if I remove it I get a
syntax error. Is there a function that returns the current sheet name? If
so, I presume I would make up a string
SHEET()+"!"+ADDRESS(ROW(label1),COLUMN(label1)+":"+ADDRESS(ROW(label2)-3,COLUMN(label2))
so it's in the required syntax and then pass that to a function that
evaluates it (is there such a function?) and use that as the value of the
range on the Source Data | Series tabsheet.

 

[Duke Carey]

if your formula is in row 9, and you've got a blank row 8, place this
formaula in A9 and copy it across

=OFFSET(A8,-1,)-OFFSET(A8,-2,0)

 

[Duke Carey]

For dynamic ranges feeding charts, check out this article

http://office.microsoft.com/en-us/assistance/HA011098011033.aspx

or this

http://www.j-walk.com/ss/excel/usertips/tip053.htm

Copy this into B8 and fill across to D8:

=INDEX(B$4:INDIRECT("R[-1]C",0),COUNTA(A:A)-2)-B$4

---

Change

COUNTA(A:A)

to

COUNTA($A:$A)

in the formula.

Thanks for that. After I'd asked the question I had a search around and
found something similar: the crucial thing that I was missing was the
INDIRECT function (I looked for obvious function names like VALUE - INDIRECT
is not quite such an obvious name). I ended up with a formula such as
=INDIRECT(ADDRESS(ROW()-3,COLUMN()))-start_value (where "start_value" is a
label attached to the first cell in the sequence.

So I've solved the problem of the totals within the worksheet.

I'm still left with the problem of doing something similar for the ranges
used by the chart.

At present I have X and Y values of the form "='Sheet1'!$A$7:$A$100" under
the Source Data | Series tabsheet.

I've found that the 'Sheet1'! prefix is essential: if I remove it I get a
syntax error. Is there a function that returns the current sheet name? If
so, I presume I would make up a string
SHEET()+"!"+ADDRESS(ROW(label1),COLUMN(label1)+":"+ADDRESS(ROW(label2)-3,COLUMN(label2))
so it's in the required syntax and then pass that to a function that
evaluates it (is there such a function?) and use that as the value of the
range on the Source Data | Series tabsheet.