Offset Function Question

[MikeL]

Hello,
I am using Offset for the first time. I would like the formula to sum cells E2:G2 for a total of "116"

It seems I misunderstand the formula as it moves 2 cells to the left of the G2 and returns "1"

Thanks in advance.

Excel 2007
	B	C	D	E	F	G	H
1	jan	feb	mar	apr	may	june
2	5	10	4	1	100	15
3
4
5							1
Sheet1

Cell Formulas
Range	Formula
H5	=OFFSET(G2,0,-2)

 

[Aladin Akyurek]

Hello,
I am using Offset for the first time. I would like the formula to sum cells E2:G2 for a total of "116"

It seems I misunderstand the formula as it moves 2 cells to the left of the G2 and returns "1"

Thanks in advance.


Excel 2007

	B	C	D	E	F	G	H
1	jan	feb	mar	apr	may	june
2	5	10	4	1	100	15
3
4
5							1

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H5 =OFFSET(G2,0,-2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You are asking for the value that is 2 columns to the left of the column of G2.

Try...

=SUM(OFFSET(G2,0,0,1,-3))

 

[MikeL]

That worked. Thanks!

 

[Aladin Akyurek]

That worked. Thanks!

You are elcome.

 

[MikeL]

Am following up on this...

Why is the optional last value a negative? Per MS support, it has to be positive.

http://support.microsoft.com/kb/324991


Enter the following formulas into cell E2 (or any available blank cell):


=OFFSET(C2,2,-1,1,1)
C2 - The referenced cell.
2 - Indicates the number of rows to move. Positive numbers mean move down, and negative numbers mean move up.
-1 - Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left.
1 (second last value) - (Optional.) Indicates how many rows of data to return. This number must be a positive number.
1 (last value) - (Optional.) Indicates how many columns of data to return. This number must be a positive number.

 

[Aladin Akyurek]

Am following up on this...

Why is the optional last value a negative? Per MS support, it has to be positive.

http://support.microsoft.com/kb/324991


Enter the following formulas into cell E2 (or any available blank cell):


=OFFSET(C2,2,-1,1,1)
C2 - The referenced cell.
2 - Indicates the number of rows to move. Positive numbers mean move down, and negative numbers mean move up.
-1 - Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left.
1 (second last value) - (Optional.) Indicates how many rows of data to return. This number must be a positive number.
1 (last value) - (Optional.) Indicates how many columns of data to return. This number must be a positive number.

If you take E2 as the start address and you intend to sum E2:G2, the formula with OFFSET would look like:

=SUM(OFFSET(E2,0,0,1,3))

If you take G2 as the address to start with and you want still to sum E2:G2, the formla becomes:


=SUM(OFFSET(G2,0,0,1,-3))

where -3 means: sum from 3 cells to the left of G2 (including G2) up to G2.