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?

 

[Johnny C]

Good point.

 

[Fazza]

instead of having formulas at all, consider having VBA make the defined names

such as
range("A2:A50").name = "Codes"
or more complex (on LHS) as required

or if the data is in a table with headers in row 1, an option to consider is using (SQL type) queries instead of formulas.

 

[Johnny C]

Unfortunately the rules in this place are 'No macros in models', all models have to pass a FAST test. They had a bad experience with a contractor who built stuff in VBA which no-one else could understand and left.

I'm a contractor so the model has to be understandable by a team with very limited Excel skills when I leave, I'm slowly introducing them to dynamic ranges and tables but it's going to take a long time, most can do VLOOKUP but INDEX/MATCH is considered advanced Excel wizardry.

 

[Fazza]

I suspect SUM is the usual limit of many users.
VLOOKUP is considered advanced by many people.