• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replace Indirect(Index(Match())) W/ Non-Volatile Function

SMOTTA

New Member
Hi Everyone,

I have a rather large model that calculates expected due dates for a particular type of event based on run data averaged (by month) for different units over the last 2,3, or 5 years (user selectable).

I have an array function that’s using Indirect() and Address() and Match() functions

Code:
=INDIRECT("Historical_Data!"&ADDRESS(MATCH($C8&$E8,Historical_Data!$A:$A&Historical_Data!$B:$B,0),MATCH(CE$6,Historical_Data!$A$3:$Z$3,0)))

to look up the historical average starts/hours for a particular unit during a particular month and it’s *killing* my performance.

I’m fairly certain there has to be a way to use non volatile functionality w/ multiple criteria to do the same thing, but remove the volatility and thus significantly increase performance, but I’m not sure how to do it with three different criteria (Unit ID, Model to use (2,3,5 year historical average), and month desired).


There are currently 12*2*(# Units) of these calculations and it takes an average of 196.67 seconds to calculate.


The formulas are on Projections!CD:CP and reference the Historical_Data tab.

Any help would be incredible appreciated. I have uploaded a sample file as well. It had to be redacted, but it has full functionality. Note there is only one "unit" on the sample sheet, but the actual application has several hundred.
 

Attachments

  • SAMPLE_FILE_REDACTED.xlsm
    84.2 KB · Views: 7
Try the following
CE10:
=INDEX(d,MATCH($C8&$E8,Historical_Data!$A:$A&Historical_Data!$B:$B,0),CE$3) Ctrl+Shift+Enter
CE11:
=INDEX(d,MATCH($C8&$E9,Historical_Data!$A:$A&Historical_Data!$B:$B,0),CE$4) Ctrl+Shift+Enter

Where d is a named formula referring to: =Historical_Data!$A$1:$AC$1000
adjust to suit data on the Historical worksheet

Then copy as appropriate
 
Hi ,

My opinion is that your workbook can be improved by several magnitudes if it is revised in all aspects.

1. I do not know why you have Worksheet_Change macros for every sheet. All that they do is Activesheet.Calculate ; what is the reason for this ?

2. You have some timers which are always running ; what is their function ?

3. There are several repetitive calculations , which can be simplified if you use helper cells ; you are calculating the year and month for the months ahead , and within the month and year calculation you have the repeated function EDATE ; this part can be easily put in a helper cell , and both the month and year function can use the helper cell.

4. The relationships are all over the workbook ; every worksheet has formulae referring to every other worksheet , or at least that is what it looks like ! Since Excel recalculation times are dependent on the dependency trees , inter-worksheet dependencies should be minimized , where ever possible.

As far as possible try to have all related data in the same worksheet , and let the dependencies flow in one direction , if possible ; thus if sheet 1 has dependencies on sheet 2 , try to avoid formulae on sheet 2 having dependencies on cells in sheet 1. Try to avoid long dependency chains where a formula on sheet 1 has dependencies on cells in sheet 2 , which in turn have dependencies on cells in sheet 3 ,...

If you can save your worksheet as a .xlsx file , thus removing all of the macros , do so , and then reopen the file and see the recalculation time. This can be the benchmark for improvement.

Narayan
 
I have an array function that’s using Indirect() and Address() and Match() functions to look up the historical average starts/hours for a particular unit during a particular month and it’s *killing* my performance.

Further no Narayan's great suggestions, have you considered leveraging PivotTables to do the sorts of calculations you are trying to do? Briefly looking at your file, it seems that a PivotTable could easily do much of what you are trying to do. Or at the very least, do much of the heavy lifting. (Perhaps in conjunction with the GETPIVOTDATA function and/or multiple Pivots in the event that you need a different layout than a PivotTable will allow)

The long calculation time you are experiencing is a warning that your approach is fundamentally flawed. Excel only goes that slow if you inadvertently program it to go that slow.

Furthermore, some of your formulas just plain don't make sense. For instance:
=INDEX(Inspection_History!$E$3:$E$3,MAX((Inspection_History!$D$3:$D$3=$A3)*ROW(Inspection_History!$D$3:$D$3)-2),0)

...means exactly the same as this:

=INDEX(Inspection_History!$E$3,MAX((Inspection_History!$D$3=$A3)*ROW(Inspection_History!$D$3)-2),0)

...because you've locked your formulas to absolute references. And as such makes no sense to me. Perhaps you put the wrong formula in?

If your intent is simply to find the maximum date associated with an Identifier, then again PivotTables make this trivial, and you will see recalculation times in the milliseconds.

IF you're unfamiliar with PivotTables, give Google a spin.
 
Back
Top