+ Reply to Thread
Results 1 to 9 of 9

How to choose an offset function starting point/reference

  1. #1
    Registered User
    Join Date
    02-23-2022
    Location
    choctaw ok
    MS-Off Ver
    version 2201
    Posts
    5

    How to choose an offset function starting point/reference

    I understand that the offset function enables one to return a range of cells. The formula (offset) uses 5 arguments. The first argument (Reference) seems, to me, to be random. I can literally use any cell in a sheet, so long as I type the correct second (row) and third(col) arguments.
    What is the proper starting point (the "reference" argument) ?
    If i have numbers (data) in the range (B2:D4) I can sum it all by using =sum(offset(a1, 1, 1, 3, 3)). Or I could use =sum(offset(b2, 0, 0, 3, 3)). Or I could use =sum(offset(e10, -8, -3, 3, 3). Or I could use =sum(offset(z100, -98, -24, 1, 1))
    WHAT SHOULD I USE?
    They all return the same thing. Even if I use the countA function in place of the last two arguments, for example =sum(offset(a1, 1, 1, countA(Sheet1!$B:$B), countA(Sheet1!$2:$2)))
    Please explain the something I am obviously misunderstanding.

    Thanks in advance,
    donald

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    Re: How to choose an offset function starting point/reference

    Hello donman2318. Welcome to the forum.

    From here it looks like all of them would work. OFFSET is so flexible and amazing you can do a lot of things with it. It really all depends on what you are trying to do and what you start with.

    Combine it with other functions like SUBTOTAL and it could keep you busy for days examining the possibilities.

    Oh BTW: in your profile version 2201 is a release number. The MS-Off Ver can be found under File > More... > Account in the upper right under Product Information.
    Dave

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,607

    Re: How to choose an offset function starting point/reference

    You should also be aware that the OFFSET function is volatile, which means it will be recalculated every time there is a change on the workbook, even if that change has nothing to do with the cell(s) containing the OFFSET function. If you have a lot of these functions then the workbook can become very sluggish. You can achieve the same effect using INDEX, which is not volatile.

    Hope this helps.

    Pete

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    Re: How to choose an offset function starting point/reference

    Administrative Note:

    Members will tailor the solutions they offer to the version (NOT release number such as 2201) of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Registered User
    Join Date
    02-23-2022
    Location
    choctaw ok
    MS-Off Ver
    version 2201
    Posts
    5

    Re: How to choose an offset function starting point/reference

    I found Microsoft Office Professional Plus 2016. Is this the MS-Off Ver? As in, the version of excel I use is 2016?

  6. #6
    Registered User
    Join Date
    02-23-2022
    Location
    choctaw ok
    MS-Off Ver
    version 2201
    Posts
    5

    Re: How to choose an offset function starting point/reference

    Hi again FlameRetired,
    Thanks for your quick reply. I hope my learning path is quickened with my membership.
    I must be misunderstanding a very fundamental concept, because I am still completely baffled with WHY one chooses a given reference/starting point. From my original example I've provided 4 "options" for the reference (a1, b2, e10, z100), what would you use? Surely not e10 or z100. However, the choosing of a1 or b2 must have a logical reason.
    I just wish I could get my head around this.
    Thanks again,
    donald

  7. #7
    Registered User
    Join Date
    02-23-2022
    Location
    choctaw ok
    MS-Off Ver
    version 2201
    Posts
    5

    Re: How to choose an offset function starting point/reference

    Hi again Pete_UK,
    Thanks for your quick reply warning me of the volatility of OFFSET. Please tell me if you have "already" seen my reply to FlameRetired. I'm not sure if my repsonses are "reply all".
    Can you help to explain my question of choosing a reference/starting point?
    I must be misunderstanding a very fundamental concept, because I am still completely baffled with WHY one chooses a given reference/starting point. From my original example I've provided 4 "options" for the reference (a1, b2, e10, z100), what would you use? Surely not e10 or z100. However, the choosing of a1 or b2 must have a logical reason.
    I just wish I could get my head around this.
    Thanks again,
    donald

  8. #8
    Registered User
    Join Date
    02-23-2022
    Location
    choctaw ok
    MS-Off Ver
    version 2201
    Posts
    5

    Re: How to choose an offset function starting point/reference

    Hi FlameRetired again,

    Can you give me two examples where choosing a1 would be best, and the other where choosing b2 would be best.
    I must admit I'm guessing that once I do "get my head around" this I will be laughing at myself for my questions
    thx,
    donald

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    Re: How to choose an offset function starting point/reference

    donman2318: "I found Microsoft Office Professional Plus 2016. Is this the MS-Off Ver? As in, the version of excel I use is 2016?"
    Yes, your profile should read 2016
    donman2318: "From my original example I've provided 4 "options" for the reference (a1, b2, e10, z100), what would you use?"
    I would say that you might take into consideration readability, as in what would make the most sense to a person that may have to change/troubleshoot the formula in the future, with little or no foreknowledge of how the spreadsheet works.
    That said and based on Pete's comment about the OFFSET function the best choice might be something like: =SUM(B2:INDEX(B2:Z25,COUNT(B2:B25),COUNT(B2:Z2)))
    Which would be both flexible and nonvolatile.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Offset Function For Another Sheet: Reference point error
    By Vasare in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-15-2021, 03:18 PM
  2. [SOLVED] Countif from a starting point using offset
    By JeffGrant in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-29-2021, 02:42 AM
  3. Offset Formula - Can The Reference Point Have A Match Function?
    By Dan1027 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-15-2020, 02:04 PM
  4. Offset formula starting point (up/down)
    By Daniel86 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-22-2018, 02:40 PM
  5. Replies: 3
    Last Post: 08-14-2017, 06:26 AM
  6. Replies: 10
    Last Post: 11-21-2015, 12:06 PM
  7. OFFSET with a moving starting reference
    By StuFromSaturday in forum Excel General
    Replies: 12
    Last Post: 08-19-2014, 08:34 AM

Tags for this Thread

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