Common errors encountered in MS-Excel and how can these be rectified

1. Not Applicable Error (#N/A):

Reason:

This error occurs when a function cannot find what it has been asked to look into the Source Data Table or Table Array. This means that no details exist into the data in reference to that selected lookup value.

For Instance:     Assume, we have applied Vlookup function in Table2 to find the Cost (Per Day) from Table1 in reference to lookup value as Resource Names, however this error occurred at the last cell because there are no details, for the resource 'A'.

Solution: 

Make sure that the Source Data Table or Table Array is complete before its use into the function. If still (with the complete table), no lookup value exists in the table then either remain this error as it is or override this error into text as 'Not Applicable' move forward with the next calculation.

For Instance:     Assume, earlier we were working with the incomplete data in following Table1 and then after getting the complete data in which details are provided for resource 'A' , then no error will occur as the function will give the desired result as $200.

2. Name Error (#NAME?): 

Reason:       

This error occurs when we try to apply any function by typing incorrect name of the function or Excel is not able to recognize that name.

For Instance:     Assume, We have applied the Product function in Table1 to calculate the Cost (Per Day) which is multiplying Hours & Bill Rates of resources. However, this error occurred because of the incomplete letters of the function name i.e., PRODUC’ instead of ‘PRODUCT’.  

Solution:     

While typing function name, just simply select that function from the dropdown list that appears automatically and press TAB key. Using this approach instead of manually typing the function name will not trigger an error.

3. Value Error (#Value!):

Reason:              

This error occurs when the variable mentioned in the applied function or in the reference cell is incorrect.

For Instance:     Assume, We have applied Vlookup function in Table2 to find the Cost (Per Day) from Table1 in reference to lookup value as Resource Names, however this error occurred in a range because the Column Inder Number is entered as 0 which is incorrect as it should always be greater than or equal to 1.

Solution:

Properly check the arguments of the function and make sure that each variable in correctly selected. This is how the error chances will not occur.

For Instance:      Assume, if we make the correction in the entered argument and change the Column Inder Number which is 4 i.e., the position for Cost (Per Day) column in the selected Table Array, then we will get the correct results.

4. Reference Error (#REF!):

Reason:

This error occurs when the function contains incorrect cell references. This usually happens while after applying the function, the row or column of used cell reference gets deleted.

For Instance:     Assume, We have applied function in Table1 to find the Cost (Per Day) in which we have selected common cell reference containing Bill Rate for all the resources as cost of each resource is being charged with that rate. If the column of Bill Rate gets deleted later on due to any reason, then this error would occur because the applied formula would not be able to recognize the used reference.   

Solution:         

As the references need to be updated to get correct result, edit the function, and do not delete the row or column for the dependent function.

 

5. Division Error (#DIV/0!):

           Reason:              

           This error occurs when the number is mistakenly divided by Zero (0) value.

For Instance:     Assume, We have applied the formula in Table1 to calculate Bill Rates with division of given Cost (Per Day) by the given Hours of the resources and dragged the formula for all resources, however this error occurred for the last 2 resources as their Hours (Divisor) are given as 0.

           Solution:            

Make sure to select the correct Divisor to get the correct result as the division cannot be done by 0 value.

For Instance:     If the Hours are incorrect in the data then get the correction numbers (Suppose 10 hours for both the resources) as shown in Table2 or if these 2 resources have not worked even for single hour then we can either remove their records from the Table or manually update their Bill Rates (If possible).

6. Null Error (#NULL!):

           Reason:

This error occurs when command between cell references in the formula or function is missed.

For Instance:     Assume, We have applied the formula in Tables for finding out Cost (Per Day) with the multiplication of given Hours with Bill Rates of the resources in Table1 however this error occurred in Table2 because we missed to enter the multiplier (*) between the cell references.

Solution:            Edit the function/formula, properly check the arguments and add the command carefully.

This will help you to make the calculations easily and effectively without getting an errors or rectification of errors. If you have any kind of challenge or you want to learn about any other Function/Option which is not covered or you want me to add more into the previously covered topic, please comment. I will be happy to take your queries or suggestions.