OFFSET function is giving the wrong result in some cells.

[polichara]

Hi all,

I have made a reduced spreadsheet demonstrating my problem. I have four cells in a 2x2 arrangement, with the values -1, 0, 0, and -1. In four other cells I have =OFFSET(A1:B2,1,1,2,2) but the result in those cells is -1, 0, 0, and 0. The fourth value should be -1, not 0. Any advice on how to fix this problem?

Thanks!

[XOR LX]

Hi,

You're making several mistakes in your assumptions about how OFFSET operates.

1) Passing a height and/or a width parameter to a multi-cell array formula comprising a single OFFSET function is redundant: how can an array comprising two vertical or two horizontal cells be returned to a single cell?

2) Change your entries in A1:B2 as follows: 1 in A1, 2 in B1, 3 in A2 and 4 in B2. In addition, put 5 in B3, 6 in C2 and 7 in C3 and look at the results of your formula.

This is because, ignoring those redundant height and width parameters,

=OFFSET(A1:B2,1,1)

committed over a 2x2 range, is equivalent to performing:

=OFFSET(A1,1,1)	=OFFSET(B1,1,1)
=OFFSET(A2,1,1)	=OFFSET(B2,1,1)

in each of those respective cells.

Regards

[Aardigspook]

Welcome to the forum.

Your formula is looking 1 row further down and one column further across than you want, so A4 is actually returning B2 not A1, and B5 is returning C3 not B2.

Either use the solution above or if you really want to use your construction (because perhaps of your real data set in your real file, then change the 1's to 0's:

Please Login or Register  to view this content.

[MrShorty]

Why do you expect {-1,0;0,-1} from this function? What the OFFSET() function is doing is:

1) Start in A1:B2 (so effectively starting in A1).
2) Go down 1 cell and to the right one cell (B2)
3) Then expand to a range 2 columns high and 2 columns wide (B2:C3). The value in B2 is -1, B3, C2, C3 are empty, so they return 0, and the final result is {-1,0;0,0}

If you want the OFFSET() function to return A1:B2, then you need something different. =OFFSET(A1:B2,0,0,2,2) will work or =OFFSET(A1:B2,1,1,-2,-2) or equivalent.

[XOR LX]

Please Login or Register  to view this content.

The OFFSET in that formula is redundant. You may as well array-enter:

=A1:B2

over a 2x2 range.

Regards

[polichara]

OK, thank you. I just realized my mistake was that I should have put 0 for the first two parameters instead of 1.

[polichara]

I realize it is redundant, this was just a reduced problem to show the problem I was having in a larger spreadsheet.

[polichara]

To clarify, I wasn't using 1 in the original spreadsheet, but the value was 1 greater than it should have been. Anyway, thank you all for correcting my mistake.

[Aardigspook]

You're welcome, glad we could help and thanks for marking the thread as Solved.

Edit: and thanks for the rep.

[XOR LX]

=OFFSET(A1:B2,0,0,2,2)

If the formula is being committed over a 2x2 range then the height and width parameters here are redundant. Any positive number x in

=OFFSET(A1:B2,0,0,x,x)

will produce an identical result.

=OFFSET(A1:B2,1,1,-2,-2)

I'm not sure I fully understand your choice here. Whilst it works, do you understand why it works, and therefore how convoluted that set-up is? I understand that this 'bug' of allowing negative values for OFFSET's width and height parameters can sometimes be used to good effect, though I'm struggling to see how that is the case here.

Just to clarify how the above works, it is equivalent to the four separate formulas:

OFFSET(A1,1,1,-2,-2)	OFFSET(B1,1,1,-2,-2)
OFFSET(A2,1,1,-2,-2)	OFFSET(B2,1,1,-2,-2)

which is:

A1:B2	B1:C2
A2:B3	B2:C3

Since only a single cell reference can be returned to a single cell, only the first cell referenced in each of the above multi-cell references is processed, the above resolving (by a miracle!) to:

A1	B1
A2	B2

Regards

[polichara]

My original misunderstanding was with the row and column parameters. Well, I should have known the right answer, but because I had been so used to using the position in other functions rather than the offset, I was off by 1. My original spreadsheet has a much larger range, and I'm using the OFFSET function within a larger formula to get a subset of an array, but for example, I was using 2 for the row parameter when I should have used 1.

As for the use of negative values for width and height, I don't intend to use that, but I do understand what the intent is.

I could have also used 0's in those parameters, but width and height values smaller than the referenced array, and that wouldn't have been redundant I think. I probably should have done that and I might have seen my error, but the way my values are arranged in my spreadsheet, the problem was a little bit hidden. I could have used different values, but I arrogantly thought this may have been a bug rather than a mistake on my part, and so I wanted to recreate the same values I used originally.

[XOR LX]

Start in A1:B2 (so effectively starting in A1)

Ah, this is perhaps the explanation for your misunderstanding. The assumption in parentheses is not correct: each of the four cells in the output will be offset from a different starting cell.

Regards