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