# How to choose an offset function starting point/reference

1. ## 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.

donald

2. ## 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.

3. ## 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. ## Re: How to choose an offset function starting point/reference

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.

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. ## 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. ## Re: How to choose an offset function starting point/reference

Hi again Pete_UK,
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. ## 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. ## 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?"
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.

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