Improving calculation times

[Johnny C]

I've got a big forecast model, which takes about 5 secs to calculate.

There's a lot of formulae that do SUMIFS with 4-5 parameters and I want to make it more visible what it's doing so other people can use it.

is there much difference in referencing the ranges directly, or using dynamic formulae? Is the named range calculated every time it's referenced or does Excel cache the resulting range the first time?

 

[Aladin Akyurek]

Possibly is

SUMIFS(Sales,Rep,"jon")

resource deplating than

SUMIFS(A:A,B:B,"jon")

where Sales and Rep are dynamic named ranges.

 

[SpillerBD]

I would think the time to calculate is more impacted by the function and number of functions you are waiting to recalculate.
I think a Pivot Table would recalculate faster, but you get into the ying-yang between that speed and having to refresh the Pivot Table.

For the "ranges" I would prefer to use Table Fields. That provides the dynamic nature without having to call the entire Column. Any form of Named Range will help "other people" see what's going on. Same thing by using Slicers for Pivot Tables.

 

[Johnny C]

A pivot table is not an option for various reasons. Ditto tables, I've got as many tables in as I can but the mofo calculation sheet has some reasonably complex formulae in.

I'm doing 2 versions, one with a 'compact' calculation sheet with about 3000 cells with complex calculations, and another with the computation broken out into 7 individual sheets with 1 calculation split across 2,000 rows. My gut feel is the complex one will be faster as the calculation tree will be wider but shorter, plus the split calculation version has an awful lot of rows with 0 as the result of the calculation.

What I'm trying to understand, is if I use a dynamic range say 3000 times, is it recalculated every time it's referenced or is the calculation tree intelligent enough to recognise it's the same range and only calculate it once. If the calc tree is built using pointers there's a good chance it's the latter if the programmers at MS were competent.

 

[MARK858]

You haven't stated exactly what formula you are using to create your dynamic used ranges.

 

[Johnny C]

An example is

=OFFSET($A$1,1,0,COUNTA($A$2:$A$50),1)

 

[MARK858]

Not by a computer at the moment so not going to try and type a formula but try using a non-volatile function i.e. not Offset.

See the links below for help on the matter.

https://exceljet.net/lessons/how-to-create-a-dynamic-named-range-with-index

https://www.youtube.com/watch?v=vhrYQxooPBk

 

[Aladin Akyurek]

An example is

=OFFSET($A$1,1,0,COUNTA($A$2:$A$50),1)

This can be re-written...

Either as

$A$2:INDEX($A$2:$A$50,MATCH(9.99999999999999E+307,$A$2:$A$50)

for a numeric range;

Or as

$A$2:INDEX($A$2:$A$50,MATCH(REPT("z",255),$A$2:$A$50)

for a text range.

Or as

$A$2:INDEX($A$2:$A$50,COUNTA($A$2:$A$50))

if you don't have in-between empty cells.

 

[Johnny C]

Thanks Aladin, I'll replace with the last option.

 

[Aladin Akyurek]

Thanks Aladin, I'll replace with the last option.

You are welcome. COUNTA looks at every cell, something that takes time. The MATCH in the first two options do their job in a very few steps, thus very fast and they are not affected by empty cells or error values.