Question about Excel's LAMBDA function

Question

This question is about Excel's LAMBDA function, which is available in recent versions. It does not matter if it's for business, education, or home, not should my OS matter.

I am writing a function to calculate Future Value. If I name the function "_FV" and use it, Excel replies: "There is a problem with this formula. ... Not trying to type a formula? When the first character is an equal ("=") or minus (-") sign, Excel thinks it's a formula: .... To get around this, type an apostrophe (') first ....

OTOH, if I name it "_FutureValue," it works properly.

Is this a bug? Or is this behavior documented somewhere?

Answer 1

Hello. Are you defining the LAMBDA directly in Name Manager, or are you creating it via a formula and then assigning the name afterward?

The parser fails before Excel can explain why the name is invalid, so you get the generic error instead of a targeted warning. The documentation lists valid characters, but it does not enumerate reserved identifiers or explain name-resolution conflicts, which is why this feels misleading.

As for naming conventions: leading underscores are allowed, but they are not a safe way to mark “private” functions if the name that follows resembles a built-in function. A symbolic prefix alone is fragile in Excel.

The most reliable pattern in practice is a short alphabetic prefix plus a descriptive name, for example: fnFutureValue, _fnFutureValue, or CalcFutureValue.

Answer 2

OK, then this explains the problem. But then I have two follow-up questions:

1. Why doesn't Excel give a more appropriate error message? Something like, "When using the LAMBDA function, do not begin function names with special characters." Even better, FIX the documentation! E.g., Under "syntax rules" the Excel help entry for "Names in formulas" says: "Valid characters    The first character of a name must be a letter, an underscore character ("_"), or a backslash (""). Remaining characters in the name can be letters, numbers, periods, and underscore characters."

      But you're saying this isn't entirely correct! Certainly the documentation for LAMBDA should warn       of this. I wasted hours of my time trying to find my mistake, when in fact it was Microsoft's.
   

   2. I couldn't find an Excel Style Guide, so I decided to follow Google's R Style Guide as closely as possible. Under naming conventions it recommends: "The names of private functions should begin with a dot. This helps communicate both the origin of the function and its intended use." But since Excel doesn't allow dots in names, I chose one of the two special characters Excel does allow, "". Besides the advantages that the R Style Guide describes, using a special character to begin a function name in Excel automatically locates functions at the start of the list of names in the Name Manager. This is a feature that's sorely missing from the NM. But if I can't use "", should I use ""? Otherwise, WHAT DO YOU RECOMMEND?