How Excel formulas are incorrectly checked

the same and different of Excel formula and function

The first thing to do is to understand the two concepts of Excel formulas and functions. What is a formula? It is the use of subtraction and other operational symbols, a number of values, text, functions and other combinations, and have a mathematical formula to produce a result. For example, the following are all formulas:

=2+5-3*2 (Constant operation)

=A-B/C (Variable operation)

= "Office Notes" & "Really nice" (character operations)

=sum (a,b) *6 (function operation)

=A1*B1+C1 (cell operations that contain numeric values)

So now the question is, what is a function? What is the difference between it and the formula? In fact, the essence of a function is a formula, but it is a formula that is wrapped.

For example, we calculate the Triangle area in Excel, you can enter the triangle in the A1 cell of the bottom length, in the B2 cell input triangle height, then the area calculation formula is:

=a1*b1/2

And if we wrap it into a function, let's say our custom function is:

Function Triarea (base,height)

Area = Base * HEIGHT/2

End Function

where function ... The End function indicates that this is a function named Triarea, and the two parameters representing the bottom and height are base and height. The middle line of code is the Triangle calculation formula. After we calculate the Triangle area, we can directly enter the following function:

=triarea (A1,B1)

We just need to input the function and its required parameters, without having to worry about how it operates internally, which is much more convenient for the user. What is wrapped in a function may be a simple formula, or it may be a very complex number of formulas, not a bit like our familiar DOS batch.

Intelligently enter Excel formulas with wizards

For ordinary users, we do not need custom functions, Excel has a number of built-in functions, we only need to call directly on it. But for beginners, it's easy to make mistakes when typing a function manually, and some functions don't quite understand the meaning, and we can use the Excel tool to intelligently enter formulas.

Suppose we asked for the sum of the numbers in three cells, you could click on a blank cell, and then tap the FX button above.

In the Open Insert Function dialog box, first select the category of functions you want to use, such as Sum function sum belongs to the "math and trigonometry" category, after the function is found in the list, here is a simple description of this function. If you don't know what kind of function you want to use, you can enter keywords in the top search box, such as "sum" and then click "Go", and the wizard will give you a recommendation.

Click "OK" to enter the function parameter setting window, the cursor is positioned in the first parameter box, directly by clicking to select the cell that holds the parameter, for example, the first parameter in this example is cell A1, just click it. Of course, you can also manually enter various parameters. In turn, set up Click OK, you can see that the formula in the cell has been automatically completed.

Excel Formula Intelligent Error Checking

If there is an error in the formula that was incorrectly manipulated or manually entered, the cell in which you saved the formula will display an exclamation point icon on the left side of the cell, and click the Display Drop-down menu, which will first give you a preliminary error message.

For more information, click Help on this error, and open the Help pane on the right, with a more detailed description and instructions on how to correct it.

Tips: Excel formula error message

The result returned by 1.#####! exceeds the width of the cell, or the date-time formula for the cell produces a negative value.

2. #VALUE! The wrong parameter or Operation object type was used.

3. #DIV/o! This error occurs when the formula is 0 apart.

4. #NAME? The error name that Excel cannot recognize is used in the formula.

5. #N/A, there are no available values in the function.

6. #REF! The cell reference is not valid.

7. #NUM! There is a problem with a number in the function.

8. #NULL! This error occurs when you attempt to specify an intersection point for two disjoint zones.

Error checking inside Excel formula

Sometimes the formula you enter does not have an obvious error, but the result is incorrect, so try the "show calculated steps" tool in the error prompt drop-down menu. Alternatively, you can switch to the Formulas tab, and then click Formula Evaluation to pull it up.

Click on the "Evaluate" button, you can see the formula in a step-by-step calculation process, so you can tell which step is the problem.

Of course, Excel functions and formulas, can not be mastered in a day or two, need a long-term learning process, but when we are familiar with the Excel formula error correction function, we can take less detours.