In this topic, you will learn how to resolve an unexpected result #N/B (Dutch, “no value available”) or #N/A (English, “no value is available”) from an Invantive Excel formula. If you still get a #N/B or #N/A as the result of an Invantive Excel formula after reading through and trying out the tips, please leave a response detailing the steps from a blank Excel workbook that lead to the error.
#N/B and #N/A as Excel Formula Outcome
The value #N/B (#N/A) indicates that the Invantive Excel formula cannot be calculated and is often interpreted as an error. The value can unfortunately have many different causes. The following steps will help you to eliminate the cause. From here on, only #N/B will be mentioned, but the explanation also applies to the English version of Excel with #N/A.
Ensure Formula and Components are Correct
This is a step that is also applicable to Excel if Invantive Control for Excel is not being used.
If an underlying Excel formula causes a #N/B error, then a formula that reuses the outcome will also show the same message. If cell A1 contains “#N/B” or “=NA()”, then the formula “=2*A1” will also result in the outcome #N/B.
Therefore, simplify the Excel formula that results in a #N/B outcome as much as possible, while keeping the outcome #N/B. If it’s necessary to use underlying Excel formulas: place each formula in an individual cell and use a cell reference in your Excel formula. First, resolve the issues in the underlying formulas if such an individual cell shows a #N/B.
Use the formula “NA()” or conditional formula “IFNA()” to translate #N/B to desired outcomes if #N/B in the sense of “No data” is a valid outcome in the underlying formulas.
Use Invantive Excel Formula
This is a step that only applies when using Invantive Control for Excel.
If an Invantive Excel formula results in #N/B or #N/A, add a “VERBOSENA” hint in the formula. The cause will then be shown as the outcome. In the image below, the top formula returns #N/B because the Control model is not present. The bottom formula uses “VERBOSENA” to find out the cause. The (English) outcome of the formula then explains the cause: “#NA: No model and open connection.”
The hint is added via the “ExecutionHint” parameter available on almost all Invantive Control Excel formulas:
A complete overview of available hints is available at Execution hints van Invantive Control Excel-formules.
Request Afterwards via UDFMeasurements
From BETA-release 22.1.35 it’s also possible to request the reason(s) for #N/B messages afterwards by querying the view UDFMeasurements@InvantiveControl. This can be done in two ways:
- Via a query
- Via Data Analysis
Retrieving UDFMeasurements@InvantiveControl is done using the built-in Query Tool of Invantive Control for Excel:
Consulting UDFMeasurements@InvantiveControl via Data Analysis is done using building blocks. Choose the shown menu option for this:
The result will then be downloaded in the familiar Data Analysis manner from the cell where the cursor is, with the following result:
Check Control Model is Present
This is a step that only applies when using Invantive Control for Excel.
Invantive Excel formulas return the value #N/B as an error because the model is not enabled.
Check in the “Modeller” ribbon that the slider is to the right and green:
Enable the model if it wasn’t present by clicking the slider.
What is somewhat confusing is that Invantive Excel formulas that have already been computed and stored will continue to provide an outcome even if the model is disabled. The reuse of previous outcomes also works when the model is disabled. To be on the safe side, log in again to clear the memory of previous outcomes and recalculate all formulas.
Check Correct Partition/Administration is Chosen
This is a step that only applies when using Invantive Control for Excel.
Invantive Excel formulas return the value #N/B as an error because the Excel formula (such as on Exact Online) is asking for a value from a partition/administration that is not included in the selected list.
Typically, the partition/administration is the first argument of the Excel formula. If the first argument is not given but a partition/administration is expected, Invantive Control for Excel will check if exactly one partition/administration is selected. That partition/administration will then be used.
In the “Invantive Control” ribbon, choose the desired or all administrations:
Check Excel XLAM File with Invantive Formulas / Use SharePoint
This is a step that only applies when using Invantive Control for Excel.
Invantive Control for Excel uses an Excel-XLAM file to make new Excel formulas available. This file with the extension ‘*.xlam’ is searched for in the profile of the user who last used and saved the file.
Note that Invantive Control for Excel consists of two add-ins. Besides the xlam, the other one is “Invantive Control for Excel” (filename Invantive.Excel.Starter.vsto).
Using Invantive Control for Excel in combination with a direct SharePoint connection is strongly discouraged, as it leads to issues with many add-ins and requires less common IT knowledge on the part of system administrators to configure it correctly. We recommend working with the traditional folder structure, which can be linked to SharePoint as central storage.
In combination with SharePoint, an error may occur such as:
We can’t connect to https://ACME.sharepoint.com/Users/john/AppData/Roaming/Microsoft/AddIns/Invantive Control functions.xlam. Check that you’re using the correct web address.
Followed by the error message:
Microsoft Excel cannot access the file https://acme.sharepoint.com/Users/john/AppData/Roaming/Microsoft/AddIns/Invantive Control functions.xlam. There are several possible reasons:
- The file name or path does not exist.
- The file is being used by another program.
- The workbook you are trying to save has the same name as a currently open workbook.
And finally, the question:
Do you want to make this document a trusted document?
This file is located on a network location. Other users with access to this network location could modify the file unlawfully.
A workaround is to answer “Yes” to this question. Alternatively, have the IT administrator better configure the network setup in terms of authorisations.
Incorrect Excel XLAM File Location Leads to #N/B
This is a step that only applies when using Invantive Control for Excel.
Invantive Control for Excel uses an Excel XLAM file (extension “*.xlam”) that makes the new Excel functions available. This XLAM file is sought in the user’s Windows profile. Failing to find this file can lead to outcomes like #NAME but sometimes also to #N/A as an error.
In the Excel workbook, the links show the full path to the XLAM file used. This will differ per user. If a user works on a PC with a different location for the Windows profile, Invantive Control for Excel will automatically adjust the link.
If #N/A is displayed as the result of a formula, to be certain, re-establish this link by selecting “Redirect References” in the “Invantive Control” menu in the ribbon under “Extras”:
Should the problem persist, also verify the links in the “Data” ribbon through the “Edit Links” option, just to be sure:
Use the buttons to choose the correct XLAM file from %USERPROFILE%:
A possibly different or network folder can be set for “Invantive Control functions.xlam” by specifying the desired folder in the environment variable INVANTIVE_CONTROL_XLAM_FOLDER. The value here is combined with the file name by the Excel add-in to find the VBA.
Other
Sometimes the #N/A value can be explained by previous steps taken by a user, but it can still be difficult for a user to trace.
The following steps often resolve any remaining issues:
- Log in again via the log-in button in the “Invantive Control” ribbon.
- Force recalculation by selecting “Recalculate All”. The corresponding keyboard shortcut is: CTRL + Alt + Shift + F9.









