| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS(C14 +1,COLUMN(C2),1,
I am trying to produce the upper reference (C2) and lower reference C(11) of
my array by use of Address, row and column functions and use them in SUM function. It works when I put them separately in different cells and use concatenate and indirect to combine them as follows: C14=COUNTA(C2:C11) = 9 C16=ADDRESS(ROW(C2),COLUMN(C2),1,TRUE) = the result is $C$2 C17=ADDRESS(C14+1,COLUMN(C2),1,) = the result is $C$10 C18=CONCATENATE(C16,":",C17) C19=SUM(INDIRECT(C18)) = Gives the result of SUM(C2:C11) When I try to substitute above functions in one cell as follows: C20=SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS (C14+1,COLUMN(C2),1,)) It says my formula has errors. What is the error and how can I solve it? Isnt it allowed to use combination of functions for references? -- Rasoul Khoshravan Azar Kobe University, Kobe, Japan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS(C14 +1,COLUMN(C2),1,
try:
=SUM(INDIRECT(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE) & ":" & ADDRESS(C14+1,COLUMN(C2),1,))) "Khoshravan" wrote: I am trying to produce the upper reference (C2) and lower reference C(11) of my array by use of Address, row and column functions and use them in SUM function. It works when I put them separately in different cells and use concatenate and indirect to combine them as follows: C14=COUNTA(C2:C11) = 9 C16=ADDRESS(ROW(C2),COLUMN(C2),1,TRUE) = the result is $C$2 C17=ADDRESS(C14+1,COLUMN(C2),1,) = the result is $C$10 C18=CONCATENATE(C16,":",C17) C19=SUM(INDIRECT(C18)) = Gives the result of SUM(C2:C11) When I try to substitute above functions in one cell as follows: C20=SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS (C14+1,COLUMN(C2),1,)) It says my formula has errors. What is the error and how can I solve it? Isnt it allowed to use combination of functions for references? -- Rasoul Khoshravan Azar Kobe University, Kobe, Japan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS(C14 +1,COLUMN(C2),1,
You still have to use INDIRECT if you are calculating a cell or range
reference. Not sure why you are using ADDRESS though, but try this: C20=SUM(INDIRECT(ADDRESS(ROW(C2),COLUMN(C2), 1,TRUE)&":"&(ADDRESS(C14+1,COLUMN(C2),1,))) Hope this helps. Pete On Jul 15, 5:16 pm, Khoshravan wrote: I am trying to produce the upper reference (C2) and lower reference C(11) of my array by use of Address, row and column functions and use them in SUM function. It works when I put them separately in different cells and use concatenate and indirect to combine them as follows: C14=COUNTA(C2:C11) = 9 C16=ADDRESS(ROW(C2),COLUMN(C2),1,TRUE) = the result is $C$2 C17=ADDRESS(C14+1,COLUMN(C2),1,) = the result is $C$10 C18=CONCATENATE(C16,":",C17) C19=SUM(INDIRECT(C18)) = Gives the result of SUM(C2:C11) When I try to substitute above functions in one cell as follows: C20=SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS (C14+1,COLUMN(C2),1,)) It says my formula has errors. What is the error and how can I solve it? Isnt it allowed to use combination of functions for references? -- Rasoul Khoshravan Azar Kobe University, Kobe, Japan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS(C14 +1,COLUMN(C2
Not sure why you are using ADDRESS though,
Do you have better solution? but try this: C20=SUM(INDIRECT(ADDRESS(ROW(C2),COLUMN(C2), 1,TRUE)&":"&(ADDRESS(C14+1,COLUMN(C2),1,))) Hope this helps. Pete On Jul 15, 5:16 pm, Khoshravan wrote: I am trying to produce the upper reference (C2) and lower reference C(11) of my array by use of Address, row and column functions and use them in SUM function. It works when I put them separately in different cells and use concatenate and indirect to combine them as follows: C14=COUNTA(C2:C11) = 9 C16=ADDRESS(ROW(C2),COLUMN(C2),1,TRUE) = the result is $C$2 C17=ADDRESS(C14+1,COLUMN(C2),1,) = the result is $C$10 C18=CONCATENATE(C16,":",C17) C19=SUM(INDIRECT(C18)) = Gives the result of SUM(C2:C11) When I try to substitute above functions in one cell as follows: C20=SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS (C14+1,COLUMN(C2),1,)) It says my formula has errors. What is the error and how can I solve it? Isnt it allowed to use combination of functions for references? -- Rasoul Khoshravan Azar Kobe University, Kobe, Japan |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS(C14 +1,COLUMN(C2
this ...???
=SUM(INDIRECT("$C$2 :C" & C14+1)) "Khoshravan" wrote: Not sure why you are using ADDRESS though, Do you have better solution? but try this: C20=SUM(INDIRECT(ADDRESS(ROW(C2),COLUMN(C2), 1,TRUE)&":"&(ADDRESS(C14+1,COLUMN(C2),1,))) Hope this helps. Pete On Jul 15, 5:16 pm, Khoshravan wrote: I am trying to produce the upper reference (C2) and lower reference C(11) of my array by use of Address, row and column functions and use them in SUM function. It works when I put them separately in different cells and use concatenate and indirect to combine them as follows: C14=COUNTA(C2:C11) = 9 C16=ADDRESS(ROW(C2),COLUMN(C2),1,TRUE) = the result is $C$2 C17=ADDRESS(C14+1,COLUMN(C2),1,) = the result is $C$10 C18=CONCATENATE(C16,":",C17) C19=SUM(INDIRECT(C18)) = Gives the result of SUM(C2:C11) When I try to substitute above functions in one cell as follows: C20=SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS (C14+1,COLUMN(C2),1,)) It says my formula has errors. What is the error and how can I solve it? Isnt it allowed to use combination of functions for references? -- Rasoul Khoshravan Azar Kobe University, Kobe, Japan |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS(C14 +1,COLUMN(C2
Do you have better solution?
Maybe. Your use of ADDRESS doesn't seem to be based on any specific condition other than to find the end of the range: ADDRESS(C14+1 C14=COUNTA(C2:C11) = 9 But you're manually finding that end of range by using: =COUNTA(C2:C11) If you explained what you're wanting to do we might be able to come up with a better solution. Are you wanting to use a dynamic range based on C2:Cn ? =SUM(C2:INDEX(C2:C65536,COUNTA(C2:C65536))) Assumes no empty cells within the range. -- Biff Microsoft Excel MVP "Khoshravan" wrote in message ... Not sure why you are using ADDRESS though, Do you have better solution? but try this: C20=SUM(INDIRECT(ADDRESS(ROW(C2),COLUMN(C2), 1,TRUE)&":"&(ADDRESS(C14+1,COLUMN(C2),1,))) Hope this helps. Pete On Jul 15, 5:16 pm, Khoshravan wrote: I am trying to produce the upper reference (C2) and lower reference C(11) of my array by use of Address, row and column functions and use them in SUM function. It works when I put them separately in different cells and use concatenate and indirect to combine them as follows: C14=COUNTA(C2:C11) = 9 C16=ADDRESS(ROW(C2),COLUMN(C2),1,TRUE) = the result is $C$2 C17=ADDRESS(C14+1,COLUMN(C2),1,) = the result is $C$10 C18=CONCATENATE(C16,":",C17) C19=SUM(INDIRECT(C18)) = Gives the result of SUM(C2:C11) When I try to substitute above functions in one cell as follows: C20=SUM(ADDRESS(ROW(C2),COLUMN(C2),1,TRUE):ADDRESS (C14+1,COLUMN(C2),1,)) It says my formula has errors. What is the error and how can I solve it? Isnt it allowed to use combination of functions for references? -- Rasoul Khoshravan Azar Kobe University, Kobe, Japan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I name the column headings ie. Address | New Users to Excel | |||
| ???? =offset(ADDRESS(ROW(),COLUMN()),1,1,1,1) | Excel Worksheet Functions | |||
| Separate address column to Five columns | Excel Worksheet Functions | |||
| I have ad address in one column. I need to break apart the data | New Users to Excel | |||
| How do you add a parentesis to a column of email address? | Excel Discussion (Misc queries) | |||
Linear Mode
