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

OFFSET and SUM(OFFSET)

P

PipBoy808

Member
I don't quite understand the last two variables that are part of the OFFSET function (i.e. height, width). I keep getting a #VALUE! error if I include these values.

I have a 6 column x 6 row range of dummy data (A1:F6) that I've been practising on. If I type in:

=OFFSET(A1,1,1,3,1)

I get an error message, when I think it should produce B2:B4.

However, if I type in:

=SUM(OFFSET(A1,1,1,3,1))

then Excel sums the values B2:B4 just fine.

It would appear that Excel knows what cells I'm talking about when I include the SUM function, but displays an error when I use OFFSET on its own. What am I doing wrong?
 
I see. Would I be correct in saying that when used on its own, OFFSET can only return a single cell? Therefore, (height, width) has to be (1,1) or non-existent unless you're combining it with a function that will actually do something with a range of cells, such as SUM?
 
Hi ,

The last two parameters in an OFFSET function refer to the number of rows and columns that will be considered as part of the OFFSET function.

Thus , if you have a formula such as :

=OFFSET($A$1,2,3)

you are just referring to $D$3.

So when you enter this formula in any worksheet cell , that cell will display the contents of D3.

Here , the last two parameters , which are optional , are taken with reference to the first parameter ; since the first parameter , which is $A$1 , is a single cell , the last two parameters are taken implicitly as 1 and 1. It is as if you had entered :

=OFFSET($A$1,2,3,1,1)

Now , if you enter an OFFSET formula such as :

=OFFSET($A$1:$A$3,2,3)

you have not entered the last two parameters , but Excel will consider that you have entered a first parameter spanning 3 rows and 1 column , and use the above OFFSET formula as if you had written :

=OFFSET($A$1:$A$3,2,3,3,1)

If you enter this formula into a cell , you will get a #VALUE! error , since you cannot enter a range spanning 3 rows and 1 column into a single cell.

Similarly , if you write :

=OFFSET($A$1,2,3,3,1)

you are again asking Excel to consider a multi-cell range spanning 3 rows and 1 column. Here again , you will get an error ; however if you wrap a SUM function around this , it is perfectly valid , since Excel can add the contents of 3 cells.

Narayan
 
That's fantastic. I think I understand now. Wrapping OFFSET with another function works because other functions will condense the offset to a single value, which fits inside a single cell. Thanks!
 
You must log in or register to reply here.
Back
Top