• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Indirect formula in excel using R1C1 Reference style

H

hansa

New Member
Can anyone help me to understand the below formula:


=INDIRECT(("data!R2C"&43+IF($E$4<7,$E$4+5,$E$4-7)&":R10000C"&43+IF($E$4<7,$E$4+5,$E$4-7)),FALSE)


Why do we use the R1C1 reference style in a formula, why don't we use the cell reference directly, that is A1, B4, etc...


Thanks
 
The formula is calculating the column number, and A1 style reference doesn't let you do that. Reading above, we're looking at the 2nd row and 10000 row, the the part after the "C" is being calculated.
 
Hi ,


I think it is slightly different ; the reason the R1C1 style is used , at least in this case , is because the alternative , using the A1 reference style will make the formula extremely complicated.


To give you an example , let us say you wish to sum the cells in the range A2:A10 , using the INDIRECT function. Let us also say that the column number , in this case 1 , is stored in a cell D5.


If you wish to enter the R1C1 reference style in a cell , say F5 , you would enter :


=SUM(INDIRECT("R2C"&D5&":"&"R10C"&D5,FALSE))


The second parameter in the INDIRECT function , FALSE in this case , tells Excel to use the R1C1 style of referencing ; this has to be specified , since the default value of the second parameter is TRUE , which means Excel will use the A1 style of referencing.


Now , if you wish to have the same formula in A1 style of referencing , see how complicated the formula becomes :


=SUM(INDIRECT(CHAR(65+D5-1)&2&":"&CHAR(65+D5-1)&10,TRUE))


or


=SUM(INDIRECT(CHAR(65+D5-1)&2&":"&CHAR(65+D5-1)&10))


This is because specifying the column in the A1 style of referencing means you have to get the character 'A' , in this case.


Getting the characters 'A' through 'Z' is easy , since we can use the CHAR function ; getting the column AR in your case , since this is column 44 , is going to be much more difficult and complicated.


Of course , we can choose to not use either of the above methods , and use the following formula :


=SUM(INDIRECT(ADDRESS(2,D5)&":"&ADDRESS(10,D5)))


Narayan
 
Hi


Thanks for your reply.


Can you please explain me this part: &43 in the formula =INDIRECT(("data!R2C"&43+IF($E$4<7,$E$4+5,$E$4-7)&":R10000C"&43+IF($E$4<7,$E$4+5,$E$4-7)),FALSE)


Thanks for your reply.
 
Hi ,


I can only explain in general , since I do not know what your data in the relevant columns is.


The following statement :


IF($E$4<7,$E$4+5,$E$4-7)


gives a value of E4 + 5 as long as E4 is less than 7 , and gives a value of E4 - 7 once E4 is greater than or equal to 7. I do not know whether E4 can have negative values.


If we assume that E4 can only assume values greater than or equal to 0 , then for values :


0 , 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 ,...


the above IF statement will return values of :


5 , 6 , 7 , 8 , 9 , 10 , 11 , 0 , 1 , 2 , 3 , 4 ,...


So the part of your formula which is 43+IF($E$4<7,$E$4+5,$E$4-7) will return values of :


48 , 49 , 50 , 51 , 52 , 53 , 54 , 43 , 44 , 45 , 46 , 47 ,...


The columns referring to these numbers will be :


AV , AW , AX , AY , AZ , BA , BB , AQ , AR , AS , AT , AU ,...


What the above formula is doing is referring to the range :


AV2:AV10000 , AW2:AW10000 ,...


By itself , we cannot say what this formula is supposed to do. If you can upload the workbook , things will be clear.


Hopefully , you should be able to understand what this formula is doing.


Narayan
 
thanks for your reply Mr. Narayan. can i have your email address, i can send you the file as this document is confidential.
 
You must log in or register to reply here.
Back
Top