• 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.

RC Style

I

irisqueiroz

Member
Hi, I have a doubt in RC style.


What does this means?


Range("G4").Select

ActiveCell.FormulaR1C1 = "=SUMIF(C3,RC[-1],C2)"


Range("H4").Select

ActiveCell.FormulaR1C1 = "=COUNTIF(C3,RC[-2])"


Thanks!
 
RC style stands for Row/Column style. In this style, rather than letters and numbers, addresses are given only in numbers. Numbers in square brackets give relative position, while regular numbers give absolute position.


So, let's take a look at the first formula. Everything is given in reference to G4.

=SUMIF(C3,RC[-1],C2)


The "C3" at the beginning is not the cell C3, but rather column 3. Since there's no R component, this is a reference to the entire column. Since there's no square brackets, C3 is equivalent to writing "$C:$C"


Next, RC[-1] is the 2nd argument. There is an R component, but no number given. This means that the cell will be in same row as our reference point, G4. So, we'll be in row 4. The column given is relative reference, 1 column to the left (aka, -1) of G4, so the address RC[-1] is referring to F4.


Finally, "C2", similar to the first argument, is an absolute reference to 2nd column, aka, $B:$B.

In conclusion, this:

=SUMIF(C3,RC[-1],C2)

is the RC equivalent of:

=SUMIF($C:$C,F4,$B:$B)


The 2nd formula in H4 will evaluate to:

=COUNTIF($C:$C,F4)
 
Hi ,


To add to what Luke has posted , you need to understand two points about this :


1. You can toggle between what is known as the A1 style of addressing ( =SUMIF($C:$C,F4,$B:$B) ) and the R1C1 style of addressing within Excel through the options ; when you click on Excel Options , Formulas , check the box labelled R1C1 Reference Style , you will see all formulae in this style ; uncheck this box to see all formulae in the A1 style of addressing.


2. When you see a formula such as =SUMIF($C:$C,F4,$B:$B) , you can understand it very easily , since the addresses are referring to cells directly ; F4 refers to the cell at the intersection of column F and row 4. When you see a formula in R1C1 style , it is more difficult to grasp it in terms of the cells that we see on screen. To start with all copied formulae are identical ; thus , when you copy the above formula : =SUMIF($C:$C,F4,$B:$B) from G4 to G5 , the formula will change to :


=SUMIF($C:$C,F5,$B:$B)


but in R1C1 style the formulae in G4 and G5 will be identical ! Of course , this has its uses ; one of them is to check whether two formulae are identical ; using VBA it is difficult to compare :


=SUMIF($C:$C,F4,$B:$B) and =SUMIF($C:$C,F5,$B:$B)


to decide whether they are identical ; using the R1C1 style of addressing , it is easy.


Narayan
 
You must log in or register to reply here.
Back
Top