Find out how to avoid, fix or fix formula errors in Excel
To take advantage of all the features that Excel offers, such as inserting formulas into text boxes in Excel or hiding and showing a formula in a spreadsheet, the first thing you need to know is how to avoid and fix formula errors that occur in Excel . Next we will talk about the most common errors and their solution.
Correct # DIV / 0!
This is usually one of the most frequent errors in Excel; happens when a number is divided by zero (0) or a cell containing white space; for example: = 6/0. The reason for this error is that yes it is a mathematically impossible operation ; or it can happen because you entered an invalid argument for some functions, giving the error # DIV / 0 !.
The # DIV / 0! it can be easily avoided, the important thing is to make sure that you do not enter the number zero (0) as a divisor in any formula; nor leave the space blank without entering a number.
In case the # DIV / 0! You can correct it by changing the cell reference which belongs to the formula; that is, change the cell that contains the value zero (0) or that does not have a number and is empty.
Another option is to write # N / A in the cell corresponding to the divisor in the formula that is giving the error; placing # N / A indicates that the division factor cannot be displayed or is not available and therefore the # DIV / 0 error!
Use the yes function to correct the # DIV / 0! Error.
One of the most effective and practical ways to solve # DIV / 0! He's using the function yes ; indicating that a certain value, in this case the cause of the error, exists; and instead of showing the # DIV / 0! error, it can show the value zero (0) or less in the formula result.
What is #VALUE! Error and how to fix formula errors in Excel?
The #VALUE error is one of the more general errors in Excel and indicates some incorrect data which can be difficult to obtain; they are usually common mistakes that have several workarounds.
The main thing is to determine the source of the error in order to fix it; A frequent cause of this error in a formula is because there are hidden cells which, although they don't appear to contain any values, do.
To solve it you can try with the cell replacement; in this case it is necessary to select all the cells that refer to or affect the result of the formula, then choose the "search and replace" option; there you can replace one cell or all of those with blank spaces.
Another way to fix it is use filters, in order to be sure that there are no traces that could affect the results; To do this, you need to go to the "sort and filter" option and then choose the "filter" option.
Then you need to uncheck the «select all» box and select the «blank spaces» option. It is important to delete any blank spaces and suppress them, in order to delete any characters that may be causing the error.
Error # N / A
The # N / A error indicates that Excel could not find the reference value or, as the initials indicate, " not available ". To avoid this error you can use the «SEERROR function»; thus being able to determine in which cell the # N / A error exists.
Another possibility to correct this error is to verify that the lookup value originally exists; that is, it checks the cell from which the data is taken as a reference for a formula.
It is also important to check the cell format; For example, the data considered for the formula may refer to numbers; if a cell contains the value in the text settings, it must be changed.
TagsEccellere
