[ad_1]
There’s nothing worse than working on a formula in your spreadsheet and getting an error instead of a result. Here, we’ll take a look at the formula parsing errors you may see in Google Sheets and how to fix them.
Some errors you see in Google Sheets give you details. For example, the #N/A error lets you know that your lookup value cannot be found. On the other hand, the error labeled #ERROR! in Google Sheets is a head scratcher because it doesn’t give you an idea of what’s wrong.
Let’s take a look at the various formula parsing errors you may see in Google Sheets and how to fix them.
Error: #DIV/0!
This is one of the easiest errors to recognize and fix in Google Sheets. If you see # DIV / 0 !, it means you are trying to divide by zero or by a blank cell.
RELATED: How to highlight blank spaces or errors in Google sheets
Here you can see that we are dividing the value in cell F2 by the value in cell G2, but the value in G2 is $0.00.
When you see this error, hover over the cell that contains it. You should see something like “Parameter 2 of function DIVIDE cannot be zero” or similar.
Arrange: Check the parameter indicated by the error and correct the zero value or blank cell.
Error: #ERROR!
When you see this error and hover over the cell, you’ll see the dreaded “Formula parse error” line with no further details. This error basically means that something is wrong with your formula.
The problem could be anything from a missing operator to an extra parenthesis or the wrong symbol.
You can see below that we received the #ERROR! for our formula. Upon closer examination, you will see that we are missing the operator to join the range of cells we want to sum. There is a space instead of a colon.
In this example below, we are adding values into our formula. However, you can see a dollar sign in front of the first value which is a no-no. By default, Google Sheets uses dollar signs to indicate absolute values.
Arrange: The best thing to do when you get this error is to loop through the formula argument one at a time to find the error.
Error: #N/A
The #N/A error is one you’ll see if you’re looking for a value that doesn’t exist in your cell range. You may be using the VLOOKUP, HLOOKUP, or MATCH function.
RELATED: How to find data in Google Sheets with VLOOKUP
Here, we are using VLOOKUP to find the value in cell B15 (Monday) in cell range A1 to F13. Monday does not exist in the specified range, so we get the error instead of the result.
This error helps a bit when you hover over the cell. You can see here that it specifically says “The value ‘Monday’ was not found in the VLOOKUP evaluation.”
Arrange: Check the lookup value you are looking for in the specified range. Sometimes it’s a simple typo in the wrong value or cell range in the formula.
Error: #NAME?
If you misspell a function name, use one that isn’t supported by Sheets, have a typo in a defined name, or even lose quotes, you’ll see a #NAME? mistake.
RELATED: 5 Ways to Use Named Ranges in Google Sheets
In this first example, we simply misspelled the AVERAGE function.
And in this example, we misspelled and entered SEARCH instead of VLOOKUP.
ArrangeNote: The most common reason for the error is a misspelling or typo, so check the function names in your formula carefully.
Error: #NUM!
The #NUM! appears when you have an invalid numeric value or one that is larger than the scope of Google Sheets.
For example, here is a formula where the result is greater than what Sheets can display. And you can see this when you hover over the error.
Arrange– Make sure the calculation you want to perform is valid and that Sheets can support the result.
Error: #REF!
You will see this following error when you delete a cell that is referenced in the formula or if you try to get a result that does not exist. Let’s see examples.
RELATED: How to subtract numbers in google sheets
Here, we simply subtract the value in cell G2 from the value in cell F2. Everything is fine when both cells contain values. But then we remove column G and see the #REF! error because now the reference is missing.
In this example, we use VLOOKUP to return a value in the seventh column; however, that column is out of range.
For each instance of the error, hover over it for help. You can see that the first error tells us that the reference is missing, and the second tells us that the function evaluates to an out-of-bounds range.
Arrange: Replace the deleted cell, column or row or simply correct the references in the formula. For a lookup function, make sure the arguments you’re using are valid.
Error: #VALUE!
This final error we’ll see is #VALUE! and is often displayed when a cell it references is of the wrong data type.
In this example, you can see that we are subtracting the value in F2 from the value in F1. But the value in cell F1 is text, not a number.
You can hover over this error for more details and see that we must have entered the wrong cell reference in our formula.
Arrange: Make sure you are using the correct data types for your formula. You can select a cell and use the More Formats dropdown on the toolbar to check the data type.
RELATED: How to change the default date format in Google Sheets
Tools to help avoid formula parsing errors in Google Spreadsheets
Google Sheets offers some features to help you with your formulas, mainly when you use functions.
suggestion formula
When you start your formula with the equals sign and the function name in a cell, you’ll see suggestions from Sheets. You can select a hint if it matches your goal and just add the arguments. If you don’t see suggestions, turn them on by going to Tools > AutoComplete > Enable Formula Suggestions.
help formula
As you type your formula, you can also select the blue question mark icon that appears on the left. You can then review the dropdown box to see the arguments the function expects along with examples.
Use the IF.ERROR function
Another way to stop seeing errors is by using the IFERROR function. This handy tool may give you a different result instead of one of the above error messages, or hide the error altogether. For full details, check out our tutorial for IFERROR in Google Sheets.
RELATED: How to hide errors in google sheets
Mistakes are compounded, especially when you’re not sure what’s wrong or how to fix it. Hopefully, this list of formula parsing errors, explanations, and fixes for Google Sheets will help you.
[ad_2]
