Using an Array formula
is faster but still too slow. |
An alternative approach is to use multiplicative
AND’s in the array formula:
{SUM(($A$4:$A$10003=$A4)*(L$4:L$10003=”AA”)*
(D$4:D$10003))}
This calculation is slightly faster at 723 seconds for a single
column against 744, but still far too slow. |

Using SUMPRODUCT is faster
than an array formula, but not enough. |
Or you can use SUMPRODUCT, which does not need
to be array-enetered:
SUMPRODUCT(--($A$4:$A$10003=$A4),--(L$4:L$10003="AA"),D$4:D$10003)
SUMPRODUCT is faster than the array formula, 711 seconds compared
to the 723, but its still too slow. |

Calculate once, store
the results and then lookup the results many times.
89 Seconds. |
With this calculation all routes with the same
origin have the same answer: so we can reduce the calculation
time significantly by only doing it once for each of the 100
unique origins, and then looking up the answer for each of the
10000 routes.
The following formulae do the calculation for one year at origin
level in 17.8 seconds, or 5 years in 89 seconds. This is much
better but still too slow.
{SUM((RouteCalc!$A$4:$A$10003=$A5)* (RouteCalc!$L$4:$L$10003="AA")*
(RouteCalc!D$4:D$10003))}
SUM((RouteCalc!$A$4:$A$10003=$A5)* (LEFT(RouteCalc!$B$4:$B$10003,3)="OTH")*
(RouteCalc!D$4:D$10003))
SUMIF(RouteCalc!$A$4:$A$10003,A5,RouteCalc!D$4:D$10003)
E5/(G5-F5) |

Dynamically calculate
subset blocks to be used in the calculation.
0 .7 seconds |
These formulae do not exploit the fact that the
routes are sorted by destination within origin, so that the
block of rows we need to do the SUM over for each origin is
contiguous and much smaller than 10000 rows. However we need
to be aware that the origin and destination Sites could change.
We can calculate the range to use in the formulae using three
extra columns:
The number of routes for this origin (numrows) is:
COUNTIF(RouteCalc!$A$4:$A$10003,A5)
The row number of the last route for this origin is the start
row for this origin plus the number of routes.
The row number if the first route for the next origin (startrow)
is the previous origin’s last route row number plus 1.
The coverage formulae then do not need array formulae and can
use SUMIF and OFFSET as follows:
SUMIF(OFFSET(RouteCalc!$K$1,startrow,1,numrows,1),
"AA",OFFSET(RouteCalc!$C$1,startrow,1,numrows,1))
SUMIF(OFFSET(RouteCalc!$B$1,startrow,0,numrows,1),
"oth*",OFFSET(RouteCalc!$C$1,startrow,1,numrows,1))
SUM(OFFSET(RouteCalc!$C$1,startrow,1,numrows,1))
E5/(G5-F5)
Calculation time for all 5 years is now 0.673 seconds. |

Dynamically calculate
a small subset block for the COUNTIF.
0 .14 seconds |
Analysis of these calculations shows that over
80% of the time is being used by the COUNTIF formulae. We can
reduce this using the fact that there will never be more than
300 origin Sites in this model, (it would be better still to
count the number of unique Sites).
COUNTIF(OFFSET(RouteCalc!$A$1,PreviousOriginLastRow,0,300),A7)
This reduces the calculation time to 0.14 seconds. |