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.

Read More

 Learn the most Powerful Formula for Google Spreadsheet.

The Query formula in Google Sheet is quite a powerful and versatile function. So much so that,we can call it a one-stop-shop for all the logical, lookup, summation, counting, averaging, filtering and sorting requirements. It helps us fetch specific information from a data set, through a query statement. Much like fetching result sets from a database using queries. The syntex of the Query language used in this formula is similar to SQL.

SYNTEX

QUERY (data, query, [headers])

data – is the reference to the range of cells on which we want to query upon.

query – is the text using which the QUERY formula churns out the information we are looking for from the data set. Since it is expected to be a string, it has to be enclosed within a set of quotes. Or, it can also be a reference to a cell, where the query text is stored.


headers – is an optional parameter that indicates the number of header rows at the top of the data. If left out, Google Sheets guesses the value based on the content within the data.

Usage: Query Formula

First of all, to understand how the formula is put to use, let us consider the following sample data. It consists of information corresponding to a list of students who have enrolled into various courses at a university. For all the demonstration purposes, we’ll enter the formula in the cell G1. It will be displayed in the formula bar in the snapshots.

Example # 1:

We will start off with a very fundamental demonstration. So, we use the QUERY formula to fetch the names of the students who are residing on campus.

Example # 2:

Having dealt with the basic example, let us now try fetching the names of the students who are NOT residing on campus.

Example # 3:

Now, we will fetch the names, ages, departments of the students whose have taken more than 7 courses.

Example # 4:

We will now attempt taking this a step further. We bring up the names, departments, join dates of the students aged 25 or below, and have joined the university between 25-Dec-2016 and 20-Jan-2017. Please note, in the query text, the dates always have to go with yyyy-mm-dd format, enclosed within single quotes.

Example # 5:

What if we need to reference the date from a cell? No problem there! We will get around with the help of concatenating operators and a text function. Therefore, in the example below, we will get the names and join dates of the students that joined after 1-Jan-2017.

Example # 6:

Is there a way to list out all of the departments and the display numbers of courses taken from the respective department? Yes, there is! And, we might as well understand the power and versatility that the QUERY formula offers.

You will notice that the QUERY formula returned the second column with the header “sum Courses”. Honestly, it is a bit awkward to have that for a header. But, we can fix that and rename it. Not only that, we will also use the second column (now renamed to ‘Courses Taken’) to sort in ascending order. Here is how we do it.

Example # 7:

Can we display the number of instances of each of the departments? Of course, we can! The QUERY formula got us covered here as well.

Example # 8:

Consequently, we will now experiment with the third parameter. While this is an optional input, it might come in handy when we come across headers that span across multiple rows. In such cases, this parameter helps us combine the headers in one single row, as shown below.

Query function video coming very soon. I explain very easy & simple way to how to use query function to generate amazing report. 

Read More