Errors in excel? Never avoid these errors

Most of the excel users would have faced the errors like #REF, #VALUE, and #NA while creating formulas for Analysis. These errors may be more common in excel, but each error has a different meaning and purpose in a formula. In today's topic, I will explain the types of errors, their uses, and how to avoid or handle those errors in excel formulas.

The error occurs mainly because of broken formulas, Majority of errors start with a pound (#), and I am going to focus mainly on the pound (#) type of errors.

What is an error?

In excel, An error can be defined as negligence, unstructured, or improper referencing, or when the range is not properly set, there are other errors like human error or value is not in the range or purposely we are expecting the error. there are different types of errors in excel and they can be reference errors, Value errors, Not applicable errors or spill errors, etc.

Error type:

• #NUM error

This type of error occurs when a numeric value in a formula isn't valid. #NUM error often occurs when the data type of the numeric input arguments in a function is not numeric. For example, we can't input a negative value for an SQRT function which would result in an error.

= SQRT (-90) => #NUM error

To avoid this error we can use the ABS function in the SQRT function which would result in proper output.

Other ways of getting #NUM occurs:

1. #NUM error also occurs by default for some functions like IRR or RATE, to avoid this error for the listed functions by increasing the iterations for the functions. File -> Options -> Formula tab -> Enable iterative calculation (assign Min and Max)
   

2. #NUM error occurs in the function or direct calculation, resulting in larger values that cannot be accommodated in excel.

= 100^500 => #NUM

• #VALUE! Error

This type of error is very common in excel, error occurs when there is something wrong with the function that is typed. The error cause is very hard to find as the error can occur even for the wrong cell referred. Sorting this error might be difficult and does not have the proper way to identify the error, but can be fixed using the trial and error method to clear the error.

For example, we can't insert a text argument in place of a Numerical argument for a function that uses numerical input.

= SQRT ("ABC") => #VALUE Error
= SUM ("as") => #VALUE Error

Other ways of getting #Value Error:

1. #VALUE error also occurs when the array of cells is not set, the best example is by using the TRANSPOSE Function when we try to transpose a range of values from row to column or column to row, to get the proper result the range of cells need to be selected, if not it would result in throwing #VALUE error.

      Value
        1
        2
        3
        4
= Transpose (A2:A5) => data transposed will throw an error when expecting a result in a cell.

• #N/A Error

This type of error generally occurs when a function couldn't find what it is looking for from its source. #N/A error majorly occurs with VLOOKUP, HLOOKUP, LOOKUP, XLOOKUP, or MATCH functions, where the formula can't find the referenced value.

For example, when using a lookup function from source data if we couldn't find the value that we are looking for it will throw an error.

Source Table:                    Result Table:
Fruit         Quantity            Fruit        Quantity
Apple            25               Banana          #N/A
Orange           34               Apple            25
Pears            16

= VLOOKUP(E2,$A$2:$B$4,2, False)=> The first result will through an error as there is no Fruit name as Banana in the Source table.

In order the tackle the #N/A you can use the IFERROR function to provide an alternative result in case it throws a #N/A error.

Other ways of getting #N/A error:

1. Incorrect value types ( i.e. Instead of numeric data type it is text data type)

2. There is a space in the value you are looking for, to avoid this error, use the TRIM function to avoid Leading and trailing spaces.

3. The reference range used in the formula is not the same as the source table range.

4. Using approximate results rather than the exact match in the formula (TRUE/FALSE).

5. If you are using N/A for missing values in the source table which can lead to an error while performing the calculation, it is often better to keep it blank or replace it with actual values once available.

• #DIV/0! Error

This type of error occurs when a number is divided by zero, this happens when the formula uses a divisor as zero, or the value in the cell it is referencing is zero (0), or the value in the cell it is referencing is left blank.

For example, we can see the error directly by a simple input value instead of using a function in excel.

= 1/0 => #DIV/0! error
= Quotient(1,0) => Will throw #DIV/0! error

This type of error can be corrected by using a Non-zero or Non-blank divisor value in the formula in excel, also using the IFERROR function to avoid replacing the error value. Entering #N/A as the divisor in place of Blank or zero can help the result to indicate the divisor value isn't available.

• #REF! Error

This error occurs when the range of cells referred to in the formula is not valid. this could also happen when a column or row is deleted in the referred range in the formula or if the referred range is incorrect.

For example, while using the VLOOKUP function if the reference range is not valid.

    C                D
   Fruit          quantity
3  Apple             12
4  Orange            15
5  Pears             18
     
= VLOOKUP (A3, $A$3:$B$5, 2,0) => The output will throw Reference error

This error can be corrected by properly referencing the data from the source table.

• #NAME Error

The main reason for this error to appear is because of the typo error in the function name. When this type of error occurs, it is better to immediately correct the error (Syntax of the formula). This error cannot be handled using an error handler like the IFERROR function to mask the error.

For example, while using the SUMIFS function, if the SUMIIF syntax cannot be used it will throw an error.

= SUMIIF () => will throw syntax #NAME error
The correct syntax for it is 
= SUMIFS () => correct syntax

To avoid typo errors in function names, using the function wizard in excel can avoid this error.

Formula tab -> Insert Function

• #NULL! error

This error occurs when we use an incorrect range operator in a formula or when we use space between the range of reference to specify two different ranges.

For example, While referencing a range in a function, it is better to mention the operators correctly.

= VLOOKUP (A2, $A$3: $D$5, 2,0). => will throw an error, as there is an unknown space in the formula. 

We can avoid these types of errors by using the correct operators in the formula.

1. A colon (:) to indicate the first and last cell when we try to refer to a continuous range of data in a formula.

2. A comma (,) indicates the separation between two different ranges in a formula while performing calculations so that they don't intersect each other. For example,

 = SUM (A1:B5, D1:D8)

Apart from the above errors, there is broken formula in excel, where this error will throw an excel Pop-up window if it falls below the category.

• The formula in the cell has broken links ( i.e. The reference data is not available at the source).

• The formula displays the syntax and not the result.

• The formula has one or more circular references (i.e. The formula references calculate the active cell where the formula is written).

• The formula doesn't have the mandatory arguments to perform the calculation.

• The data type of the arguments is not the same as the formula inputs.

• The formula doesn't have the matched opening and closing parentheses.

• The sheet name reference in the formula is not in single quotes.

• The formula is referencing the deleted data.

• The external workbook link to the formula is not referenced correctly.