+ Reply to Thread
Results 1 to 4 of 4

OFFSETTING a reference range to exclude 3 bottom rows

  1. #1
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    20 yr. old Excel 2002!
    Posts
    710

    OFFSETTING a reference range to exclude 3 bottom rows

    Hi
    How do I define a growing by row insertion range to exclude 3 bottom rows?
    Is there a way to do it with the OFFSET formula?
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    483

    Re: OFFSETTING a reference range to exclude 3 bottom rows

    Try

    =SUM(A1:INDEX(A:A,LOOKUP(2^1023,A:A,ROW(A:A))-3))

  3. #3
    Forum Contributor
    Join Date
    01-20-2014
    Location
    Greece
    MS-Off Ver
    20 yr. old Excel 2002!
    Posts
    710

    Re: OFFSETTING a reference range to exclude 3 bottom rows

    That might work with SUM, but what if I wanted to use for example INDEX of the A1:Z13 and I want to disregard data on the last 3 rows?

    Edit: A1:Z13
    Last edited by drgkt; 09-30-2017 at 06:04 PM.

  4. #4
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    483

    Re: OFFSETTING a reference range to exclude 3 bottom rows

    Quote Originally Posted by drgkt View Post
    That might work with SUM...
    What do you mean by "might"? Have you tried the suggested solution? You asked for a sum formula in your OP, didn't you?

    Quote Originally Posted by drgkt View Post
    ... what if I wanted to use for example INDEX of the A1:Z13 and I want to disregard data on the last 3 rows?
    What exactly are you trying to achieve with "INDEX of the A1:Z13"?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Delete two rows after offsetting first row
    By AlexJr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-28-2017, 06:39 AM
  2. [SOLVED] Rows copying from bottom to top. need to copy top to bottom.
    By jafvortex93 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2015, 02:07 PM
  3. offsetting rows
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2015, 02:35 PM
  4. Need help deleting empty rows from a range from the bottom of the list up
    By pasqualebaldi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2014, 07:48 PM
  5. Draw bottom border on every rows from top to bottom
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-08-2013, 09:52 AM
  6. How do i loop by offsetting range by 1 row
    By blw2002 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2012, 05:04 PM
  7. How do i loop by offsetting range by 1 row
    By blw2002 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-23-2012, 04:42 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1