Forum Discussion

boukasa's avatar
boukasa
Brass Contributor
Nov 26, 2025

How does Excel calculate named formulas and materialized named ranges?

I have been creating utility formulas with AFE to make my lambdas more readable and maintainable, for example:

ManualAllocRng =OFFSET(ManualAllocCorner,1,0,URows,EntityCount);

How does Excel calculate these results? Are they calculated every time I use them, or is there an internal type of materialization that happens once and then is re-referenced on each usage? In particular, I am wondering:

(1) If I have a lambda that references my ManualAllocRng twice, does it matter if I write it as 

thing = LAMBDA(x,LET(
  rng, ManualAllocRng,
  a, something(rng),
  b, otherthing(rng),
  res, combine(a,b,x),
  res
));

or as

thing = LAMBDA(x,LET(
  a, something(ManualAllocRng),
  b, otherthing(ManualAllocRng),
  res, combine(a,b,x),
  res
));

Does version 2 calculate the result twice? Does version 1?

(2) Should I instead be materializing the value in a Calcs sheet, naming the corner ManualAllocResMaterialized, and using ManualAllocResMaterialized# throughout the workbook instead of referencing ManualAllocRes? Does every reference to the named formula calculate its result again? Does every reference to a spill recalculate the result, or does it "look" at the spilled range that was already calculated?

On a related note, I have found a circumstance where sheet-based spilled result

=Ledger.FilterMatch

produces a different result from VBA

Dim B as Variant
B = Evaluate("=Ledger.FilterMatch")

 

This seems like a serious bug.

So I am considering materializing all my AFE-based named formulas onto a Calcs sheet just to avoid this bug, even if there is no performance issue to consider in the questions I asked earlier.

excel
Formulas and Functions
Macros and VBA

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 27, 2025

    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.

  • GeorgieAnne's avatar
    GeorgieAnne
    Iron Contributor
    Nov 27, 2025

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

Resources