How does Excel calculate named formulas and materialized named ranges?

Excel favors eager evaluation in most situations. In your two examples, the timings were about the same. The example using trim range was the quickest.

ManualAllocRng =
OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),5);


// Average of 5 timings	
// Elements	1.25 million
// TestAλ	0.21
// TestBλ	0.21
// TestCλ	0.18


TestAλ = 

LAMBDA(
    x,
LET(
  rng,      ManualAllocRng,
  a,        DROP(rng,,2),
  b,        TAKE(rng,,-2),
  result,   HSTACK(a,b)+x,
  result
));

TestBλ = 

LAMBDA(
    x,
LET(
  a,        DROP(ManualAllocRng,,2),
  b,        TAKE(ManualAllocRng,,-2),
  result,   HSTACK(a,b)+x,
  result
));

TestCλ=

LAMBDA(
    x,
LET(
    rng,    Sheet1!$A:.$E,
    a,      DROP(rng,,2),
    b,      TAKE(rng,,-2),
    result, HSTACK(a,b)+x,
    result))

The way in which the first two examples are authored may have an effect on the calculation speed, but it really depends on what you're doing with the arrays.  Excel seems to prioritize allocating memory to spill re-sizing but it's not easy to re-produce the situation.  I've seen functions take 12 seconds to calculate even with the function terminating within an IF even before the LET block comes in.  There are ways to stop the eager evaluation and even speed up functions with some well-placed thunks. In the examples A/B above, there were no differences in the calc speed.

When working with named formulas in Excel—especially those built using AFE functions like OFFSET, INDEX, or dynamic arrays—it’s important to understand how Excel handles their evaluation. Named formulas are not “materialized objects”; they behave more like reusable expressions that are re-executed whenever referenced.

1. Named formulas are recalculated every time they are used.
Excel does not cache or materialize named formulas in a reliable or persistent way. Each reference to a named formula triggers a fresh evaluation unless you explicitly store its result in a variable inside a LET.
In your first version, storing ManualAllocRng inside a LET means it is calculated once and reused.
In your second version, calling ManualAllocRng twice means Excel evaluates the named formula twice.

2. Spill ranges behave differently depending on where they come from.
A spilled formula placed directly on a worksheet (e.g., =FILTER(...)) is calculated once; all references to A1# simply read the already-computed spilled range.
But a named formula that produces a spill does not materialize anywhere—each reference recalculates it.

3. The mismatch between sheet-based spilling and Evaluate() is a known engine inconsistency.
Application.Evaluate does not use the same array-calculation engine as the worksheet grid, which is why you may see different results for the same formula. This is a real (and known) Excel bug/limitation.

IMHO, your entire named function will be treated as volatile. You may with file provided by Charles Williams within this Volatile Excel Functions -Decision Models post.

However, variant 1 and 2 shall be calculated differently. LET() evaluates each internal name, independently will it be used later or not, and on next steps uses result of such evaluation. I guess next steps shall not trigger of OFFSET recalculation within LET for named result. Thus version 1 calculates it once, version two - twice.

When working with named formulas in Excel—especially those built using AFE functions like OFFSET, INDEX, or dynamic arrays—it’s important to understand how Excel handles their evaluation. Named formulas are not “materialized objects”; they behave more like reusable expressions that are re-executed whenever referenced.

1. Named formulas are recalculated every time they are used.
   Excel does not cache or materialize named formulas in a reliable or persistent way. Each reference to a named formula triggers a fresh evaluation unless you explicitly store its result in a variable inside a LET.
   -- In your first version, storing ManualAllocRng inside a LET means it is calculated once and reused.
   -- In your second version, calling ManualAllocRng twice means Excel evaluates the named formula twice.
2. Spill ranges behave differently depending on where they come from.
   A spilled formula placed directly on a worksheet (e.g., =FILTER(...)) is calculated once; all references to A1# simply read the already-computed spilled range.
   But a named formula that produces a spill does not materialize anywhere—each reference recalculates it.
3. The mismatch between sheet-based spilling and Evaluate() is a known engine inconsistency.
   Application.Evaluate does not use the same array-calculation engine as the worksheet grid, which is why you may see different results for the same formula. This is a real (and known) Excel bug/limitation.

Hello boukasa​ 

Happy Holidays!

Are you asking if a LAMBDA function is volatile or not?

The answer is NO, LAMBDA functions (by default) are NOT volatile, and they are only recalculated once one of their arguments change value. So, in your example both will function the same way.

And YES a LAMBDA can become volatile if one of their arguments is a volatile function such as NOW(), RAND(), INDIRECT() as examples of many other volatile functions.

I hope this helps

GiGi...