How to Fix Query Errors???

How to Debug  Query Errors???

You are here for learn about different error types in Google Sheets and how to correct It. Google Sheets returns the error value if the formula in the cell has some kind of error.

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.

Query Errors 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 cells 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 an 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 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.

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 error 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 a 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. Otherwise, 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. The #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 the 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)

Result: #ERROR!

That’s all. Hope this tutorial can help you in finding  various types error of query  function in Google Sheets.

Live with joyfully.