Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make R1C1 nomenclature work with SUM() in a cell ?
Hi all;
Hopefully a simple problem... In Excel 97 I want to sum integer fields in a column, where the 'sum' field is the last item in the column. Say, 22 integers in column D, and D28 =SUM($D6:$D27). Very, very trivial so far BUT... inserting rows above the cell containing the =SUM() entry does NOT always adjust the range in the SUM parentheses correctly. Depends how you insert rows, and where the inserting point is, and possibly on other factors. Net result is that I can insert rows in the sheet which do not get included correctly in the column sum. I've seen this inject serious defects into many spreadsheets. It bit me again today. I need to generate a formula which says "sum column D, starting at cell $D6 and going through to (the cell above the sum cell)". Excel Help says I can do this using RC[-1] notation, but the SUM function won't accept this, ie =SUM($D6:RC[-1]) throws "the formula you typed contains an error". Web search didn't help. Lots of tutorials and examples, nothing on this. Can anyone help please ? Thanks, Mal. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make R1C1 nomenclature work with SUM() in a cell ?
=SUM(INDIRECT("D6"):R[-1]C)
activeCell.FormulaR1C1 = "=SUM(INDIRECT(""D6""):R[-1]C)" -- Regards, Tom Ogilvy wrote in message ... Hi all; Hopefully a simple problem... In Excel 97 I want to sum integer fields in a column, where the 'sum' field is the last item in the column. Say, 22 integers in column D, and D28 =SUM($D6:$D27). Very, very trivial so far BUT... inserting rows above the cell containing the =SUM() entry does NOT always adjust the range in the SUM parentheses correctly. Depends how you insert rows, and where the inserting point is, and possibly on other factors. Net result is that I can insert rows in the sheet which do not get included correctly in the column sum. I've seen this inject serious defects into many spreadsheets. It bit me again today. I need to generate a formula which says "sum column D, starting at cell $D6 and going through to (the cell above the sum cell)". Excel Help says I can do this using RC[-1] notation, but the SUM function won't accept this, ie =SUM($D6:RC[-1]) throws "the formula you typed contains an error". Web search didn't help. Lots of tutorials and examples, nothing on this. Can anyone help please ? Thanks, Mal. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make R1C1 nomenclature work with SUM() in a cell ?
Also:
=SUM(D6:OFFSET(D28,-1,0)) -- Jim Rech Excel MVP wrote in message ... | Hi all; | | Hopefully a simple problem... | | In Excel 97 I want to sum integer fields in a column, where the 'sum' | field is the last item in the column. Say, 22 integers in column D, | and D28 =SUM($D6:$D27). Very, very trivial so far BUT... inserting | rows above the cell containing the =SUM() entry does NOT always | adjust the range in the SUM parentheses correctly. Depends how you | insert rows, and where the inserting point is, and possibly on other | factors. Net result is that I can insert rows in the sheet which do | not get included correctly in the column sum. I've seen this inject | serious defects into many spreadsheets. It bit me again today. | | I need to generate a formula which says "sum column D, starting at | cell $D6 and going through to (the cell above the sum cell)". | | Excel Help says I can do this using RC[-1] notation, but the SUM | function won't accept this, ie =SUM($D6:RC[-1]) throws "the formula | you typed contains an error". Web search didn't help. Lots of | tutorials and examples, nothing on this. | | Can anyone help please ? | | Thanks, | | Mal. | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make R1C1 nomenclature work with SUM() in a cell ?
Hi
try in D28: =SUM(OFFSET($D$28,-1,0,-ROW()+1)) " wrote: Hi all; Hopefully a simple problem... In Excel 97 I want to sum integer fields in a column, where the 'sum' field is the last item in the column. Say, 22 integers in column D, and D28 =SUM($D6:$D27). Very, very trivial so far BUT... inserting rows above the cell containing the =SUM() entry does NOT always adjust the range in the SUM parentheses correctly. Depends how you insert rows, and where the inserting point is, and possibly on other factors. Net result is that I can insert rows in the sheet which do not get included correctly in the column sum. I've seen this inject serious defects into many spreadsheets. It bit me again today. I need to generate a formula which says "sum column D, starting at cell $D6 and going through to (the cell above the sum cell)". Excel Help says I can do this using RC[-1] notation, but the SUM function won't accept this, ie =SUM($D6:RC[-1]) throws "the formula you typed contains an error". Web search didn't help. Lots of tutorials and examples, nothing on this. Can anyone help please ? Thanks, Mal. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make R1C1 nomenclature work with SUM() in a cell ?
Hi Mal
Either "=SUM(R6C4:R[-1]C4)" for distinct D column, or "=SUM(R6C:R[-1]C)" for "this column". HTH. Best wishes Harald skrev i melding ... Hi all; Hopefully a simple problem... In Excel 97 I want to sum integer fields in a column, where the 'sum' field is the last item in the column. Say, 22 integers in column D, and D28 =SUM($D6:$D27). Very, very trivial so far BUT... inserting rows above the cell containing the =SUM() entry does NOT always adjust the range in the SUM parentheses correctly. Depends how you insert rows, and where the inserting point is, and possibly on other factors. Net result is that I can insert rows in the sheet which do not get included correctly in the column sum. I've seen this inject serious defects into many spreadsheets. It bit me again today. I need to generate a formula which says "sum column D, starting at cell $D6 and going through to (the cell above the sum cell)". Excel Help says I can do this using RC[-1] notation, but the SUM function won't accept this, ie =SUM($D6:RC[-1]) throws "the formula you typed contains an error". Web search didn't help. Lots of tutorials and examples, nothing on this. Can anyone help please ? Thanks, Mal. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make R1C1 nomenclature work with SUM() in a cell ?
Sorry. Didn't read the question carefully enough.
Best wishes Harald |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make R1C1 nomenclature work with SUM() in a cell ?
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make R1C1 nomenclature work with SUM() in a cell ?
Jim; you suggested =SUM(D6:OFFSET(D28,-1,0))
....no errors, but requires me to know the cell D28 address Mal- D28 (in this example) is the cell you're entering the formula it. Surely you know that<g. And it will adjust automatically when you copy the formula or insert rows. -- Jim Rech Excel MVP wrote in message ... | On Thu, 16 Dec 2004 12:08:03 GMT, wrote: | | Hi all; | | Hopefully a simple problem... | | Can anyone help please ? | | | Many thanks for all the helpful suggestions- all very close to what I | want to achieve. Unfortunately, no luck yet. | | Tom; you suggested: | =SUM(INDIRECT("D6"):R[-1]C) | ...This gives "The formula you typed contains an error" with the | R[-1]C section highlighted. | | Jim; you suggested | =SUM(D6:OFFSET(D28,-1,0)) | ...no errors, but requires me to know the cell D28 address, which is | what I'm trying to avoid (sorry if I was unclear). When rows are | inserted, the sum cell becomes D29, D30, D31... etc. Its address | isn't fixed. I tried using ROW() and COLUMN() but then I got "The | formula you typed contains an error"... | | Mysterious Benefactor #3 you suggested... | =SUM(OFFSET($D$28,-1,0,-ROW()+1)) | ... and this gives #REF! and also requires me to know the address of | D28... | | Harald; you suggested | "=SUM(R6C4:R[-1]C4)" for distinct D column, or | "=SUM(R6C:R[-1]C)" for "this column" | ...which is almost EXACTLY what I want to do. | ...but both of these give me "The formula you typed contains an error" | with the RHS of the colon highlighted. | | | I've also tried =SUM(ADDRESS(etc) without luck. What on earth am I | doing wrong ? | | Mal. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make R1C1 nomenclature work with SUM() in a cell ?
Copied from the Excel Formula Bar - worked fine for me if I am using R1C1
addressing. You can't type a formula in R1C1 format if you are in A1 mode. (and you get the error you describe). -- Regards, Tom Ogilvy wrote in message ... On Thu, 16 Dec 2004 12:08:03 GMT, wrote: Hi all; Hopefully a simple problem... Can anyone help please ? Many thanks for all the helpful suggestions- all very close to what I want to achieve. Unfortunately, no luck yet. Tom; you suggested: =SUM(INDIRECT("D6"):R[-1]C) ...This gives "The formula you typed contains an error" with the R[-1]C section highlighted. Jim; you suggested =SUM(D6:OFFSET(D28,-1,0)) ...no errors, but requires me to know the cell D28 address, which is what I'm trying to avoid (sorry if I was unclear). When rows are inserted, the sum cell becomes D29, D30, D31... etc. Its address isn't fixed. I tried using ROW() and COLUMN() but then I got "The formula you typed contains an error"... Mysterious Benefactor #3 you suggested... =SUM(OFFSET($D$28,-1,0,-ROW()+1)) ... and this gives #REF! and also requires me to know the address of D28... Harald; you suggested "=SUM(R6C4:R[-1]C4)" for distinct D column, or "=SUM(R6C:R[-1]C)" for "this column" ...which is almost EXACTLY what I want to do. ...but both of these give me "The formula you typed contains an error" with the RHS of the colon highlighted. I've also tried =SUM(ADDRESS(etc) without luck. What on earth am I doing wrong ? Mal. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make them work on the Each cell Value | Excel Worksheet Functions | |||
r1c1 reference style - how to make it default | Excel Discussion (Misc queries) | |||
Make table query will work in datasheet view but will not make tab | Excel Discussion (Misc queries) | |||
How can i make this work within an IF statement: (41<=Cell<=46) | New Users to Excel | |||
Macro to make a cell work like a check | Excel Programming |