Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
When is a defined Name actually calculated?
Excel optimizes its calculation engine to provide the fastest response which
equals minimal calculation. I have no special knowledge of how the calculation engine works or how Excel handles the calculation of names. I recall there is a KB article that talks a bit about how Excel recalculates (see link below) and Charles has done a lot of empirical research and formed opinions on how it operates, so perhaps you can get a better answer from him. Unless you are having some type of problem, I wouldn't waste a lot of hand wringing over it. Did you have an alternative in mind? Anyway, here is an article that provides as much information as I am aware of on how Excel recalculates: http://msdn.microsoft.com/library/de...c_xlrecalc.asp -- Regards, Tom Ogilvy "onedaywhen" wrote in message om... Tom, Charles, Thanks you your collective suggestion of the COLUMN formula, one which I hadn't considered. The thing is, I also want to anticipate the situation where the value 'Ref' in C1 is cut and pasted to another cell in row 1:1. The COLUMN formula wouldn't work but the MATCH one would. Apologies for not making this clear (I only just consciously considered it myself, however!) So I'm happy with my Name's formula but my question was about when it gets calculated. Tom, I'm sorry, I'm not sure I fully understand your reply. Just to clarify, my concern isn't that it won't be calculated, rather I'm concerned it's being calculated too often. I *think* my MATCH formula is calculated only when something in row 1:1 is changed, including the insertion of a column. Perhaps it is calculated when the workbook is first open. Both these cases are OK, they don't happen very often. But I wondered whether the formula gets calculated every time the Name is used in another Name. Here's some more of my Names: Data: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1)) ColRef =MATCH("Ref",Sheet1!$1:$1,0) Refs =OFFSET(Data,0,ColRef-1,,1) So, each time I use the 'Refs' Name does Excel recalculate the 'ColRef' Name? Or does Excel store the value of 'ColRef'' and only recalculate the formula when a change is made that affects row 1:1? Thanks again. "Tom Ogilvy" wrote in message ... Define your formula to be =COLUMN(Sheet1!$C$1) It should be updated whenever it is appropriate Your suggested use of match should work as well. Again, since you supply the dependency information in the function arguments, I would trust Excel to update it appropriately. -- Regards, Tom Ogilvy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
When is a defined Name actually calculated?
As I said befo I believe a defined name is only calculated whenever a
cell that directly refers to it is calculated, but if you have multiple cells referring directly to a name, and you recalculate all these multiple cells, then the name will be recalculated multiple times. So defined names do not work exactly like cells: its more like the name gets substituted by its refersto whenever a formula containing the name gets calculated. So in general its is not efficient to put calculation-intensive functions inside names: use a spare cell somewhere instead, and if you want to use names then give the spare cell a name. Charles Williams www.DecisionModels.com "Tom Ogilvy" wrote in message ... Excel optimizes its calculation engine to provide the fastest response which equals minimal calculation. I have no special knowledge of how the calculation engine works or how Excel handles the calculation of names. I recall there is a KB article that talks a bit about how Excel recalculates (see link below) and Charles has done a lot of empirical research and formed opinions on how it operates, so perhaps you can get a better answer from him. Unless you are having some type of problem, I wouldn't waste a lot of hand wringing over it. Did you have an alternative in mind? Anyway, here is an article that provides as much information as I am aware of on how Excel recalculates: http://msdn.microsoft.com/library/de...us/dnexcl2k2/h tml/odc_xlrecalc.asp -- Regards, Tom Ogilvy "onedaywhen" wrote in message om... Tom, Charles, Thanks you your collective suggestion of the COLUMN formula, one which I hadn't considered. The thing is, I also want to anticipate the situation where the value 'Ref' in C1 is cut and pasted to another cell in row 1:1. The COLUMN formula wouldn't work but the MATCH one would. Apologies for not making this clear (I only just consciously considered it myself, however!) So I'm happy with my Name's formula but my question was about when it gets calculated. Tom, I'm sorry, I'm not sure I fully understand your reply. Just to clarify, my concern isn't that it won't be calculated, rather I'm concerned it's being calculated too often. I *think* my MATCH formula is calculated only when something in row 1:1 is changed, including the insertion of a column. Perhaps it is calculated when the workbook is first open. Both these cases are OK, they don't happen very often. But I wondered whether the formula gets calculated every time the Name is used in another Name. Here's some more of my Names: Data: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1)) ColRef =MATCH("Ref",Sheet1!$1:$1,0) Refs =OFFSET(Data,0,ColRef-1,,1) So, each time I use the 'Refs' Name does Excel recalculate the 'ColRef' Name? Or does Excel store the value of 'ColRef'' and only recalculate the formula when a change is made that affects row 1:1? Thanks again. "Tom Ogilvy" wrote in message ... Define your formula to be =COLUMN(Sheet1!$C$1) It should be updated whenever it is appropriate Your suggested use of match should work as well. Again, since you supply the dependency information in the function arguments, I would trust Excel to update it appropriately. -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '50290': Application-defined or object-defined erro | Excel Discussion (Misc queries) | |||
SUMIF with defined name as calculated value | Excel Discussion (Misc queries) | |||
"User-defined type not defined" message in Excel | Excel Discussion (Misc queries) | |||
When is a defined Name actually calculated? | Excel Programming | |||
VBA - Application-defined or object-defined error | Excel Programming |