Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Offset vs Address (Excel 2007)

    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.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset vs Address (Excel 2007)

    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)

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset vs Address (Excel 2007)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •