Defined Names and calculation Speed: What's more efficient formula with names or no names

Sorry for the length skip to cliff notes at bottom if you don't want to read it all:

I am trying to determine what is more efficient, creating more names that have the cells and ranges i want referenced and putting them in the formula or leaving the reference directly in the formula without the names:

I've been reviewing the decision models site and searching but can't find a definitive answer and am still not clear on exactly when or under what conditions named ranges are calculated: It looks to be every time referenced so if referenced twice per formula it would calculate twice per formula(?). However, is that even if it is a static reference ie: A1:D16, or is that only if the named ranges are dynamic or is it if they have any formula in the "refers to" box regardless of how dynamic it is.

Are all formulas that refer to a named range evaluated on workbook Open/close/Save

or Are all named ranges evaluated just on work book Open/Close/Save

Basically under what other conditions are named ranges evaluated/calculated

PLEASE SEE ATTACHED WORKBOOK, Makes the below much clearer

Users audibility is not an issue here, I only care about raw performance at the moment.


To the Problem:

I have the following names currently (I have to have these names, can't get rid of them)

TPDATA- Refers to A1:D16 (Static reference but the "refers to" is updated through a macro that sets it to the size of the data) In real use these ranges will be much larger, along the lines of A1:AB15000 etc
TPHEADERS- Refers to A1:D1 (Same as above except the "refers to" is set to be only the first row of the data.)

So I have the following formula: (See attached workbook for more Clarity)


=IF(COUNTIFS(INDEX(TPDATA, 0, MATCH(Table!$A$8, TPHEADERS, 0)), Table!$A9, INDEX(TPDATA, 0, MATCH(Table!$B$8, TPHEADERS, 0)), TEXT(Table!H$8, "yyyymm"))=0, "", SUMIFS(INDEX(TPDATA, 0, MATCH(Table!$B9, TPHEADERS, 0)), INDEX(TPDATA, 0, MATCH(Table!$A$8, TPHEADERS, 0)), Table!$A9, INDEX(TPDATA, 0, MATCH(Table!$B$8, TPHEADERS, 0)), TEXT(Table!H$8, "yyyymm")))

These are the pieces I am concerned about:

INDEX(TPDATA, 0, MATCH(Table!$A$8, TPHEADERS, 0))
&
INDEX(TPDATA, 0, MATCH(Table!$B$8, TPHEADERS, 0))

In my spreadsheet each of these pieces of the formula is referenced 2 times per overall formula, and each work package has 672 of these overall formulas. So basically each piece is referenced 1344 times per work package for 2688 references total. My spreadsheet might have anywhere from 2 to hundreds of work packages depending on user needs.

So i am in optimization mode.


I've already kicked the "MATCH(Table!$A$8, TPHEADERS, 0)" part of the formula to a helper cell (G1) (and the same for the other piece) and am now referring to that cell instead of the match function so i am only calculating the match function once per worksheet rather than 1344 times per workpackage:

This gives me this formula:


INDEX(TPDATA, 0, $G$1)
&
INDEX(TPDATA, 0, $H$1)

Where G1 = MATCH(Table!$A$8, TPHEADERS, 0)
& H1 = MATCH(Table!$B$8, TPHEADERS, 0)

This gives me this overall formula:

=IF(COUNTIFS(INDEX(TPDATA, 0, $G$1), Table!$A9, INDEX(TPDATA, 0, $H$1), TEXT(Table!D$8, "yyyymm"))=0, "", SUMIFS(INDEX(TPDATA, 0, MATCH(Table!$B9, TPHEADERS, 0)), INDEX(TPDATA, 0, $G$1), Table!$A9, INDEX(TPDATA, 0, $H$1), TEXT(Table!D$8, "yyyymm")))


I will also pull out the other match and text function to a helper cell so they are only calculated once per worksheet.

However, for now is this the limit of the optimization of those pieces referenced above or would creating a named range defined as say:

CRITRANGE1 = INDEX(TPDATA, 0, $G$1)
CRITRANGE2 = INDEX(TPDATA, 0, $H$1)


and then replacing the Index(......) pieces with the named range to get this overall formula:

=IF(COUNTIFS(CRITRANGE1, Table!$A9, CRITRANGE2, TEXT(Table!D$8, "yyyymm"))=0, "", SUMIFS(INDEX(TPDATA, 0, MATCH(Table!$B9, TPHEADERS, 0)), CRITRANGE1, Table!$A9, CRITRANGE2, TEXT(Table!D$8, "yyyymm")))

...be more efficient than leaving the formula as is and just using the helper cells with no additional names.

CLIFF NOTES:

When are named ranges evaluated and

what is more efficient as far as performance/speed goes:

This:

=IF(COUNTIFS(INDEX(TPDATA, 0, $G$1), Table!$A9, INDEX(TPDATA, 0, $H$1), TEXT(Table!D$8, "yyyymm"))=0, "", SUMIFS(INDEX(TPDATA, 0, MATCH(Table!$B9, TPHEADERS, 0)), INDEX(TPDATA, 0, $G$1), Table!$A9, INDEX(TPDATA, 0, $H$1), TEXT(Table!D$8, "yyyymm")))

or this:

=IF(COUNTIFS(CRITRANGE1, Table!$A9, CRITRANGE2, TEXT(Table!D$8, "yyyymm"))=0, "", SUMIFS(INDEX(TPDATA, 0, MATCH(Table!$B9, TPHEADERS, 0)), CRITRANGE1, Table!$A9, CRITRANGE2, TEXT(Table!D$8, "yyyymm")))



See my attached workbook for a clearer understanding of how the formulas work. The attached work book is my concept test bed or prototype so it is smaller in scope than the actual sheet I will be using is. My actual sheet has a 14 year time range hence why i am not using that to test stuff out.


Any help is greatly appreciated

Cross posted here: http://www.mrexcel.com/forum/showthread.php?t=635237