Formula parse error как исправить

You’re trying to use a simple COUNTIF function, but you get an “#ERROR!” message instead. What does it mean and how can you fix it?

Google Sheets doesn’t have a compiler. But when you ask it to assign a value or calculation to a cell, it will check to make sure that the value or calculation is correct. If it isn’t, it will send you an error.

Unfortunately, there are a lot of errors out there. It may not be immediately obvious what each error means.

In this article, we’ll go over each common formula parse error in Google Sheets — and hoe to fix them

What Is a Formula Parse Error?

With some minor exceptions, errors in Google Sheets generally occur as silent values inserted into a given cell. So, if cell B4 has a #DIV/0! (divide by zero) error, cell B4 will display #DIV/0!

Whenever you see something that looks like this, you know it’s an error:

        #DIV/0!
        #ERROR!
        #NUM!

Errors start with a # (pound sign) and end with a ! (exclamation point). And, usually, that error has to be fixed. While you can leave it in your spreadsheet without harm (the rest of your spreadsheet will be unaffected), your calculations will likely be incorrect.

There is one notable exception: the “There was a problem” pop-up. If you create a syntax error that the computer can’t understand, it will pop up “There was a problem” to warn you and let you fix it.

It’s important to fix errors as you go in Google Sheets. If you don’t fix your errors, it’s possible that your sheet is giving you a result that’s incorrect. A budget with errors could just be a budget that’s not counting every number.

That being said, since every error is different, you need to understand the error code if you want to resolve them. Let’s take a look at how to fix a formula parse error in Google Sheets.

#DIV/0! – You Are Trying to Divide by Zero

Just as in a calculator, you can’t divide by zero in Google Sheets. #DIV/0! is a Google Sheets formula parse error to warn of this mistake. But you probably already know that you shouldn’t enter in “1/0.”

How to Fix the Error

The “divide by zero” error most commonly pops up when you have a long list of calculations that you’re doing and one happens to be 0.

For instance:

In the above situation, you didn’t intend to divide by zero — it happened accidentally because of the values that were fed into your formula. You could easily fix this like so:

Now you’ve used the IF operator to tell Google Sheets to divide only if the number is more than 0.

You should always consider the fact that a value might not be what you expect it to be when you develop a sheet. Validate your data properly if you don’t want your sheet to break when it’s given unexpected data. There will be a time that someone enters in a “0” in a slot that shouldn’t hold an “0.”

#ERROR! – Your Entry Doesn’t Make Sense

An #ERROR! will occur if you’ve entered in something that does make sense in Google’s syntax, but it doesn’t actually amount to anything. In the above example, nested parentheses are frequently used to capture additional arguments and functions within a cell. But the nested parentheses don’t include anything.

Though this isn’t strictly wrong in terms of syntax, it also doesn’t mean anything. So, Google returns a rather generic #ERROR! as a request for you to fix not the syntax, but the logic. If you see an #ERROR! message, it’s likely that what you typed in doesn’t exactly do what you think it does.

How to Fix the Error

Note that as you type out a formula, Google Sheets will automatically start writing out that formula for you. You should be able to see which variables you need to pass to a given formula and how the formula will work (in brief).

Following along with the Google Sheets guide for the particular skill you’re learning will be an easy and simple way to learn how to use more functions. It’s often caused by missing parts in the function such as quotation marks, brackets, or commas.

#N/A – Your Item Was Not Found

The #N/A error generally occurs when something isn’t found that was expected. It’s most common when you’re using the LOOKUP, VLOOKUP, and HLOOKUP functions. You can also find it in more niche functions like IMPORTXML.

How to Fix the Error

In the above example, we’re using VLOOKUP to look up the number of Apples in a chart. But if we used “Kiwi” instead:

Now we get an #N/A error. An N/A error is a sign that we need to fix what we’re looking for. When it comes to a LOOKUP function, we could use “fuzzy” parameters (the best possible match) or we could simply validate the table first (make sure that something returns).

But #N/A doesn’t just occur when you’re using a LOOKUP function; that’s just when they’re the most likely. It’s so common that there’s a function called ISNA(), which can be used to validate your data and make sure that your information is used correctly. In other words, the best way to fix an #N/A error is to make sure the data in your table is valid.

#NAME? – You’re Incorrectly Applying a Label

#NAME? is a slightly more esoteric error condition. In Google, you can name a range; this makes it a “named range,” making it easier to reference.

How to Fix the Error

You must enclose the name within quotes (“Rainbow”). When you just use a word (Rainbow) it will pop up with #NAME?

If you want to name an importrange, you need to do it correctly. And make sure to validate the names that you do use. If you’ve never used a named range, you may want to look further into it — it can clean up and streamline your code.

#NULL! – You Are Using Microsoft Excel!

In fact, you shouldn’t see this error in your Google Sheet. #NULL! is a commonplace error in Microsoft Excel, when a value is returned that is empty though it is expected not to be. Google tends to be more forgiving than this, usually just treating a null value as a blank space or a zero, depending on needs. You can even set things to “null” without getting a #NULL! error.

For the most part, it’s not a terrible idea to assume that if something works one way in Microsoft Excel, it’ll work the same way in Google Sheets. But that isn’t always true. #NULL! is an excellent example.

#NUM! – Your Number is Too Large to Display

In this situation, a given calculated field is just too large to be displayed. The number that we’re trying to calculate is above the feasible limits of any system, so Google Sheets isn’t going to waste time trying to calculate it (and, ultimately, locking up your system).

Instead, Google Sheets will warn you about numbers that are outside of the range that can be depicted. If this happens to you during intense calculations, then you know that either something has gone wrong, or you need to wildly reduce the amount of precision that you’re dealing with.

How to Fix the Error

There may be times when you need a high level of precision or Very Large Numbers, but it’s unlikely that most people will ever stretch the capacity of Google Sheets’ cells. If you’re seeing the #NUM! error, it’s much more likely that you’ve accidentally typed something you didn’t mean to. Check your inputs.

To change the accuracy requirements, you can use the move decimal place shortcuts in the toolbar.

#REF! – Your Reference No Longer Exists

The #REF! Google Sheets error refers to a reference that doesn’t or no longer exists. In the above example, we’re referring to a sheet (Sheet2) that doesn’t exist, so we get a #REF! error.

But people don’t commonly refer to something that doesn’t exist altogether. Usually, this happens because a sheet gets deleted. 

How to Fix the Error

Luckily, in Google Sheets you can always review previous iterations of a file. You can even launch it as a copy or replace the current file altogether. If you accidentally delete a reference, you can pull up an older sheet and see where that reference came from. You may also be able to press Ctrl + Z if deleting the reference in the same sheet was your last action.

#REF! will often show up if the cell your result is in is also in the specified range so you should remove it if that’s the case. For example, if your total is in cell B6 and your formula is =SUM(B1:B6) change it to =SUM(B1:B5).

#VALUE! – Your Item is Not the Expected Type

The #VALUE error in Google Sheets occurs when something isn’t the expected type. This error is simple to fix. In the above, we are trying to add together two strings. We can’t’ do that! If we replaced them with numbers, the formula would work fine.

Of course, it doesn’t always look that simple. You can get a #VALUE error with more complicated formulas, which requires that you look at the assigned values to ensure that the right value is being passed.

How to Fix the Error

Whenever you get a #VALUE! error, you should review the formula itself. You may be misusing the formula, which is what’s leading to the vague error.

“There was a problem” – You’ve Written Your Formula Incorrectly

This is the most common formula parse error in Google Sheets. It can also be one of the more frustrating errors to get. If you type something wrong (frequently, it’s just a typo), you’ll get a pop-up with “There was a problem.” This pop-up can be a little annoying because it’ll interrupt whatever you were doing. You’ll have to close the box and fix the syntax.

In the above screenshot, we just mistyped. We wanted to say “sum()”, but instead we ended up typing “sum()/”.

The “There was a problem” pop-up is distinct from #ERROR!. The #ERROR! appellation means that while the syntax appears to be correct, it doesn’t make sense to the compiler. You have technically typed something that is accurate, but it has no meaning.

Comparatively, a formula parse error means that your formula syntax does not make any sense. It will never run, even if the right values are fed into it. A formula parse error is fairly serious, but it’s also fairly trivial; it should be easy to tell what’s wrong.

Other Strategies for Dealing With a Formula Parse Error in Google Sheets

Using Google’s Built-In Error Documentation

At this point, you might have noticed that Google has a type of built-in error documentation. Every formula that Google Sheets supports is tied into a database within Google Sheets itself. As you start to type in formulas, you will see pop-ups guiding you through their completion.

Let’s revisit an error:

In the #ERROR! above, you might be tempted to believe that you’ve calculated a number too large. But this number isn’t too large at all. If it was, you’d be getting a #NUM! error, not an #ERROR! error.

Just hold your mouse over a cell with a red triangle in the upper right corner and you’ll see exactly what type of error you’ve received. In the above, you can see that it’s a “Formula Parse Error.” From there, you’ll hopefully notice the extraneous “_” that is holding the formula back.

Whenever you get an error, the first thing you should check is the red triangle. If you just hover over any errors you get, you’ll get more information about how to fix them. When in doubt, you can go to Google Sheets tutorial pages to learn more.

Functions to Help Deal With Formula Parse Errors in Google Sheets

IFERROR

The IFERROR() function is easy to use when you want to “clean up” errors. Wrap a function up with IFERROR(), and you can have the program take a different tactic entirely when errors are discovered, depending on what the discovered error is.

The syntax of IFERROR() is very simple:

IFERROR(value,[value_if_error])

So, it essentially operates as any IF command. If the cell has an error, it will print out the “value_if_error.” Otherwise, it will leave the cell alone. There are other ways to manage a cell, but IFERROR() is one of the simplest, cleanest, and most readable.

=ERROR.TYPE()

Gives the type of error through a numerical value:

• 1 = #NULL!
• 2 = #DIV/0!
• 3 = #VALUE!
• 4 = #REF!
• 5 = #NAME?
• 6 = #NUM!
• 7 = #N/A
• 8 = All other errors

=ISNA()

Provides a true value of there is an #N/A error in the specified range.

=ISERR()

Provides a true value if there is any other type of error in the specified range.

Reaching Out for Help

One of the major advantages of Google Sheets is that it has such a thriving community throughout the world. If you still can’t figure out how to fix your errors, you might not be alone. An online Google Sheets community (or even Google Sheets’ technical support) may be able to uncover problems that you have missed.

A primary benefit to Google Sheets is that you can add other people in as readers, commenters, and editors. If you’re struggling with your Google Sheet, consider sharing it with others who know the ropes. They’ll be able to get your issues fixed quickly through sheer experience.

Frequently Asked Questions

How Do I Fix a Formula Parse Error in Google Sheets?

First identify what the error code means. Mostly, there’s a missing reference or your formula is incorrect. Double check your formula against examples online.

What Is Parse Error in Google Spreadsheets? / What Does Formula Parse Error Mean?

Let’s take a quick look at formula to parse error meaning. A parse error in Google Sheets is a blanket term for a formula error. It accounts for the most possible problems that can occur when typing a formula. For example, #REF! means the reference does not exist in Google Sheets, so you should check the range in the formula is referencing an appropriate part of the spreadsheet.

How Do You Refresh Formulas in Google Sheets?

By default, formulas refresh when a change is made. But, you can also set them to refresh based on time by navigating to File > Spreadsheet settings > Recalculations and picking the appropriate option.

Why Is My Sum Formula Returning 0 Google Sheets?

The most likely explanation is that your values aren’t formatted as numbers. Make sure they are by using =ISNUMBER(cell range) in an empty cell, it will return FALSE if there are values that aren’t numbers in the range. Another possible reason is that you have an * in your formula and a null value cell. Anything multiplied by zero is zero. 

Finding the Cause of Your Errors

Finding the cause of your formula parse error in Google Sheets begins with figuring out what the error code means. If you get an #ERROR! message, you know that there’s something wrong with your logic. If you get a #REF! message, you know that you probably just deleted a reference. If you get a #NUM! message, you know that you definitely tried to do something with numbers far too large.

Google Sheets, just like other complex software platforms, can require a bit of trial and error.

There will always be errors. Understanding these errors, being able to track them down, and being able to fix them will all make it easier for you to make useful and attractive spreadsheets.

При работе с таблицами Google вы используете множество функций и формул для упрощения работы. Это благодаря обилию функций и формул Google Sheets. Всякий раз, когда вы назначаете формулу в Google Sheets, вы можете ожидать желаемых результатов, но могут быть случаи, когда результаты могут быть неточными. Вы можете столкнуться с несколькими ошибками синтаксического анализа формулы в Google Таблицах.

Несмотря на то, что эти ошибки синтаксического анализа формул Google Sheets разочаровывают, знание того, как их исправить, может в конечном итоге помочь вам решить их. Поэтому вы должны знать, что это за ошибки и почему они возникают? Таким образом, достаточно попрактиковавшись, вы сможете преодолеть эти раздражающие ситуации, не разочаровываясь.

Цель этого поста — объяснить, что такое ошибки синтаксического анализа формулы, как их идентифицировать и в конечном итоге исправить. Пожалуйста, не пропускайте ни слова здесь, если вам это нужно.

Как определить и исправить ошибки синтаксического анализа формулы в Google Sheets

Прежде чем погрузиться в список ошибок, вам нужно знать, почему эти ошибки появляются в ваших таблицах Google? Проще говоря, это ваша вина, потому что, если вы вводите какие-либо математически невозможные формулы, Google Таблицы выдают вам ошибку.

Обычно такие ошибки возникают из-за глупых опечаток. Иногда в ваших формулах есть ошибка, которая не имеет смысла. Итак, давайте выясним, что это за ошибки и как их можно исправить?

1. Ошибка всплывающего окна «Произошла проблема» в Google Sheets

Это самый распространенный тип ошибок «Была проблема» выскакивать. Обычно это происходит, когда вы вводите формулу с любыми нежелательными символами.

В большинстве случаев это происходит из-за опечаток. Однако, как только вы просмотрите формулу и при необходимости отредактируете ее, она будет немедленно исправлена.

Например, здесь вы можете увидеть нежелательный символ “^” был введен в вашу формулу; в результате вы получаете ошибку. Что ж, уберите символ из формулы, и в конце концов вы получите точный результат.

2. #Н/Д Ошибка синтаксического анализа формулы

Наиболее широко используемая функция поиска упрощает вашу работу в Google Таблицах. И ошибка #N/A возникает всякий раз, когда вы применяете эту функцию. Но почему?

На самом деле эта ошибка возникает, если листы Google не могут найти какое-либо значение, которое должно быть в вашей ячейке, к которой вы применяете функцию поиска.

Например, давайте посмотрим на лист ниже, на котором я применил формулу. Здесь функция поиска была применена к A1:B4, и, как вы видите, последняя показывает ошибку, потому что не может обнаружить никакого значения. В результате в ячейке появляется ошибка #Н/Д.

Итак, как это исправить?

Вы должны убедиться, что у вас есть значение в ваших таблицах Google, которые вы ищете. Нет другой альтернативы, кроме этой. Поэтому всегда тщательно проверяйте, а затем используйте функцию поиска.

Если у вас нет никакого значения, вы можете избежать уведомления об ошибке, используя ЕСЛИ ОШИБКА функция.

3. #ОШИБКА! Ошибка

Полная концентрация необходима всякий раз, когда вы используете любую формулу в своих таблицах Google. Если нет, то у вас может быть много ошибок при анализе формул в Google Таблицах, и #ОШИБКА! является одним из них.

Однако в основном это происходит из-за круглых скобок в вашей формуле. Либо это могут быть лишние или отсутствующие скобки.

Ниже приведен пример: из-за наличия в формуле дополнительных кавычек Google Таблицы не могут понять формулу, что приводит к #ОШИБКА! ошибка.

Стать более осознанным во время использования этой формулы — единственный способ исправить это. Поэтому всегда проверяйте, что каждое значение, которое вы вводите в свою формулу, в порядке, когда вы получаете эту ошибку.

4. #ЦЕННОСТЬ! Ошибка

Каждая формула в Google Sheets работает, когда вы вводите в нее правильные данные. Точно так же #ЦЕННОСТЬ! ошибка синтаксического анализа формулы возникает, когда ваша формула получает неправильные данные.

Например, если вы применяете какую-либо формулу для математических вычислений, но вводимые данные на самом деле являются текстом, то как это работает? Есть ли в этом смысл? Для математики Google Таблицы будут искать числа вместо текста.

Например, приведенное выше является примером #ЦЕННОСТЬ! ошибка в ваших таблицах Google. Давайте посмотрим на формулу в верхней строке формул. Здесь написано A1+B2, верно? Это хорошо видно, и вы хотите суммировать значения этих двух ячеек, верно? Но содержит ли A1 какие-либо числа? Тогда почему эта формула будет работать?

Правильная формула должна выглядеть так: A2+B2. Что ж, всякий раз, когда вы сталкиваетесь с этой ошибкой, пожалуйста, прочитайте текст инструкции в разделе «Ошибка». Так вы поймете, что именно нужно сделать.

5. #ДЕЛ/0! Ошибка

Другая ошибка синтаксического анализа формулы, которая может возникнуть в вашей таблице Google, это #ДЕЛ/0. Это происходит, когда вы делите любое значение на 0 или на ячейку без значения. Однако это математически невозможно. Таким образом, ошибка появляется на вашем листе.

Есть ли способ исправить эту ошибку? Просто убедитесь, что все параметры вашей ячейки не равны нулю или пусты. Если, однако, у вас действительно есть нулевое значение или пустое, тогда используйте ЕСЛИ ОШИБКАформула. Следовательно, сообщение об ошибке больше не будет отображаться в ваших таблицах Google.

6. #ЧИСЛО! Ошибка

#ЧИСЛО! ошибка возникает, когда у вас есть какие-либо недопустимые номера, к которым вы применяете функцию. Если вы попытаетесь ввести неверный номер в функцию, которая его не поддерживает, вы получите #ЧИСЛО! ошибка разбора формулы.

Ниже приведен пример функции SQRT (квадратный корень), которая не поддерживает отрицательные числа. Это работает только тогда, когда у вас есть положительное или нулевое числовое значение.

В результате функция возвращает ошибку разбора формулы #ЧИСЛО из-за использования -1 в качестве значения в ваших таблицах Google.

Поэтому, чтобы исправить эту ошибку, дважды проверьте, чтобы все числовые значения были положительными или равными нулю, когда вы используете функцию SQRT в своих таблицах Google.

7. #ССЫЛКА! Ошибка

Вы получите это #ССЫЛКА! ошибка в ваших таблицах Google, когда у вас есть недействительные ссылки. На изображении ниже ошибка возникает из-за настройки формулы для строки/столбца, которые были стерты или удалены. Вот почему это говорит Ссылка не существует.

Другой причиной этой ошибки может быть ссылка на ячейку или столбец, которых нет в диапазоне данных. На изображении ниже ВПР функция была применена к столбцам A: B, и результаты отображаются в столбце D.

Здесь Носок получает #ССЫЛКА! ошибка синтаксического анализа формулы, потому что она не существует в столбце A: B. Итак, чтобы избежать этой ошибки, вам нужно ссылаться на правильные ячейки и столбцы со значениями.

8. #НАЗВАНИЕ? Ошибка

Это очень распространенная ошибка, из-за которой #НАЗВАНИЕ? ошибка возникает в ваших таблицах Google. Когда вы пишете синтаксис неправильно для любой формулы, то #НАЗВАНИЕ? ошибка появится в Google Таблицах.

Например, синтаксис приведенной ниже формулы написан неправильно. Не существует функции, которая существует как IFF. Вот почему он говорит Неизвестная функция. Однако, как только вы напишете ЕСЛИ, формула будет работать правильно.

Чтобы исправить эту ошибку, вы должны правильно написать синтаксис вашей формулы. Поэтому всегда делайте двойную проверку, прежде чем применять любую формулу, подобную этой.

Подведение итогов

Обобщение ошибок синтаксического анализа формул в Google Sheets может быть очень неприятным. Следовательно, необходимо знать, что это за ошибки, как они возникают и как их можно устранить в конечном итоге, чтобы избежать такого разочарования. В этом посте вы найдете всю необходимую информацию. Таким образом, практикуйтесь регулярно, и сосредоточьтесь на своих формулах и функциях, и вам все станет легче.

Надеюсь, это поможет. А пока я уйду в отпуск и вернусь к вам с другим Google таблицы решение. Я был бы признателен за ваши отзывы в разделе комментариев ниже и поделитесь ими со своими близкими, если это будет полезно.

You’re working in a spreadsheet and you want to use a function.

You write the formula, excited to get the results, then you see «Formula parse error» leaving you feeling confused and a little defeated.

Let’s cover what that actually means and what probably lead to that error message.

It’s like trying to speak a different language without taking the time to learn it first.

The software can kind of make out what you’re saying, but not well enough to give you an accurate result.

There are two likely causes for this error: There’s a typo in your formula, or the order of operations is unclear.

We’ll go over some examples of each so that you can identify and fix them in your own formulas.

Usually, a formula parse error happens because of:

Incorrect syntax – E.g.: Typing =+ instead of =, forgetting to put quotation marks around text values, putting two operators next to each other without anything in between them
Incomplete syntax – E.g. Leaving out a parentheses.

Another reason why you may be getting these errors is that you’re trying to use text values where numbers are expected.

Let’s dive into the specific types of errors you may encounter:

#N/A Error

One of the most common errors is the #N/A error. It occurs when a formula can’t find what it’s looking for.

For example, if you’re using the VLOOKUP function to find a value in a table, and the value you’re looking for isn’t in the table, you’ll get the #N/A error.

#DIV/0 Error

This happens when you try to divide a number by zero.

For example, if you have a formula =A17/B17 and the value in B17 is 0, you’ll get the #DIV/0! error.

#REF! Error

When a formula contains an invalid cell reference, you will get this error message.

For example, if you have a formula that references cells A17:A22 and you delete row 21, the formula will return the #REF! error because it no longer has a valid reference.

#VALUE Error

The #VALUE! error occurs when a formula contains an invalid value.

For example, if you have a formula that multiplies two cells and one of the cells contains text instead of a number, you’ll get this error.

#NAME Error

This error occurs when a formula contains an invalid name.

For example, if you have a named range called » Prices» and you accidentally type «price» in your formula, you’ll get the #NAME? error.

#NUM Error

The #NUM! error occurs when a formula contains an invalid number.

Say you have a formula that divides two cells and the result is too large to be displayed, you’ll get this error.

Now that we know what can cause a formula parse error, let’s look at how we can fix them.

How to Fix Formula Parse Errors

The best way to avoid getting formula parse errors is to carefully check your syntax as you type it out. If you’re not sure what order the operations should go in, refer back to the order of operations suggested by the software you’re using.

If you’re getting formula parse errors, here are some steps you can take to fix them:

• Check your formula inputs and make sure they’re correct.
• Use the IFERROR function and display a different result if an error occurs. E.g. «Not found.»
• Check your spelling and make sure all the parentheses are in the right places.
• Make sure you’re using the correct operators.
• Use cell references instead of hard coding values into your formulas.
• If you’re using text values, make sure they’re enclosed in quotation marks.

By following these steps, you can avoid formula parse errors and get accurate results from your formulas.

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:

1. #N/A
2. #DIV/0!
3. #VALUE!
4. #REF!
5. #NAME?

Look familiar? Let’s see how to solve these errors! 🤗

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.

Missing Reference:

 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. 

Circular Dependency:

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. 🎇 

Formulas in Google Sheets were meant to make things easier for us.

However, every Formula user, regardless of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will).

These errors can often be quite frustrating, especially if you don’t understand what they mean.

In this article, we will talk about each common formula parse error in Google Sheets, what they mean, and how to trace the cause and correct the problem.

What is a Formula Parse Error?

A formula parse error occurs when Google Sheets is unable to understand your formula. There may be a number of reasons for this, for instance:

• There might be a typo in the formula.
• There might be more or fewer parameters than the number expected for a specific function.
• One or more parameters entered might be of a different type from what is expected.
• Cell references in your formula might be out of bounds.
• You might be trying to do a calculation that is mathematically impossible.

There may be a number of other reasons, and all these reasons make it difficult for Google Sheets to fulfill what is requested by your formula. As such, it returns an error message.

We are going to take a look at some common error messages and explore what they mean in the following section.

Finding the cause of the problem is of utmost importance, in order to solve it. Thankfully, Google Sheets tries to help you out by suggesting what might be wrong with the formula, so you can break it down and detect the root of the problem.

Different Types of Formula Parse Errors in Google Sheets

Let us first take a look at what kinds of different error messages you are likely to see:

#N/A Error

This error usually occurs when a particular value that the formula needs is not present. The ‘N/A’ in the error message simply means ‘not available’. In other words, the value is not available for the formula to work on.

This problem is often encountered when you use lookup formulas like VLOOKUP in Google Sheets.

For example, if you’re looking for a value and that value does not exist in the given range, you will most likely see a #N/A error.

In the image below, the VLOOKUP function needs to find the value ‘Tom’ from the range A:B. But since the value ‘Tom’ does not exist in the range, the function simply returns a #N/A function to indicate that the value ‘has not been found’.

Related: Google Sheets VLOOKUP from Another Sheet

How to Fix the #N/A Error

The #N/A error does not always signify a problem with your formula itself, but a certain circumstance (the absence of a value) that results in an error.

To rectify this error, you can combine the formula with an IF statement that displays a custom message if the #N/A error is encountered. A very useful function to handle these kinds of issues is the IFERROR function.

For example, we rectified our problem below by using an IFERROR statement to display a “Search Value not found” message instead of an #N/A error:

#DIV/0 Error

This error occurs when a number in the formula is being divided by zero. This makes no sense mathematically, so the function returns a #DIV/0 error. For example, if you are trying to divide a value by a function or operation that results in a 0 value, you will get a #DIV/0 error.

In the image below, we are trying to divide the number 7 by the difference between values in A1 and A2, which results in 0. Since it is not possible to divide 7 by 0, the formula returns a #DIV/0 error.

This error also happens when you are trying to apply the AVERAGE function on a range of blank cells. This happens as the AVERAGE function needs to divide the SUM of the values with the number of the values, and if the range is blank, it’s equivalent to dividing by 0.

How to Fix the #DIV/0 Error

To resolve this error, you can try to first find out why your denominator is evaluating to a ‘0’ value. Select parts of the denominator in the formula bar to see what each part evaluates.

You can see the result of the calculation is a little popup right on top of the formula bar. Once you find the cause, you can make changes to your formula accordingly.

If that does not work, you can check if any part of the formula’s denominator links to a blank cell or range. If so, then you can either fill required values into the cell or select the required range for the formula.

To avoid problems like this altogether, you can again use the IFERROR statement to display a particular message whenever an error is encountered:

#REF! Google Sheets Error

This error occurs when you have an invalid reference in your formula. There may be different types of invalid references:

Missing Reference:

This kind of #REF! error occurs when you might be referencing a cell in your formula that is missing. In the image below, we have two values (45 and 50) in cells A1 and A2. We used the formula: =A1+A2 to find the sum of these two numbers in cell A3.

Till now it is fine. But what happens if we delete row 2? Then the value in cell A2 goes missing. This causes the formula in cell A3 to return a #REF! Error.

Circular Dependency:

This kind of #REF! error occurs when your formula might be referencing itself. In the image below, we used the formula =SUM(A1:A3) in cell A3. This means the formula, in addition to referring to values in A1 and A2, also refers to itself.

So the function will keep going round in circles trying to be both an input and an output cell, which would result in an infinite loop and a system crash. So to avoid this, the formula detects the circular reference and returns a #REF! error.

Lookup that is Out of Bounds:

This kind of #REF! error occurs when using VLOOKUP, and trying to reference a cell that is outside the bounds of the VLOOKUP range parameter.

In the image below, the VLOOKUP formula is trying to look for the search parameter in the third column of the source table, even though the range parameter specified only consists of 2 columns.

Since you’re trying to return a value outside the range specified by the formula, it returns a #REF! error.

How to Fix the REF Error

Read the error message to find out what kind of #REF! error your formula has. If there are missing references, you can see the exact reference that is erroneous in the formula bar. The reference causing the problem will be replaced with an #REF!.

Once you’ve identified which cell is missing, you can replace the #REF! with the correct cell reference.

If there’s a circular dependency issue, then you need to identify the range of cells in the formula and change it to make sure it does not include the current cell too.

Finally, if there’s an out of bounds error, then either make sure the search term exists in the search table or make required changes to the search term itself. You can also avoid this type of error from happening by using the IFERROR function.

#VALUE! Google Sheets Error

This error occurs when one or more parameters in your formula are of a different type than what is expected. So if a function only accepts numbers as a parameter, but you have a text value in the cell being referenced, then you will end up with a #VALUE! error.

In the image below, a backslash character () got added to the end of the number in cell A2 by mistake. So when we use the formula =A1+A2 in cell A3, we are trying to add a number to a text value, which is invalid. Therefore, the formula returns a #VALUE! Error.

Similarly, in the next image shown below, the cell A2 has a space in it, which makes it get treated as text. If the cell had been blank, then the ‘+’ operator would have assumed the value as a ‘0’.

Again, since we are trying to add a numeric value to a text value (‘ ‘), the formula returns a #VALUE! error.

How to Fix the #VALUE! Error

Once again, the error message should give you a clue about the source of the error.

To resolve the #VALUE! error, you can try one of the following:

#NAME? Error

This error commonly occurs when there is a problem with the syntax of your formula. It could be due to a spelling mistake, an incorrect named range, or the presence or absence of quotation marks.

For example, if you forget to add quotation marks around a string value, then the formula considers it as a named range, and if a named range by that name doesn’t exist then it returns a #NAME? Error.

In the image below, we did not put quotation marks around the first parameter of the CONCAT function, causing it to consider the first parameter “Peter” as a named range. Since it cannot find a named range by that name in the sheet, it returns a #NAME? error.

Related: Google Sheets CONCATENATE

Similarly, in the next image below, we misspelled the function name AVERAGE, causing the formula to return a #NAME? error.

Again, in the next image below, as you can see, we missed adding a comma in between the cell references A1 and A2. This is a syntax error, due to which the AVERAGE function returns a #NAME? error.

How to Fix the #NAME? Error

Since this kind of error usually occurs due to a syntax issue, the best approach is to first check if there are any spelling mistakes in the function name or named range names.

You can also check to see if all string values are enclosed in quotations and that there are both opening and closing quotations for every string value.

If using cell reference ranges, check to see if you have them separated by a colon symbol. (‘:’).

#NUM! Error

This error occurs when your formula has invalid numeric values. For example, if you try to find the square root of a negative number or if a calculation results in a very large number that is outside the scope of Google Sheets.

In the image below, we are passing a negative value to the SQRT function, which is not acceptable, so the function returns a #NUM! error.

Similarly, in the next image shown below, we are trying to find the result of 150 raised to the power of 200, which is a very large number (more than 1.79769e+308, which is the limit). The formula thus returns a #NUM! error.

How to Fix the #NUM Error

This error invariably means there’s an error with one or more of your numeric arguments.

Usually, the error message gives a good clue about the issue causing the error. For example, if the error message says “Function SQRT parameter 1 value is negative.

It should be positive or zero.” it’s quite clear that your first parameter in the SQRT function evaluates to a negative number.

So to resolve it, you can re-evaluate your calculation to make sure it doesn’t result in a negative number. To see what the result of an operation is, you can select the operation by highlighting it in the formula bar. You should be able to see the result of the calculation is a little popup right on top of the formula bar.

Keep in mind though, that if an operation evaluates to a very large number, you would not be able to see its result in the popup. This, in itself, can give you a sign of the source of the #NUM! error.

#ERROR! Message

This type of error is unique to Google Sheets. It usually occurs when Google Sheets cannot make sense of your formula, so it cannot tell you exactly what is wrong with it. There may be a number of reasons you see this error.

It may be because you forgot to add an important operator between cell references, values, or parameters. For example, forgetting to add a comma between cell references of non-contiguous cells in your sheet.

It could also be because your number of opening brackets doesn’t match the number of closing brackets. In the image below, we used a $symbol to refer to a price amount, but Google sheets mistake it for an absolute reference and the formula, then, does not make sense.

Sometimes this error also occurs if you don’t want to enter a formula, but want to start your text with an equal to sign (‘=’). However, as soon as Google Sheets sees the equal to sign, it assumes that you are trying to use a formula. But the text following the equal to sign does not make sense, so you get a #ERROR message.

How to Fix the #Error

Resolving this error can sometimes be tough, especially if you have a particularly complex formula. This is because the error message does not give any clues about the error. However, there are a few steps that you can take to see if it helps resolve the problem:

• Check if the number of opening and closing brackets match and that they do not cause any logical errors.
• Check if there are colons and commas separating contiguous and non-contiguous cell/range references.
• If using currency or percentage symbols, make sure you remove them before applying them to the formula. If you need to use them, you can enter them as plain numbers and later format the result with an added percentage or currency symbol.

Formula Parse Error Message Popup

You see this error message in the form of a popup that does not let you enter your formula until you resolve the error. One comes across this error quite rarely.

It usually occurs when there’s some fundamental problem with your formula. In most cases, you will see it if you accidentally entered a character or symbol in your formula before pressing the return key.

In the image below, we accidentally entered a backslash character at the end of the formula before pressing the return key. This type of mistake is quite common since the backslash key is right above the return key on the keyboard.

A mistake like this will most likely result in a formula parse error message popup.

How to Fix the Formula Parse Error Message Popup

It’s best to avoid this error from happening in the first place. Always double-check your formula before hitting the return key.

Make sure there are no surplus characters hidden in your formula. Also, make sure there are no missing characters or cell references.

The #NULL Error in Google Sheets

There’s no fix for this error. But only because it doesn’t exist in Google Sheets. It’s only an Excel error.

Functions to Help Fix Formula Parse Errors

There are a few functions that you can use to help identify formula issues and resolve them. Let’s take a look at them and what they do.

=ISNA(value)

Checks whether the cell has an #N/A error

=ISERR(value)

Checks for every other type of error (not including #N/A)

=ERROR.TYPE(value)

Shows the type of error by returning a number value:

• 1 for #NULL!
• 2 for #DIV/0!
• 3 for #VALUE!
• 4 for #REF!
• 5 for #NAME?
• 6 for #NUM!
• 7 for #N/A
• 8 for all other errors

Google Sheets Formula Pass Error Extra Tips

Sharing with Other Countries

Some international sheets may use semicolons ; instead of commas , so try changing those out if you’ve copy-pasted a formula.

Highlighting

If you double-click the cell with the formula, sheets will usually underline the misfunctioning part of the formula in red. That makes it easier to pick apart your formula.

Formula Parse Error in Google Sheets FAQ

What Does Formula Parse Error Mean?

A formula parse error essentially means there is an error in the way you’ve entered a formula into Google Sheets.

How Do I Remove Formula Parse Error?

Troubleshooting will remove a formula parse error. There are several different types of errors that can occur. Click the cell to find the type you need to fix then follow our guide to fix that particular error.

How Do I Stop Formula Parse Error in Google Sheets?

The most common reason for a formula parse error is a mistyped formula. So, to minimize them make sure you’ve got the syntax and the figures right.

How Do I Get Rid of DIV 0 in Google Sheets?

The #DIV/0 error occurs when the denominator results in a zero. You can’t divide by zero, so check your formula to find where the cause for this error could be popping up.

How Do I Fix the Error Number in Google Sheets?

The #NUM error occurs when the result unexpectedly returns a negative value in a formula that only works with zero or positive numbers. You need to find the part of your equation that is causing the negative value result.

Fixing Your Formula Parse Error Google Sheets Issues

In this tutorial we discussed formula parse errors in Google Sheets in detail, explaining what they are, why you see them, and how to resolve them. We hope our guidelines help you come to terms with parse errors in your Google Sheets. If you’re still having trouble fixing a formula parse error, feel free to ask about it in the comments below.