Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Formula Errors & How To Deal With Them

| | Information Helpful? Why Not Donate.

#NULL!, #DIV/0!, #VALUE!,#REF!,#NAME?, #NUM! and #N/A

See Also: DEBUG / EVALUATE FORMULA ERRORS

As soon as you have discovered how to use formulas in Excel, you will likely need to lean how to correct or hide formula errors. The first thing you should know is what each error type means. Once you understand what each error value means, correcting the formula becomes a LOT easier. Also note that a Formula can return an error IF a range it references contains an error cell.

To mask errors and return an alternate value in its place, it is best to return zero rather than empty text (""). This is because zeros are generally more downstream formula friendly than text.

HIDE ZEROS
To hide zeros on the Workbook level go to Tools>Options>View - Zero Values.

Custom Formats

Excel sees a cells format as having four Sections. These are, from left to right:

Positives;Negatives;Zeros;Text.

To hide zeros cell-by-cell use a Custom Number Format like 0.00;-0.00; where 0.00 is desired the format for non zeros. Note the use of -0.00 for negatives.

ERROR VALUES

#NULL!
Often occurs when you specify a intersecting range which in fact does NOT intersect. The space is the Intersect Operator and should be used correctly like;

=A1:F1 B1:B10
OR with named ranges
=Range1 Range2

In both cases Excel will return the cell value that intersects A1:F1 and B1:B10. In this case, B2.

However, if we used =A1:F1 B2:B10 Excel would display the #NULL! error as it is NOT possible for a row 1 range to intersect a column range that starts at
row 2.

#DIV/0!
Simply means you cannot divide zero into a number. For example

=A1/A2

would result #DIV/0! IF A2 contains nothing or zero. To correct this one could use one of 2 methods.

=IF(A2=0,0,=A1/A2)

=IF(ERROR.TYPE(A1/A2)=2,0,A1/A2)

Note the use of the ERROR.TYPE Function. It is important to identify the error type so you are NOT masking another error type you SHOULD know about.

That is, we could use;
=IF(ISERROR(A1/A2),0,A1/A2)

=IF(ISERR(A1/A2),0,A1/A2)

BUT, it is NOT good practice as you will end up masking most error values
when you SHOULD be masking only the #DIV/0! error.

Error.Type Function

For specifying error types. #NULL! = 1 #DIV/0! = 2 #VALUE! = 3 #REF! = 4 #NAME? = 5 #NUM! = 6 #N/A = 7

#VALUE!

Possibly the most frequent error type. Occurs when the wrong type of argument or operand (operand: Items on either side of an operator in a
formula. In Excel, operands can be values, cell references, names, labels, and functions.) is used. For example, you may have;

=A1*A2

and IF either cell had text and NOT numbers, the #VALUE! error would be displayed. This is why one should NOT change the default horizontal alignment of data cells. That is, text is always left aligned while numbers are right aligned by default. If you allow this and then widen a Column, you can tell at a glance what Excel is seeing as text and numbers.

#REF!

This means a non-valid reference in your formula. Often occurs as the result of deleting rows, columns, cells or Worksheets. This is why deleting
deleting rows, columns, cells or Worksheets is bad practice. Also check named ranges if used.

You DO NOT want to mask this error as you SHOULD be aware of it.

#NAME?

This error means a Function used is not being recognized by Excel. Check for
typos and always type Excel Functions in lower case. This way, when you
enter the formula Excel will automatically convert it to upper case, if it
is recognized.

Another common reason is if you are using a custom function without the code being present in he same Workbook. Or, you are using a function that requires a specific Excel add-in being installed. E.g the Analysis Toolpak

On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.

If necessary, follow the instructions in the setup program.

As with the #REF! error, you don't want to mask this error.

#NUM!

This error occurs if you supply a non valid number to a function argument. E.g, using a negative number when a positive is needed. Or, using a $, %
symbol with the number.

This error can be masked so long as you are aware of the reason why. Again, use the Error.Type function as shown in #DIV/0!

#N/A
The most common reason for this error is any of the Lookup functions. It means Excel cannot find a match for the value it's being told to find. There
are many ways to correct or mask this error out there, BUT most are wrong in their approach and force a LOT of unneeded over-heads.

Consider placing the Lookup functions on the same Worksheet as the Table (if not already), then create a simply reference (e.g. =IV1) to the cell(s) to
get the result into the needed Worksheet. Doing this also opens up another opportunity in that we could now use;

IF(ISNA(IV1),"",IV1)

See Stop #N/A Error

Another reason is when Array formulas are used AND the referenced ranges are not of equal size in each array.

See Also: DEBUG / EVALUATE FORMULA ERRORS

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft