What Is Formula Parse Errors In Google Sheets?
Formula Parse errors in Google sheets, in simple terms, are the errors which occur in Google sheets. The error can be a mistake in the formula or invalid cell reference. In Google sheets, we will be notified with a formula parse error message. Normally, errors occur due to mistake in the arguments. At times, even adding an extra comma or not adding enough punctuation marks can also cause an error.
For example, consider the below table showing values in column A.
Now, assume that we need to add the total if the value is more than 10, using SUMIF function in Google sheets. To begin with, insert the SUMIF function formula in Google sheets and select the cell range with the condition which is values must be greater than 10 (>10). So, the complete formula is =SUMIF(A1:A3,”>10″).
But instead of this formula, if =SUMIF(A1:A3,>10) is inserted, we will get a formula parse error, as shown in the below image.
In this article, let us learn the formula parse errors and how to resolve them with detailed examples.
Key Takeaways
- Errors in Google sheets commonly appears when there is a mistake in the entered formula.
- In Google sheets, formula parse errors occurs when users enter incomplete formula, do not include proper punctuation marks or include too many unnecessarily.
- Other reasons include not including the correct cell reference or having spelling mistakes in the function name in Google sheets.
- To note, some of the commonly occurring formula parse errors in Google sheets are #VALUE, #ERROR, #N/A, #REF, #NAME and #NUM errors.
Types of Formula Parse Errors In Google Sheets
Formula parse errors are commonly syntax errors. Some of the types of formula parse errors in Google sheets are:
- #ERROR
- #N/A
- #REF
- #VALUE
- #NAME
- #NUM
Now, let us learn about each of the error with detailed examples.
#1 – #ERROR
This error occurs when there is an issue with the punctuation marks used in the formula.
For example, consider the below table showing names and values recorded in a sample study in columns A and B, respectively.
Now, we need to add the values with a condition that the value must be greater than 10.
The steps are:
Step 1: To begin with, we need to enter the cell range in the spreadsheet. So, in this example, the data is available in the cell range A1:B5. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell B5 to find the result.
Step 2: Enter the SUMIF function in Google sheets formula.
Step 3: So, the complete formula is =SUMIF(A1:B4,”>10”). But instead if this formula, by mistake, if the formula is inserted as =SUMIF(A1:B4,>10), we will get the #ERROR error as shown in the below image.
Now, to fix this error, simply add the double quotation marks, as shown in the below image.
Step 4: Press Enter key.
We will be able to see the result in cell B5, as shown in the below image.
Likewise, we can fix #ERROR in Google sheets.
#2 – #N/A
This error occurs when there is an issue with the number of arguments used in the formula or when the data is not available in the entered cell references.
Let us use the same example we used in #ERROR example.
Consider the below table showing names and values recorded in a sample study in columns A and B, respectively.
Now, we need to add the values with a condition that the value must be greater than 10.
The steps are:
Step 1: To begin with, we need to enter the cell range in the spreadsheet. So, in this example, the data is available in the cell range A1:B5. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell B5 to find the result.
Step 2: Enter the SUMIF function in Google sheets formula.
Step 3: So, the complete formula is =SUMIF(A1:B4,”>10”). But instead if this formula, by mistake, if the formula is inserted as =SUMIF(B2:B4), we will get the #N/A error as shown in the below image.
Now, to fix this error, simply add the condition with double quotation marks, as shown in the below image.
Step 4: Press Enter key.
We will be able to see the result in cell B5, as shown in the below image.
Likewise, we can fix #N/A in Google sheets.
#3 – #REF
This error occurs when there the cell reference (in rows or columns) are missing from the data.
For example, consider the below table showing item, delivery charge and result in columns A, B and C, respectively.
Now, we need to add the subtract the values to find the total profit.
The steps are:
Step 1: To begin with, we need to enter the cell range in the spreadsheet. So, in this example, the data is available in the cell range A1:D5. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell D2 to find the result.
Step 2: Enter the formula =B2-C2 function in Google sheets formula.
Step 3: Press Enter key.
We will be able to see the result in cell D2, as shown in the below image.
But, by mistake, assume column B is deleted. Then, we will see the #REF! error as shown in the below image.
Now, to fix this error, we need to add the column B to find the result.
After we enter the column, using Autofill option, we will be able to see the result in cell range D2:D5, as shown in the below image.
Likewise, we can fix #REF error in Google sheets.
#4 – #VALUE
This error occurs when there is an issue with the value used in the formula.
Let us use the same example we used in #REF error in Google sheets.
For example, consider the below table showing item, delivery charge and result in columns A, B and C, respectively.
Now, we need to add the subtract the values to find the total profit.
The steps are:
Step 1: To begin with, we need to enter the cell range in the spreadsheet. So, in this example, the data is available in the cell range A1:D5. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell D2 to find the result.
Step 2: Enter the formula =B2-C2 function in Google sheets formula.
Step 3: Press Enter key.
We will be able to see the result in cell D2, as shown in the below image.
But, by mistake, assume that instead of =B2-C2, we have entered A2-C2. Then, we will see the #VALUE! error as shown in the below image.
Now, to fix this error, we need to add the correct cell reference to find the result.
After that, using Autofill option, we will be able to see the result in cell range D2:D5, as shown in the below image.
Likewise, we can fix #VALUE error in Google sheets.
#5 – #NAME
This error occurs when there is an issue with the formula syntax. Similarly, if any unknown function is included as a function, we will get #NAME error in Google sheets.
For example, consider the below table showing sales data, location and sales in columns A, B and C, respectively.
Now, we need to find the data pertaining to the row ‘3’, using INDEX function in Google sheets.
The steps are:
Step 1: To begin with, we need to enter the cell range in the spreadsheet. So, in this example, the data is available in the cell range A1:C6. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell C6 to find the result.
Step 2: Enter the INDEX function in Google sheets formula.
Step 3: So, the complete formula is =INDEX(A1:C5,3).
But instead if this formula, by mistake, if the formula is inserted as =NAME, we will get the #NAME error, showing there are no arguments included as shown in the below image.
Now, to fix this error, simply add the complete formula, as shown in the below image.
Step 4: Press Enter key.
We will be able to see the result in cell C6, as shown in the below image.
Likewise, we can fix #NAME in Google sheets.
#6 – #NUM
This error occurs when there is an issue with the punctuation marks used in the formula.
Let us use the same example we used in #NAME error.
For example, consider the below table showing sales data, location and sales in columns A, B and C, respectively.
Now, we need to find the data pertaining to the row ‘3’, using INDEX function in Google sheets.
The steps are:
Step 1: To begin with, we need to enter the cell range in the spreadsheet. So, in this example, the data is available in the cell range A1:C6. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell C6 to find the result.
Step 2: Enter the INDEX function in Google sheets formula.
Step 3: So, the complete formula is =INDEX(A1:C5,3).
But instead if this formula, by mistake, if the formula is inserted as =INDEX(A1:C5,10), we will get the #NUM error as there are no data in row 10, as shown in the below image.
Now, to fix this error, simply add the correct row number, as shown in the below image.
Step 4: Press Enter key.
We will be able to see the result in cell C6, as shown in the below image.
Likewise, we can fix #NUM in Google sheets.
Common Causes Of Formula Parse Errors
Some of the common causes of formula parse errors are:
- Entering incomplete formula
- Not including proper punctuation marks or including too many
- Not including the correct cell reference
- Mistakes in the function name
How To Fix Formula Parse Errors In Google Sheets?
We can fix formula parse errors in Google sheets using the following steps.
The steps are:
Step 1: Identify the error type.
Google sheets shows the error type we have made in the spreadsheet. For example, if the error is #NAME error, then we need to fix the function name.
Step 2: After identifying the error, Google sheets will prompt us to do the essentials to fix the error.
Step 3: Similarly, we can fix the errors in Google sheets.
Important Things To Note
- Errors in Google sheets appears when there is any mistake in the formula or function used in the spreadsheet.
- Some of the common errors are:
- #ERROR
- #N/A
- #REF
- #VALUE
- #NAME
- #NUM
Frequently Asked Questions (FAQs)
For example, consider the below table showing values in column A.
Now, assume that we need to add the total if the value is more than 100, using SUMIF function in Google sheets.
To begin with, insert the SUMIF function formula in Google sheets and select the cell range with the condition which is values must be greater than 100 (>100). So, the complete formula is =SUMIF(A1:A3,”>100″).
But instead of this formula, if =SUMIF(A1:A3,>100) is inserted, we will get a formula parse error, as shown in the below image.
Likewise, we will be able to see a message showing the Formula parse error in Google sheets.
NAME errors in Google sheets occur when there is a mistake in the function name. To fix #NAME error, we need to enter the function correctly. For example, instead of =SUM, if the function is entered as =SOM, we will get #NAME error.
Errors in Google sheets commonly occurs when we don’t include the correct formula with arguments in Google sheets. For instance, instead of adding the condition in double quotes, if we insert the formula without double quotations, we will get an #ERROR error in Google sheets.
Recommended Articles
Guide to What Is Formula Parse Errors In Google Sheets. Here we learn explain the how to types of formula parse errors in google sheets. You can learn more from the following articles –
Leave a Reply