Hi
friends,
While discussing about functions in Excel, often
we get #N/A, #VALUE, #DIV/0, #NAME?, #REF, #NUM, #NULL, #### or other types of errors while calculating or using formulas.
In today's discussion, I'll give you few ideas
to handle these errors very efficiently.
Free Download Excel Working File
View in YouTube How to Eliminate Excel Error Very Easily and Quickly
View in YouTube How to Eliminate Excel Error Very Easily and Quickly
First of all let us know the meaning of these
errors, knowing the error type will help you to get rid of this problem very
quickly and easily.
So, let's discuss one by one...
1. "####" error : it is very simple just widen the column i.e., increase the width of the column.
The row of ##### occurs in a variety of
instances, such as when:
a. an entered value is wider than the current
cell width for a cell formatted for dates or times.
b. A formula entered into the cell formatted for
numbers produces a result that is wider than the cell.
c. A number or text data, in excess of 253
characters, is entered into a cell
formatted for numbers dates, times, or accounting.
d. A negative number resides in a cell that has
been formatted for dates or times. Dates and times in Excel must be positive.
2.#VALUE! error : in maximum case you will find this error is because of the different type of data types, for example if you add one cell containing number and the other cell having a text it will show you this error.
3. #DIV/0! error : if you try to divide some value by zero this error displayed, this is always means 'divide by zero' is happening.
The divisor or denominator in a division
operation is equal to zero - either explicitly - such as =A5 / 0 - or as the
result of a second calculation that has zero for a result. It might occur
because a formula referencing a cell that is blank.
4. #N/A error : The most common error found in lookup formulas (VLOOKUP, HLOOKUP, INDEX and MATCH etc.), means lookup value not found, this error needs rectification in your formula and you can use a function to eliminate this error (not only this error but for maximum Excel errors) and it is =IFERROR(value, value_if_error) which I'll discuss letter in this post.
But remember this error
can be very usefully utilized in data analysis to find out the mismatch in two
or more complicated and large data containing columns (I'll discuss it in my YouTube Video).
5. #NAME? error : this error is
primarily occurs because of the typo error e.g., if you type =COUNTIIF
(One 'I' is extra in this example) in place of =COUNTIF or SUUM
instead of SUM function you will get this error.
6. #REF! error : reference error, as the name suggest this error is displayed because of non existing reference in the formula or it may display as you delete the data accidentally.
The possible reasons are :
a. individual cells or entire columns or rows
containing data referenced in a formula are accidentally deleted.
b. data from one cell is moved (using cut and
paste or drag and drop) into a cell that is referenced by a formula.
c. a formula contains a link - using OLE (Object
Linking and Embedding) - to a program that is not currently running.
7. #NUM! error : Entering a
non-numeric argument to a function that expects a numeric value. A function or
a value that evaluates to a numeric value beyond the Excel’s capabilities to
handle. Result of a function that could not find a valid value in spite
multiple iterations. Very rare in real life data processing if you are a little
bit cautious.
8. #NULL! error : occurs when the two or more cell references are separated incorrectly or unintentionally by a space in a formula.
Multiple cell references in a formula are
separated by a space instead of a mathematical operator such as a plus sign ( +
) - example: =A1 A3+A5, or
the start and end points of cell ranges are
separated by a space instead of by the range operator - the colon ( : ) -
example: =SUM( A1 A5) or
individual cell references in a formula are
separated by a space instead the union operator - the comma ( , ) - example:
=SUM( A1 A3,A5) or
the intersect operator - the space character -
is used intentionally, but the specified ranges do not intersect - example:
=SUM(A1:A5 B1:B5)
How to Correct these errors ?
There are various techniques to correct this
type of error display in Microsoft Excel, the best method we use is a function
(and I personally love to use it always while typing a formula, it's a good
habit) IFERROR()
Syntax
=IFERROR(value, value_if_true)
Use your formula in 'value' parameter and you
can write anything in the parameter 'value_if_true' e.g.,
while calculating
numbers you can write it '0' or if you need text to be displayed, you can write
any text but within double quote e.g., "Value not found" etc. Please
look carefully the below image, it will be more understandable.
 |
| #N/A #VALUE #DIV/0 #NAME? #REF #NUM #NULL & #### error messages in Microsoft Excel |
You can use another function ISERROR()
Syntax
=ISERROR(VALUE)
Use your formula in 'value' parameter, it will
return TRUE if error FOUND and FALSE if error NOT FOUND. But here you need to
use IF logical function (I'll discuss in detail about this interesting logical function latter in this blog) to get the better result as described in the below
image.
 |
| How to use ISERROR function in Excel |
Sometimes you may get an #N/A error in VLOOKUP(), HLOOKUP(), MATCH() with INDEX() functions and although you can find or view the existence of the data but is not showing i.e., VLOOKUP
returns #N/A error where the value exists by the formula like the image below.
 |
| VLOOKUP #N/A Error in Excel |
It's mainly because of the data type change, you may notice a little green
triangle in the left-upper corner of the cell, it means that the data is in text
format instead of number. A square box appears while this error occurs, you can click inside this box to view the message about the probable cause of this error.
Please view my YouTube Channel for more information
regarding this errors, you'll get adequate information about this topic.
Thanks a lot...
No comments:
Post a Comment