**Excel Course : **
The Ultimate Excel Programmer Course

**Scenario**

Suppose you are asked to calculate cumulative sale. And the figure should be displayed in columns. Hence the formula should increment by row when you copy it horizontally.

**Solutions**

**1. OFFSET - COLUMN**

*Let's understand*

**OFFSET**and**COLUMN**functions
The Excel syntax for the OFFSET function is:

=OFFSET(reference,rows,columns,[height],[width])

1.

**Reference -**It is the starting point in range.

2.

**Rows**- These are the number of rows you want Excel to move from the starting point.

3.

**Columns -**These are the number of columns you want Excel to move from the starting point.

4.

**Height**[Optional] - It is the size of range with number of rows tall you want to return.

5.

**Width**[Optional] - It is the size of range with number of columns wide you want to return.

**Examples :**

1. OFFSET(A1,0,0,2)

From A1 returns 2 rows tall range. That means A1:A2

2. OFFSET(A1,0,0,1,2)

From A1 returns 2 columns wide range. That means A1:B1

3. OFFSET(A1,0,0,2,2)

From A1 returns 2 rows tall and 2 columns wide range. That means A1:B2

4. OFFSET(A1,1,0)

It means move down 1 row i.e. A2. Since height and width components are optional in OFFSET formula, you can skip their references.

5. OFFSET(A1,0,2)

It means move right 2 columns i.e. C1.

6. SUM(OFFSET(A1,1,2,2,1))

It means move down 1 row, 2 columns and then select 2 X 1 range. That means C2:C3

**COLUMN**

The Excel syntax for the COLUMN function is:

=COLUMN(reference)

It returns the column number of a reference.

It returns the column number of a reference.

**Examples :**

1. COLUMN(A1) returns 1.

2. COLUMN(B1) returns 2.

2. COLUMN(B1) returns 2.

**Evaluate Formula :**
Let's translate the following formula into English :

=SUM(OFFSET($A$1,0,0,COLUMN(B1)))

Since COLUMN(B1) returns 2, the above formula means = SUM (A1 through 2 rows wide). That is equivalent to =SUM(A1:A2)

=SUM(OFFSET($A$1,0,0,COLUMN(B1)))

Since COLUMN(B1) returns 2, the above formula means = SUM (A1 through 2 rows wide). That is equivalent to =SUM(A1:A2)

When you copy the above formula across columns, the column formula increments by one.

=SUM(OFFSET($A$1,0,0,COLUMN(C1)))

**2. INDIRECT - ADDRESS**

=SUM(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(COLUMN(B1),1)))

*Let's understand*

**INDIRECT**and**ADDRESS**functions
The Excel syntax for the INDIRECT function is:

=INDIRECT(reference)

It allows you to change the reference to a cell within a formula without changing the formula itself.

It allows you to change the reference to a cell within a formula without changing the formula itself.

**Examples :**

1. If cell A1 has the value "B2", then =INDIRECT(A1) will return the value in B2.

2. =SUM(INDIRECT("A1:A2")) --- INDIRECT function changes a text string to a reference.

Let's evaluate this formula. = SUM(A1:A2)

The Excel syntax for the ADDRESS function is:

=ADDRESS (row_num, col_num, ref_type, ref_style, sheet_name)

It returns the address for a cell when provided a row and a column number.

1.

2.

3. ref_type

1 or omitted for absolute (For example, $A$1)

2 for absolute row, relative column (For example, A$1)

3 for relative row, absolute column (For example, $A1)

It returns the address for a cell when provided a row and a column number.

1.

**row_num -**It is the row number to use in the cell reference. For example, 3 for row 32.

**col_num -**It is the column number to use in the cell reference. For example, 3 for column C3. ref_type

**[Optional]**- It is the type of address you want to return.1 or omitted for absolute (For example, $A$1)

2 for absolute row, relative column (For example, A$1)

3 for relative row, absolute column (For example, $A1)

4 for relative (For example, A1)

4.

1 or TRUE for A1 style ; 0 or FALSE for R1C1 style.

4.

**ref_style****[Optional]**- It is the reference style which signifies whether the address returned in formatted in the A1 style or the R1C1 style.1 or TRUE for A1 style ; 0 or FALSE for R1C1 style.

5. sheet_name

**[Optional] -**It is the name of the worksheet to be used as the external reference.
For example, "Sheet2"

**Examples :**

1. ADDRESS(2,1) means $A$2

2. ADDRESS(1,3,2) means C$1

**Evaluate Formula :**

Let's evaluate the following formula :

=SUM(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(COLUMN(B1),1)))

= SUM(INDIRECT("$A$1:$A$2"))

= SUM($A$1:$A$2)

Oh Great you save my lots of real time

ReplyDelete