Different Error Types in Google Sheets and How to Correct It

0
84
Different Error Types in Google Sheets

Learn about different error types in Google Sheets and how to correct It. Google Sheets returns error value if the formula in the cell has some kind of error. To make things easier for the users, Google Sheets returns different error values for different error types. The different error types in Google Sheets are as follows and all these error values start with the # number sign. You should properly understand the error values to correct the formula error in Google Doc Spreadsheet.

Different Error Types in Google Sheets

All the below formula error values have a number associated with it. The prefix in the values below are the error numbers associated with it.

1. #NULL!
2. #DIV/0!
3. #VALUE!
4. #REF!
5. #NAME?
6. #NUM!
7. #N/A
8. All other error types

The above are the 8 different error types in Google Sheets. If any of the cell contain error values you can test it and get the error numbers in another cell. You can use the Error.Type function for this purpose. This function can identify all the above 8 types of errors in Google Sheets and can return the numbers from 1 to 8 accordingly.

How to Use Error.Type Function in Google Sheets

Syntax:

ERROR.TYPE(reference)

Usage:

Suppose cell A1 has an error value #DIV/0!. The following formula would return number 2 as result.

=ERROR.TYPE(A1)

Then what is the use of Error numbers in Google Sheets?

Example:

The value in cell A1 is 20 and B1 is 0. The following formula would return error.

Formula: =A1/B1
Result: #DIV/0!

In the cell that contains the formula, you can see a formula error notification saying “function DIVIDE parameter 2 cannot be zero”. If you divide a value in a cell with 0 in another cell the above error occurs.

See one more example. Here Value in A1 is 20, B1 is a text string.

Formula: =A1/B1
Result: #VALUE!

In both the above cases you can use Google Sheets ERROR.TYPE function as below in an IF logical test.

=iferror(if(ERROR.TYPE(A1/B1)=2,“Value Can’t be Zero”,IF(ERROR.TYPE(A1/B1)=3,“TEXT”)),A1/B1)

This formula would return “Value Can’t Be Zero” if the error type is #DIV/0!, “TEXT” if the error type is #VALUE! and else the final part A1/B1.

Do you know why I’ve used IFERROR function in this formula. The ERROR.TYPE function itself returns an #N/A error if there is no error in the calculation. So if the error value is #N/A we can decide that there is no error in our formula. With the help of IFERROR we can execute the A1/B1 calculation.

You May Also Like: Difference Between ISERR and ISNA Functions in Google Sheets

You have already learned about different error types in Google Sheets and the use of Error.Type function. Now time to learn in details about all error types and and how to correct it in your Spreadsheet.

Understand Error Values in Google Sheets

1. #NULL!

I’ve never seen #NULL! error in Google Sheets. Of course it’s there in Excel. Anyway in Google Sheets there is an error number associated with it and it’s 1.

2. #DIV/0! Error in Google Sheets and How to Correct It.

It’s easy to correct #DIV/0! Error in Google Sheets. See the below example. Here the value in Cell B2 is 0 that’s why the error happens. It shouldn’t be 0 or blank. I’ve applied the formula in Cell C1. Check the error notification in that cell.

#DIV/0! Error in Google Sheets and How to Correct It.

3. #VALUE! Error in Google Sheets and How to Correct It

This error is one of the most commonly appearing error in Google Sheets. It happens when you apply mathematical operations in one or more cells that contain text. In the below example the multiply function encountered an error in B3 as it’s text.

#VALUE! Error in Google Sheets and How to Correct It

But even if the values are in number, in certain cases you may find this error! Because in certain cases the numbers in the reference cells may be numbers formatted as text. You can check it form the menu FORMAT > Number.

4. #REF! Error in Google Sheets and How to Correct It

This is related to Invalid Cell References in Google Sheets. There are two main reasons for the #REF! error in Google Sheets.

Example 1: Circular Dependency.

Circular Dependency

See the above formula. The formula is to sum Cell B11 and B12. But in the SUM formula the cell references include the cell B13 too. B3 is the cell where I’ve keyed in the SUM formula.

Example 2: If you delete any cell, row, column or sheet which is already used as reference in any formula, then the formula would return the above error.

5. #NAME? Error in Google Sheets and How to Correct It

The below logical test is the best example to show you why Google Sheets returns #NAME? error.

#NAME Error in Google Sheets and How to Correct It

In the above IF formula, I should have entered the text string in double quotes. Other wise Google Sheets would consider it as a named range. Since there is no named range in the sheet, the error occurs.

6. #NUM! Error in Google Sheets and How to Correct It

This error is not common. #NUM! error is caused by an invalid argument in a formula in Google Sheets. Below is the example.

Formula: =rate(75, -1500, -10000, 200000)
Result: #NUM!

Formula: =rate(69, -1500, -10000, 200000)
Result: 1%

7. #N/A Error in Google Sheets and How to Correct It

This error is common in Google Sheets and this simply means value is Not Available.

Cell Value in A8=”Apple”

If I use the formula as below in any other cell, it would return the #N/A error. Because there is no value in cell A8 as “Orange”

=ifs(A8=“Orange”,500)

But if the function used is IF not IFS, it would return FALSE.

=IF(A8=“Orange”,500)

Finally for any other error types, normally typo, which is not specified above, Google Sheets would return #ERROR!.

Formula: =sum(A9 b9)
Resutl:#ERROR!

That’s all. Hope this tutorial can help you in finding different error types in Google Sheets and how to Correct it. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here