Posted by
We all get those Excel errors that occasionally start with #. What do they really mean? Are they important ?. I ignored them ?. In order to make these important decisions it is important to understand what they mean and why they were created, and then make an informed decision about how you want to handle them.Also know how to fix Error 0x0 0x0 This article will take you through some of the most common mistakes and mistakes you can make.
Let’s start looking at #DIV! error. This Excel error means you have tried to divide by zero. Example = B2 / C2 would give the error #DIV! 0 if C2 contains 0. You need to be aware that if cell B2 was indeed empty Excel would re-interpret that cell as containing zero giving you the equivalent of having a cell containing. and the real zero. With this error on your worksheet makes your close analysis impossible, tired? It is therefore best to accept zero separation in order to further analyze the data
How is the error fixed?
It is easy enough to fix with a simple piece of error management, let’s take the example above using a simple IF STATEMENT.
= IF (C2 = 0.0, B2 / C2)
This means that, if C2 = 0 then enter zero, otherwise, B2 / C2
Now let’s take a look at the #NUMBER! error. This I would say is a mistake that often happens. This error will be displayed when using the wrong type of arguments or operands. [The operand is simply a two-dimensional object for the user in the formula and these applications in Excel can be values, cell references, name labels etc.]
A quick example to explain
= B1 * B2
If one of the above cells did not have a number which means that one contains a piece of text, we would find the most famous #VALUE! error displayed.
Option 1:How to Fix an Error?
Check that your formula parameters have the correct types of data in the example above B1 and B2 should contain numbers. That should solve your mistake.
Now we can move on to #REF! error. In Excel this error refers to invalid cell references, which occur when the formula itself contains invalid cell references. You will find this happen if you
1.Any of the columns or rows containing data used in the formula have been deleted – usually by mistake!
- The original formula was copied or transferred to another cell and the reference was incorrect.
- The data used in the formula is relocated leaving the formula or function with incorrect reference.
Option 2:How to Fix an Error?
Well – if the data was accidentally deleted, use the Undo button to restore it – you’ll be lucky if this is the only reason you got #REF! error.
- If your data cannot be restored or retrieved in this way then you will need to re-enter the data and adjust the cell references if necessary, it is closed for a long time but can not be easily done.
- Arrange / edit cell references for copied or distributed formulas.
- Adjust the cell references to the data formulas submitted.
This will correct your mistake and get you back on track!
The next mistake I want to discuss is the #NULL error. This error occurs when references to two or more cells are not properly separated in a formula or the first and last areas of a cell width can be separated by a colon:
Option 3:How to Fix an Error?
Make sure you use a comma used correctly to separate cells from functions e.g. = SUM (A1, A2, A3, A4)
Note that the first and last points in the scope of a task are well separated by a colon: e.g. = SUM (D1: D4)
Check that a mathematical operator, such as a plus or minus sign is used to separate cell references in a formula e.g.
= D1 + D2 + D3 + D4.
#NUM! an error occurs when Excel expects a number, or when a formula expects a number, or the result of your formula or function produces a number too large or too small for Excel to display.
Option 4:How to Fix an Error?
Click the error cell, click the Appear button, and then click Tracking Error.
Review possible causes and solutions.
The old #N / A! usually occurs when using LOOKUP tables. For example, = The VLOOKUP function can be used in the context below
Columns A1: A4 contains a list of words, and columns B2: B4 contain another list of words we want to compare.
COL A COL B
Janet James
Jane John
James Dickson
We use a look at Cell C2 that looks like this
= VLOOKUP (B2, A2: A4,1, False) in translating this means looking at James in A2: A4 restore the first column, and I want exactly the same)
All but the formula effect in C2 will return the error and the names John and Dickson are not the same in column A.
Option 5:How to Fix an Error?
You can ignore the error or have Excel return ‘something else’ if the desired value is not found. Let’s use an example, it’s always easy to show by the examples I find.
So let’s say in our example above, we have list A with our names and List B with the names we have to check in list A, if we use the standard VLOOKUP formula #N / A it will appear where we can find matching words. namely John and Dickson.
All we have to do is add a bit of VLOOKUP Formula to the = ISERROR function and then we can get Excel to handle the error and display whatever we want when the name is not available. The formula will look like this
= IF (ISERROR (VLOOKUP (B2, A2: A4,1, FALSE)) “No Same Name”, VLOOKUP (B2, A2: A4,1, FALSE))
In this example I have asked the formula to replace the No Name Match if there is no similarity.
Finally let’s take a look at #NAME? Error 0x0 0x0. This is really simple. It means that Excel does not recognize the function you are using or the custom function you are trying to use does not have that code in your workbook, this usually happens when you are using a function that needs to be added with Add ins. Eg. Analysis tool park.