Shulou(Shulou.com)11/24 Report--

Original title: "what if there is a NAME error in the input formula?" An article teaches you! "

In the process of learning Excel, most people will encounter all kinds of problems.

Once you encounter a problem, you may be at a loss.

The most common problem is that when entering a formula, there will be all kinds of garbled code in the cell. (rookies are often called garbled, which is called error value in Excel. )

Today, let's take a look at the problems related to NAME errors.

01. NAME error caused by writing error ▋ function name writing error

When we first came into contact with the form, the simplest and most frequently used functions were summation, maximum, minimum, average, counting and other functions, such as the following to calculate the sum of sales of all personnel:

Normally, we enter the formula in the B6 cell:

= SUM (B2:B5)

The result is as follows:

But sometimes it looks like this:

Huh? What's going on?

It is clear that the input is a summation function, why there is such an error?

After careful observation, only to know that it was written as "sun", (Sun), wrong one letter, a difference of 108000 miles!

For Excel, this type of error value is usually displayed, starting with a "#" sign, followed by the English word "NAME".

At this point, we can click the error prompt button next to (

A drop-down menu appears

Inside we can understand this error prompt to help us analyze the cause of the error and change the error.

For example, the cell here shows "# NAME", and the first prompt in the drop-down menu is: "invalid name" error. You can specifically check whether you have written the wrong function name.

You can also click the "help about this error" command to view specific help information.

After you find the cause of the error, you just need to change it to the correct function name.

The name defined by ▋ is miswritten.

Using a "definition name" in a table sometimes looks fresher and easier to understand.

For example, we want to define the green cells into names, and then sum the cells.

As shown below:

If the name of the definition is misspelled, the NAME error value will also occur.

If you take a closer look, there is a space in the middle of the green, so the Excel cannot recognize it.

At this point, just delete the extra spaces.

Summary of ▋

The writing of function names in ❶ needs to be exactly the same as in Excel (size-insensitive) before Excel can recognize it.

For ❷, the defined name also needs to be exactly the same as the custom name (size-insensitive), with no extra spaces or other characters.

02. NAME error caused by text without double quotation marks many rookies write formulas like this:

If the quarterly sales volume is greater than 300, it will be displayed as "up to standard", otherwise it will be displayed as "not up to standard".

The formula looks fine, but it is shown as a NAME error. What is the reason for this?

The problem lies in the text.

If you use text in a function formula, you need to put a pair of double quotes on both sides of the text.

As shown below:

After adding a pair of double quotation marks, the result is correct.

But sometimes it's still wrong:

Double quotation marks have been added, why is it still wrong?

The answer is: the double quotation marks here must be entered in English half-corner, not in Chinese.

Summary of ▋

The text entered in the function needs to be in double quotation marks, and it must be in English half-corner state, otherwise there will be a NAME error.

03. NAME errors caused by different versions when we declare some items, we sometimes need to save the form of version 2007 or above (suffix: ".xlsx, .xlsm", etc.) as version 2003 (suffix: ".xls").

Then upload it to the Internet or send it to someone else.

If the other party is using version 2003 or lower, there may be a NAME error when opening the form you have made.

As shown below:

We want to get the formula text of B6 cell in B7 cell. In version 2016 (the following function is a new function in version 2013), enter the formula:

= FORMULATEXT (B6)

Then, if you save the table to version 2003 (suffixed with ".xls"), a compatibility check dialog box will appear with some prompts:

At this point, if the other party uses a version that is lower than the Excel version you are using, the following NAME error message will appear.

In this case, because the lower version does not support some functions in the higher version, resulting in a NAME error, we cannot use the higher version of the function when making the table.

It is also possible to copy and paste the results returned by the higher version of the function into values and remove the formulas.

Summary of ▋

There are many useful functions in the higher version, but they are not available in the lower version (version 2003 or other lower version).

The most common ones are IFERROR (fault tolerant function), SUMIFS (multi-conditional summation function) and so on.

PS: these functions need to be used in Office 2003 and above.

So how to solve this problem?

❶ for IFERROR (fault-tolerant function), we can use the lower version of the "IF+ISERROR" combination function instead of the former.

The effect is the same, but in terms of writing and running efficiency, it is not as good as the high version.

❷ for SUMIFS (multi-conditional summation function), we can use SUMPRODUCT (summation function) array formula instead.

You can also use auxiliary columns to merge multiple columns and multiple conditions into a conditional column with the concatenator "&", and then use SUMIF (single conditional summation) to solve the problem.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Spiegel Zaixin Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.