Excel applies the formula error code "#NAME?" Solving method

Some functions and formulas are often used when you tab in Excel, and you return some error codes if you use them incorrectly. Of course, the reason for these error codes is often the result of our operation error, we can according to the wrong agent in Excel to analyze the specific reasons, so as to accurately solve the problem! We've already analyzed several common error codes, and today we'll talk about the code "#NAME?".

Error code: #NAME?

In the first case, if the formula returns an error value of #NAME?, this is often because the text that Excel does not recognize is used in the formula, such as the name of the function is misspelled, the range or cell name that is not defined is used, the text is quoted without quotes, and so on.

Workaround:

① we want to find the average of the A1:A3 region, the formula in B4 cell is "=aveage (A1:A3)", and the "#NAME" appears after carriage return. Error, because the function "average" has been misspelled incorrectly as "Aveage", and Excel is not recognized and therefore has an error. You can correct the error by spelling the function name correctly.

② Select C4 Cell, enter the formula "=average (data)", also appeared after carriage "#NAME?" Error. This is because in this formula we use the region name data, but the name is not yet defined, so there is an error.

③ the correct method is: Select the "a1:a3" cell range, and then select the menu "name → definition" command, open the Define Name dialog box, in the text box, enter the name "data" click the "OK" button.

④ return to Excel edit window, you can see the error is missing;

⑤ Select D4 Cell, enter the formula "=if (a1=12, this number is equal to 12, this number is not equal to 12)", after the carriage return appears "#NAME?" Error (for example, 12) because quotation marks are not added when quoting text;

⑥ modified by adding quotes to the quoted text, with special attention to the quotation marks in the English state. So the formula is changed to "=if (a1=12," This number equals 12 "," This number does not equal 12 ")";

The second case: the function name input error. For example, by mistakenly converting the formula "E1=sum (A1:D1)" In the edit Bar to "E1=sym (A1:D1)", a "#NAME" is generated in the E1 cell. Error.

Workaround: Less-skilled users would be better off using functions such as function wizards to enter functions. For example, let the cursor stop at the cell where you want to enter the formula, and then click the Edit Formula button (equal sign) in the Excel edit bar to insert the "=" into the edit bar. Then click on the Left Function list behind the button to open the Drop-down menu, select the desired function, open the Function Related dialog box, according to the prompts, drag the mouse to select the cell or range of cells you want to calculate, and click OK when finished.

In the third case, the text is used without double quotes in the formula, and Excel treats it as a function name. For example, a1=68, a2=96, if you want to make B1, B2 Show "health costs 68", "Health fee 96", you can use the formula: "=" Health fee "&A1", "=" Health fee "&A2", if the text quoted ("Health fee") missing double apostrophe, will appear "# NAME? " Error.

Workaround: Refer to the text in a formula don't forget to add a double apostrophe.

Fourth: The cell range reference in the function is missing a colon, or the colon is lost to another symbol. For example, the formula: "=sum (A1C10)" is the result of "#NAME?" Error.

Workaround: An unskilled user would be best to refer to a range of cells with the mouse-dragging method. For example, you can first enter in the Excel formula bar: "=sum ()", then leave the cursor in the middle of the parentheses, and then, with the mouse, select the range of cells you want to calculate A1:C10, then "A1:C10" automatically enters the parentheses.