Troubleshooting

Also see the Formulas > Troubleshooting page.

DATEDIF Function

The DATEDIF function is only available for backwards compatibility and should never be used.
Instead of using this function you can find alternative formulas on the page under Advanced Functions.

@ prefix

When an existing function triggers Implicit Intersection, the @ character will appear infront of the function.
Before Dynamic Array Formulas were introduced in Excel 2021 implicit intersection existed and happened behind the scenes automatically.

_xlfn prefix

You will see the _xlfn. prefix displayed in front of a function when that function is not supported in that version of Excel.
For example if someone has used a function that is only available in Microsoft 365 and you open the workbook in Excel 2016.
For example the XLOOKUP function, which is not supported in versions of Excel earlier than Excel 2021.
These unsupported functions should be removed or replaced with different functions.

CEILING.PRECISE Function

The CEILING.PRECISE function is only available for backwards compatibility and should never be used.
Instead of using this function you should use CEILING.MATH

SINGLE Function

The SINGLE function was added in Excel 2021 but was quickly removed.
If you try and use this function you will see a warning pop up message and the function will be automatically replaced with the "@" character.

User Defined Functions - #NAME!

You cannot have the code module the same name as the user defined function.

User Defined Functions - VBE Refresh Bug

Every time a user defined function is updated the VBE title bar has [Running] appended to it.
Once the UDF function has completed the title bar reverts back to what it was before.
This updating of the title bar is unnecessary and will slow down your user defined function(s).
To avoid this happening close the VBE Editor then save and close the workbook.
You can also see a speed improvement when you initiate the calculation from VBA using Application.Calculate
This bug is reproducible in Microsoft 365.

link - fastexcel.wordpress.com/2011/06/13/writing-efficient-vba-udfs-part-3-avoiding-the-vbe-refresh-bug/