Many functions return ‘#N/A’ which can corrupt an other wise clean set of calculations.
For example, suppose you’re performing a VLOOKUP on a two column list of products and prices and you’re looking for a product not listed. Your VLOOKUP formula will return a ‘#N/A’ result.
If this formula is part of a larger calculation – the total cost of a multiple set of purchases for example – it would infect the whole calculation which would now also return ‘#N/A’. Even worse, any cell or formula anywhere in your spreadsheet (eg a complex quote for a customer) that has this result as an input will return ‘#N/A’. This can be very annoying if a large spreadsheet is compromised due to just one missing product name.
Anyway, here are some of the ways to deal with this ‘dreaded #N/A’ error…
Add The Missing Data
This is the simplest method: just add the missing data (eg the missing product in our example)
Indeed remaining any ‘#N/A’ errors from a calculation is a good control over data completeness
ISNA()
The ISNA function returns TRUE if a cell contains #N/A and FALSE if not.
It can be used in other formulas (eg IF formulas) to deal with any #N/A arising from a calculation.
Here’s more on this function:
https://exceljet.net/functions/isna-function
IFNA()
A combination of the IF function and ISNA, this function allows you to specify what you want your spreadsheet to do if it encounters an #N/A (or any other error).
For you might want to display an error message if there’s an #N/A, or return zero so that the calculation can proceed.
Here’s more on this function: