=OFFSET(BC153, -1, 0)
Why would yer?
What advantage does this have over ‘=BC152’?
oh, unless you are deleting stuff and want to avoid #Refs maybe?
Others?
cheers
Simon
- Comment
- Reblog
-
Subscribe
Subscribed
Already have a WordPress.com account? Log in now.
Smurf on Spreadsheets 
Tuesday, 5th January, 2010 at 11:52 am |
I would avoid Offset wherever possible as it is volatile.
Use Index instead
Tuesday, 5th January, 2010 at 2:08 pm |
I use OFFSET all the time and I especially like the ability to reference cells any direction (using the minuses) from the cell being referenced.
I use this with SUM() to give me a user-defined range. i.e.
=SUM(A1,OFFSET(A1,0,CurMth))
will give me the total of a range that starts at A1 and goes over as far as the number in CurMth indicates. So if the user has a list of Months the March choice would be the 3rd in the list and that would be the vaue in CurMth and so the calc would give you the first 3 month’s total. In reporting of Financial and Production numbers this is REAL handy for me.
As far as its being Volatile – I rely on that fact. I have never seen any performance hits from using OFFSET on any large file. If so the benefit would more than offset (ironic eh?) the calc milliseconds.
Dick
Tuesday, 5th January, 2010 at 3:09 pm |
Sorry it should have been:
=SUM(A1:OFFSET(A1,0,CurMth))
Dick
Tuesday, 5th January, 2010 at 2:34 pm |
I use OFFSET quite a bit too, it works well with long lists of dates where the ability to count back and forwards is invaluable.
Tuesday, 5th January, 2010 at 3:36 pm |
I agree with sam – better to avoid volatile functions. For me that preference is due to a 20MB model someone else wrote which I have to use that contains over 300K volatile function calls (more INDIRECT calls than OFFSET calls). When you reach 10K such calls recalc slows enough to be noticed. When you reach 100K such calls, it’s like you’re in a time machine at least a decade into the past.
So instead of SUM(A1:OFFSET(A1,0,CurMonth)) [sure that shouldn’t have been SUM(A1:OFFSET(A1,0,CurMonth-1)) ?], better to use
SUM(A1:INDEX(1:1,CurMonth))
As for Simon’s original example, as long as there were proper separation of user entry/work area and result area, the entry/work area either shouldn’t have any formulas or fixing them should be the user’s problem when the user deletes anything, and if the entire entry/work area worksheet’s grid were named USERRNG, it could be referenced relatively in another worksheet using formulas like
=INDEX(USERRNG,ROW()+rowoffset,COLUMN()+coloffset)
or, e.g., if cell G7 in the result worksheet would reference cell B2 in USERRNG, etc,
G7: =INDEX(USERRNG,ROWS($B$2:B2),COLUMNS($B$2:B2))
Note that $B$2:B2 is a range in the results worksheet rather than in USERRNG. The user can insert/delete anything they want in USERRNG, it’ll have no affect on these INDEX formulas. ‘Course that begs whether it’s wise to allow users to insert or delete anything when there are potentially many levels of dependent formulas.
Tuesday, 5th January, 2010 at 11:03 pm |
“[sure that shouldn’t have been SUM(A1:OFFSET(A1,0,CurMonth-1)) ?]”
YES – me bad ; That’s what I get for rushing.
I should also add that I use the long frmat of our friend OFFSET() all the time too in =SUM(OFFSET(=sum(offset(A1,rows,cols,height,width)) format. This is a wonderful formula in my books.
As far as voliatility, I want volatility and I also like the idea that I am only referencing a single address so I use relative or mixed references to drive the paraeters of the formulae.
Sorry but I use all the other thngs you suggest in many other scenarios and I don’ care about the auditability of the formulas because they’re MY formulas and even they were done by someone else these formulas bring a good level of structure to the model when used anyway so thay’re fine by me.
End of thread for me :-)
Dick
Wednesday, 6th January, 2010 at 1:10 am
‘I want volatility’
You clearly don’t use spreadsheets with thousands of volatile function calls. When overused, they make a very persuasive case for ditching Excel entirely and using real grid controls.
‘I don’ care about the auditability of the formulas because they’re MY formulas’
Either you never make mistakes or your clients just assume that’s the case?
Tuesday, 5th January, 2010 at 4:00 pm |
I also love OFFSET. I think the volatile part is still an issue to be considered, but not on most models.
I’m sure nobody here is struggling to understand OFFSET but If you know people who does, you might like to have them take a look at this tutorial I did.
OFFSET Function Tutorial
There is an XLS file they can download at the end of the post that can help visualize how the function works.
Sebastien
Tuesday, 5th January, 2010 at 4:05 pm |
II am reluctant to add this BUT ….
When I teach clases in advanced Excel development I usually admit that OFFSET is my favourite Function in Excel. That’s sick – having a favourite Function – maybe I should get a life :-).
Despite Harlan’s detailed explanation I think I’ll stick with OFFSET() thank you ….
Dick
Tuesday, 5th January, 2010 at 5:23 pm |
I’m very much in the pro-OFFSET camp – great to create expanding ranges for charts that I can add an extra year to the range just by changing the value of one cell in the spreadsheet
Like Dick, it’s something that even experienced users might not have come across and thus is great for teaching (though the complete syntax is rather complicated to grasp by beginners)
Tuesday, 5th January, 2010 at 6:27 pm |
I am in the anti-OFFSET camp – but have to use it occasionally.
Partly because of volatility and performance issues (which I agree do not become an issue unless OFFSET is used a lot or the model is large).
But also because of how it affects precedence and dependence tracking. I discovered a long time ago – may not be true today but it has become habit now – that prec/dep checking did not include cells that have OFFSET in them somewhere – and it does not have to be part of the reference that you are checking (ie it could be further along in the formula but it still does not find the reference). This is quite a hinderence when you are reviewing someone else’s model – trying to figure out where the logic is flowing, when you can not find out all the cells that reference the cell of interest because they have used OFFSET and INDIRECT.
–Charlie
Tuesday, 5th January, 2010 at 7:10 pm |
There is still a problem with the precedence and dependence tracking.
I overcome this limitation by putting the whole expected range in the reference part of the OFFSET function and then specify the height and width to shrink the result.
e.g.: =OFFSET($A1:$Z1,0,12,1,1) instead of =OFFSET($A1,0,12)
It uses the top left cell anyway for the calculation but in this case Z1 would become a precedent of the cell with the formula
Sébastien
Tuesday, 5th January, 2010 at 7:46 pm
But why bother using
=OFFSET($A1:$Z1,0,12,1,1)
when you could simply use the NONVOLATILE
=INDEX($A1:$Z1,13)
?
OFFSET is generally problematic, but if you’d NEVER address cells outside its 1st argument range, there’s NO GOOD REASON to use it rather than INDEX.
Tuesday, 5th January, 2010 at 7:59 pm |
OFF TOPIC
Just checked the Excel Team blog. They’ve changed the @#$%&*! format again! If only they’d spend as much time adding USEFUL features to Excel. I guess the beta must be going so swimmingly that there’s so little to do they might as well needlessly change their blog.
Then again, they may just be living MSFT’s current mission statement: CHANGE FOR CHANGE’S SAKE! Or MSFT’s current vision statement: IF IT AIN’T BROKE, LET THE UI TEAM GET THEIR HANDS ON IT!
Tuesday, 5th January, 2010 at 10:08 pm |
hmm
maybe I wasn’t clear, I was posting about the pointlessness of the above formula, tomorrows post is when I was planning the religious war.
bloody typical of you lot to start without me! ;-)
I use SUM(OFFSET(A1,x,y,r,c)) every now and then
Harlan, if they just keep messing with the colours, its pointless but I can live with it. But maybe there are bigger changes afoot in Officeland. Maybe thats why the Excel team don’t have time to do their own posts?
Tuesday, 5th January, 2010 at 10:22 pm |
{sigh} Slow off the mark – as usual – Simon ;)
Wednesday, 6th January, 2010 at 1:23 am |
“‘I want volatility’
You clearly don’t use spreadsheets with thousands of volatile function calls. When overused, they make a very persuasive case for ditching Excel entirely and using real grid controls.”
I frankly don’t use UDF’s hardly at all. I rely on native functions and sometimes I will actually use code to refresh the model one cell at a tme based on some flag – leaving just the values in the cell. It’s pretty simple to implement with a little planning and leaves as small a footprint as possible.
‘I don’ care about the auditability of the formulas because they’re MY formulas’
Either you never make mistakes or your clients just assume that’s the case?”
Pretty much :-) …. By using OFFSET() functions I am actually able to use the least number of formulas and copy them around as necessary, thus reducing the chance of error. Efficient sheet and formula design reduces errors. Then I test the results and I lock it down…
Frankly I don’t do “models”in the classic sense – rather I do Excel for Reporting mostly so I avoid large spreadsheets full of formulas (formulae?) that take calc time and disk space.
I just do a different type of spreadsheet than it seems most here do anyway/
It’s worked for me for 25 years – since before 1-2-3 with VisiCalc.
I know this must upset you but that’s the way it is :-).
Dick
Wednesday, 6th January, 2010 at 6:03 am |
I don’t mean udfs. I mean volatile BUILT-IN functions such as OFFSET. For example, the next two formulas return the same value.
=SUM(OFFSET(A1,8,5,4,2))
=SUM(INDEX(A1:Z100,8+1,5+1):INDEX(A1:Z100,8+1+4,5+1+2))
Note: I chose not to reduce the index arguments in the second in order to emphasize the relationship between OFFSET and INDEX. Whenever the value in any cell in A1:Z100 changes, Excel will recalculate both formulas.
However, if no cell in A1:Z100 contains formulas referring to cell CG789, if the value in cell CG789 changes, Excel will recalculate the first formula but not the second. If a change or even simple re-entry of any cell occurs, Excel will recalculate the first formula, but Excel won’t recalculate the second formula unless cells in A1:Z100 are affected by changes in those other cells.
In the workbook I already mentioned, I could be entering values in one worksheet, and all +100K formulas containing the +300K volatile function calls recalc (as well as all other formulas which refer to the cells containing those formulas) even if my entries have no effect on those other formulas.
It’s the NUMBER of volatile function calls that matter. If you have fewer than 2K cells containing formulas calling volatile functions, you likely won’t notice or care. OTOH, use of volatile functions in more than 5K formulas is, from my perspective, poor implementation.
I suspect this is the difference between spreadsheets for reporting and spreadsheets for decision support/analysis. Most of the spreadsheets I use involve at least an order of magnitude more formula cells than data entry and static label cells combined. I haven’t made reports in 12 years.
Wednesday, 6th January, 2010 at 6:05 am |
‘course I screwed up the second formula, which should be
=SUM(INDEX(A1:Z100,8+1,5+1):INDEX(A1:Z100,8+4,5+2))
Wednesday, 6th January, 2010 at 7:30 am |
“As far as its being Volatile – I rely on that fact. I have never seen any performance hits from using OFFSET on any large file. If so the benefit would more than offset (ironic eh?) the calc milliseconds.”
There is a huge performance hit.
Please try this
a) Define a dynamic Range : Client :=$A$1:Index($A:$A,Counta($A:$A))
Fill the column with some data upto row 65536
b) In Cell B1:B10 = Type the formula : Counta(Client)
Calculate the Calculation Time (use the excellent timer available as Ross’s webiste)
Also Calculate the recalculation Time
c) Redefine the dymamic range: Client :=$A$1:Offset($A$1:COUNTA($A:$A)-1,0)
Now find out the Calculation Times and Recalculation Times
You will notice hardly any difference in the Calculation times
But the recalculation times will significantly different in case of OFFSET- to the tune of 25-30 times slower compared Index.
I can e-mail you a sample spreadsheet if you wish to test it your self
Wednesday, 6th January, 2010 at 9:42 am |
OFFSET itself is an extremely fast function: I have a workbook with over 250000 OFFSET formulae which FullCalcs in 0.1 seconds.
In Dynamic range type formulae its the COUNTA that uses the calculation time (so do the COUNTA once and reference the result in many OFFSET formulae).
Interestingly using INDEX as the second part of a range argument (A$2:INDEX(A$2:A$8,7,)) makes it semi-volatile: it gets recalculated at workbook open time (presumably as part of the dependency chain rebuild). This can slow down workbook open. Also I think INDEX can use more memory than OFFSET.
But I agree that using volatile functions needs to be done with care.
Wednesday, 6th January, 2010 at 10:55 am |
what about this I just found:
=OFFSET(BY160,0,0)
cewl
Wednesday, 6th January, 2010 at 12:35 pm |
Charles.
I used your VolatileFunc.xls to test volatility of Index
The counter did not change on F9.
But is there a way to determine if Index recalculates on Workbook open
Wednesday, 6th January, 2010 at 3:09 pm |
Its only that form (the second part of a range argument A$2:INDEX(A$2:A$8,7,) ) of index that is semi-volatile, the standard form is not.
I think it was first noticed because a workbook containing it requested a save when closing even when there had been no recalc.
I think you can use a Calculate event message to determine when the recalc at open occurs, then compare different forms of Index.
I must have a test workbook somewhere …
Wednesday, 6th January, 2010 at 3:23 pm |
OFFSET itself may be fairly fast on recalc since it just returns range references. It’s how the resulting range reference is used that’s usually the problem. If OFFSET were returning references to 1000-cell ranges and those were used as arguments to other, slowish functions, the result is slow overall recalc.
Wednesday, 6th January, 2010 at 4:27 pm |
Harlan,
“If OFFSET were returning references to 1000-cell ranges and those were used as arguments to other, slowish functions, the result is slow overall recalc.”
I agree, but sometimes using OFFSET to return a small calculated subset range to an expensive formula is better than using the expensive formula on the full-sized range.
As you point out INDEX is preferable in many cases since its non-volatile, but I have found that on large workbooks it tends to get to the 64K dependency limit (in versions prior to XL2007) faster.
Wednesday, 6th January, 2010 at 9:45 pm |
“…MSFT’s current mission statement: CHANGE FOR CHANGE’S SAKE! Or MSFT’s current vision statement: IF IT AIN’T BROKE, LET THE UI TEAM GET THEIR HANDS ON IT!”
ROFL – sorry Harlan that just cracked me up.
Thursday, 7th January, 2010 at 10:50 pm |
[…] Simon’s blog, sam comments regarding OFFSET vs. INDEX: There is a huge performance hit. … You will notice hardly any […]
Saturday, 23rd January, 2010 at 1:18 pm |
quote Simon:
what about this I just found:
=OFFSET(BY160,0,0)
cewl
It can be worse. I just found
=HYPERLINK(“#”&CELL(“address”,OFFSET($X$1,0,0)),”x”)
cewwler!!
Monday, 25th January, 2010 at 10:59 am |
A curiosity: the formula =LOOKUP(2,{1,2},A1) behaves like a nonvolatile version of =OFFSET(A1,0,1)
Try entering a value in A2 then enter the formula above in A1. The formula will return the number in A2 but it will not update again when A2 is changed unless you force a recalculation. I guess this is because A2 is not referred to in the formula so does not form part of the calculation chain.
Reopening the workbook restores the same different values in A1 and A2, but inserting :INDEX(A1,1) or :IF(1,A1) after A1 will cause the formula to recalculate after opening so that A1 equals A2.
This demonstrates Charles’s point above that dynamic ranges recalculate each time the workbook is opened.
Monday, 25th January, 2010 at 11:29 am |
Oops, change A2 to B1 in the description above.
Wednesday, 6th November, 2013 at 7:08 am |
[…] Originally Posted by njbeancounter sweet!!! thank you In case you might start wondering… Offset WTF | Smurf on Spreadsheets […]