Offset vs Address (Excel 2007)

Topic

New Reply

I’m confused about when to use offset and when to use address.

When I read the help file about offset, it says “Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.” This description sounds like what I want. The function I’m using is =SLOPE(OFFSET(A5,COUNT(A5:A500)-4,1),OFFSET(B5,COUNT(B5:B500)-4,1)). This returns #VALUE. When I evaluate the OFFSET arguments separately I get values, not a range.

I can get what I want using Indirect and Address, but it seems like OFFSET should work and be less convoluted. I need to use a formula because we add data frequently. I’ve tried using a named range, but didn’t get what I needed either.

Any clarification about what I’m doing wrong and the differences between offset and Indirect(address) would be most appreciated. I’ve attached an example spreadsheet.

Reply | Quote

Replies

In this example, you want OFFSET to specify the size of a range, not to specify a shift. Try this formula:

=SLOPE(OFFSET(A5,0,0,COUNT(A5:A500),1),OFFSET(B5,0,0,COUNT(B5:B500),1))

or even

=SLOPE(OFFSET(A5,0,0,COUNT(A5:A500)),OFFSET(B5,0,0,COUNT(B5:B500)))

Explanation: look at OFFSET(A5,0,0,COUNT(A5:A500),1). This means:
– Start at A5.
– Shift 0 rows down.
– Shift 0 columns to the right (i.e. we’re still at A5).
– Resize the number of rows to the count of numeric values in A5:A500.
– Resize the number of columns to 1; since we started with 1 column you can omit this argument.
Similar for OFFSET(B5,0,0,COUNT(B5:B500),1)

Reply | Quote

Thanks Hans. Your explanation makes a lot more sense than the help files!

Reply | Quote