#### Bhagya07

##### New Member
can any1 explain with example?

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Relative:
=A1
When copied down it turns to =A2, =A3, =A4 etc
When copied right it turns to =B1, =C1, =D1
When copied diagonally it turns to =B2, =C3, =D4
It's great when copying formulas where the address needs to increase accordingly

Absolute:
=\$A\$1
No matter where or how you copy it, it will always read =\$A\$1
This is good when you need its cell reference to stay the same no matter where you copy it.

Mixed:
=\$A1
In this case, No matter where you copy it, the reference to column A will always reference column A, but the row reference will increase with each row you copy down.

=A\$1
In this case, No matter where you copy it, the reference to row 1 will always reference row 1, but the column reference will increase with each row you copy right.

Last edited:
Relative:
=A1
When copied down it turns to =A2, =A3, =A4 etc
When copied right it turns to =B1, =C1, =D1
When copied diagonally it turns to =B2, =C3, =D4
It's great when copying formulas where the address needs to increase accordingly

Absolute:
=\$A\$1
No matter where or how you copy it, it will always read =\$A\$1
This is good when you need its cell reference to stay the same no matter where you copy it.

Mixed:
=\$A1
In this case, No matter where you copy it, the reference to column A will always reference column A, but the row reference will increase with each row you copy down.

=A\$1
In this case, No matter where you copy it, the reference to row 1 will always reference row 1, but the column reference will increase with each row you copy right.

thankyou, and what does the below code means RC[-3]:RC[-1] and Rc[-4]:RC[-2]
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:RC[-2])"

thankyou, and what does the below code means RC[-3]:RC[-1] and Rc[-4]:RC[-2]
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:RC[-2])"

Got me. I've never had a need for R1C1 formulas. It's obviously summing and averaging a range, but how that range translates into something tangible.... I've never had a need for it. It is the down side of being self-taught/Mr Excel taught, you only learn what you need to know to accomplish the task you're trying to do. I'll see if I can research it sometime today and get back with you though. Unless someone else has an answer for you.

[Solved] VBA: =sum

In short, it is a way to reference a range that starts 3 columns to the left of the active cell to 1 column to the left of the active cell. It's very similar to the Offset function.

Replies
3
Views
55
Replies
6
Views
289
Replies
7
Views
89
Replies
6
Views
164
Replies
0
Views
203

1,203,508
Messages
6,055,813
Members
444,826
Latest member
aggerdanny

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back