A formula parsing error message appears when the formula inputted expects a specific data type, but it received the incorrect kind. In other words, Google Sheets is unable to interpret your formula. It returns an error message since it is unable to complete the formula request.
It can be aggravating, especially if the formula is lengthy and the parsing problem isn’t apparent.
Do not worry! We would teach you how to identify the possible causes of the parsing problem and how to fix them!
Here are the five most common formula parse errors in Google Sheets that you may face:
Look familiar? Let’s see how to solve these errors! 🤗
Table of Contents
Solve a #N/A Error in Google Sheets
When a #N/A error appears, it means that a value is not available. This error would be seen frequently when using the
VLOOKUP function, as the search key cannot be found.
However, in this scenario, it does not mean that the formula we inputted is wrong. When the formula returns a #N/A error, it only means the specified search key is not within the range selected.
Let’s use an example to get better visualization.
As you can see from this example, Search Key B’s return value came back as an #N/A error. This is because the search key inputted “B2-05” could not be found in the range selected “A5:B9”.
Hence, this would cause the formula to return a #N/A error signifying the search key we inputted could not be found.
Solve a #DIV/0! Error in Google Sheets
#DIV/0! error appears when the formula divides a number with the value zero. This can happen when the denominator is zero. This does not make sense mathematically, so the formula returns a #DIV/0! error.
This error can also appear when the denominator is blank as well.
As you can see, since B1 does not have a value, the formula could not divide 40 by zero.
You would also often see this when using the
AVERAGE function. The error would appear when the range selected for the formula is empty.
Simply make sure the denominators used or selected has a value, and this parse error would not appear again!
Solve a #VALUE! Error in Google Sheets
When one or more parameters in your formula are of a different type than expected, you’ll get this error. So, if a function only accepts numbers as an argument, but the cell being selected has a text value, you’ll get a #VALUE! error.
Spaces within your cells can also cause this error.
Even though A2 looks like an empty box, but we have inputted a space within the cell. This caused the formula to return a #VALUE! error.
Here is another example:
Here we can see that the formula inputted is multiplying a number value with a text value which is “five”. This formula does not make sense mathematically as well as the parameters in the formula are of different types.
To fix this error, make sure the cells selected contain the same parameter type. To perform a math operation, always remember to use numeric values only.
Another scenario in which this error might occur is when mixing the dates format in a formula.
US date format: MM/DD/YYY
Rest of the world: DD/MM/YYYY
As you can see, when subtracting the two dates, Google Sheets could only read 12/25/2021 as a date as it is a number value. Google Sheets reads 25/11/2021 as a text, hence the formula returns a #VALUE! error.
To fix this, simply make sure the dates entered into cells are in the same formats.
Solve a #REF! Error in Google Sheets
When you have an invalid reference, the #REF! error occurs. The most common situations are when the cell selected is missing or when the formula is referring to itself.
This often happens when the original cell selected has been deleted (when you delete a whole row or column).
After we delete column A, the formula goes haywire as they could not find the original A1 selected.
Another scenario is when we copy a formula with a selected range at the corner of your Google Sheets.
It’s possible that when you copy and paste, the relative range shifts outside the confines of the sheet, which isn’t allowed and will result in a #REF! error.
When we copy the formula SUM(A1:B1) to B2, it will result in a #REF! error. This is because the original formula has two columns selected, but when the formula is copied and pasted into B2, it is missing one more column.
When the formula you entered is referencing itself, it is known as circular dependency. This happens when we selected a range that also consists of the formula itself.
As you can see, the formula contains a selection of cells that includes the formula itself.
Simply make sure that when you select the cells to be inputted, always exclude the formula to avoid such errors appearing.
Solve a #NAME? Error in Google Sheets
The #NAME? error appears when the syntax of the formula inputted has a problem. Most commonly, it is when the function name itself is misspelled.
The error would also appear when the named range does not exist.
Another scenario is when a text value inputted is missing the quotation marks. This would also cause the #NAME? error to appear.
When done correctly, the error would not appear.
When the #NAME? error appears, making sure that the function name and range names are valid to avoid this error.
You may make a copy of the spreadsheet using the link attached below and try it for yourself:
There you go! After learning about these five commonly appear formula parse errors, would do not need to worry when they appear. Always make sure to understand what the error is trying to signify and fix the problems in the formula accordingly. 🎇