DMW logo
contact@consultdmw.com

Excel’s #VALUE!, #REF!, #DIV/0!, #NAME?, #NULL! and #N/A Errors

Understanding the significance of warnings sometimes posted by Excel formulas, how to adapt your formulas to accommodate them, and how to anticipate some potential hazards that may arise from them.

Last updated on 2020-05-26 by David Wallis.


Preface

There are warnings that Excel displays when it cannot evaluate a formula for one reason or another, illustrated here in Column C:

Excel error warnings

On the How to Deal With the Excel #DIV/0! Error page you’ll find explanation of the IFERROR function in the formula in Column D.

Adapting your formulas to respond to such warnings is important if you want to ensure that your spreadsheets are calculating as you require them to.


The #VALUE! Warning

The cause of this warning can be hard to track down, but these are common causes of the #VALUE! warning:

To illustrate the #VALUE! warning at work in aritmetic calculations, in this spreadsheet Cells B3, B4 and B5 are empty, and Cells B6 and B7 contain the formula =NA():

Excel VALUE warning

Hmmm! I’m comfortable with what Excel is telling me until I get down to Rows 6 and 7. What the logic is in returning the different warnings, I’m uncertain about. This example highlights why you need to test all likely outcomes of formulas, lest thay have unanticipated knock-on effects in your spreadsheets, possibly leading to incorrect results.

Here I’m running a test to understand how Excel might respond to some functions I want to use in a report summary:

Excel VALUE function returns

The SUM, COUNT, AVG, MAX and MIN functions in the formulas in Cells C12 to C16 all reference Cells C2 to C10.

That the COUNT function should be happy to ignore the warnings is a puzzle to me, and as such puts me constantly on the lookout for anomalous behaviour of any of my formulas.


The #REF! Warning

When a formula cannot locate a cell you have addressed in your formula, that formula will return the #REF! warning. For example, consider this spreadsheet in which everything is hunky-dory:

Very simple Excel formula

Until some vandal deletes Column A:

Excel formula REF warning

Note that the warning appears within the formula itself as well as appearing in the cell as the result of the formula.

A flawed VLOOKUP formula can return a #REF!, as in this example:

Faulty Excel VLOOKUP  formula warning

The VLOOKUP doesn’t like to be asked to find the value in the fifth column of a lookup table that contains four columns, A2:D5, only.


The #DIV/0! Warning

The #DIV/0! warning results from any attempt to divide by zero and is the subejct of How to Deal With the Excel #DIV/0! Error page.


The #NAME? Warning

Here are some possible causes of the #NAME? warning:


The #NULL! Warning

Possible causes of the #NULL! error warning are these:


The #N/A Warning

These are some causes of the #N/A warning:


Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

“My beautiful proof lies all in ruins.”

Georg Cantor (1845 – 1918)