Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Labelling a row so it can be used as an offset for sum, difference or range
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. |
#2
|
|||
|
|||
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. . |
#3
|
|||
|
|||
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. . . |
#4
|
|||
|
|||
"Jason Morin" wrote in message
... 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. |
#5
|
|||
|
|||
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) |
#6
|
|||
|
|||
For dynamic ranges feeding charts, check out this article
http://office.microsoft.com/en-us/as...098011033.aspx or this http://www.j-walk.com/ss/excel/usertips/tip053.htm "Martin Underwood" wrote: "Jason Morin" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
dynamic offset | Excel Discussion (Misc queries) | |||
Formula Help With MATCH & OFFSET | Excel Worksheet Functions | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions | |||
Passing Cell Address to Offset | Excel Worksheet Functions |