help me understand this function

[litoria]

I was given an excel sheet today with this function:
=SUM(INDIRECT(ADDRESS(ROW(C26),COLUMN(C26)) & ":" & ADDRESS(ROW(C26),COLUMN(C26)+C9,4)))

Can someone help me to understand how this works? I have never seen the INDIRECT or ADDRESS functions before. Thanks!

 

[bebo021999]

I was given an excel sheet today with this function:
=SUM(INDIRECT(ADDRESS(ROW(C26),COLUMN(C26)) & ":" & ADDRESS(ROW(C26),COLUMN(C26)+C9,4)))
Can someone help me to understand how this works? I have never seen the INDIRECT or ADDRESS functions before. Thanks!

i will guide you step by step to understand.
Example: You have array of number from C26:G26, with value of 5,2,1,2,3
At D9, you want to get sum with above formular
At C9 is adjust value.
How it run?
If C9=0, D9 = sum(C26:C26) = 5
If C9=1, D9 = sum(C26:D26) = 7
If C9=2, D9 = sum(C26:E26) = 8
If C9=3, D9 = sum(C26:F26) = 10
mean the range will expanse 1,2,3 collumn to the right, depend on C9.
Explan formular:
ADDRESS(ROW(C26),COLUMN(C26)) returns C26 (in text format)
ADDRESS(ROW(C26),COLUMN(C26)+C9,4), if C9=1, return D26 (in text format)
INDIRECT(ADDRESS(ROW(C26),COLUMN(C26)) & ":" & ADDRESS(ROW(C26),COLUMN(C26)+C9,4)) return range C26:D26 if C9=1 (in address format)

Below formular is equal:
=SUM(OFFSET($C$26,,,,C9+1))

Hope it is clear for you.

 

[litoria]

This is very helpful. What does the 4 at the end of the formula do?

 

[bebo021999]

This is very helpful. What does the 4 at the end of the formula do?

Construction:
ADDRESS(row_num,col_num,abs_num,a1,sheet_text)
Example:
=ADDRESS(1,2) ===> $B$1
=ADDRESS(1,2,2) ===> B$1
=ADDRESS(1,2,3) ===> $B1
=ADDRESS(1,2,4) ===> B1
=ADDRESS(1,2,4,TRUE) ===> B1
=ADDRESS(1,2,4,FALSE) ===> R[1]C[2]
=ADDRESS(1,2,4,TRUE,"sheet1") ===> sheet1!B1