Microsoft Excel is one of the programs that belong to the Microsoft Office suite; It is characterized by the various functions it incorporates, which allow a large number of calculations to be carried out. In addition, it allows people to make an Excel spreadsheet to calculate rental prices, for example, and allows companies to manage finances, make budgets, etc. However, this valuable tool can present some errors, we will teach you how to avoid, solve or correct formula errors in Excel.
Learn how to avoid, fix or fix formula errors in Excel
In order to enjoy all the features that Excel has to offer, such as inserting formulas into text boxes in Excel or hiding and showing a formula in a spreadsheet, the first thing you you should know is as avoid and fix errors in formulas that occur in Excel. Next we will talk about the most common errors and their solution.
Fix bug #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 that contains white space; for example: =6/0. The reason for this error is because it is mathematically an impossible operation; or it can happen because you have entered an invalid argument for some functions, giving the #DIV/0! error.
The #DIV/0! it can be easily avoided, the main thing is to be sure not to include the number zero (0) as a divisor in any formula; nor do you leave the space blank without entering a number.
In case the error #DIV/0! You can fix it by changing the cell reference that belongs to the formula; that is, modify the cell that contains the value zero (0) or that has no number and is blank.
Another option is to type #N/A in the cell corresponding to the divisor in the formula that is giving the error; Placing #N/A indicates that the divisor factor cannot be displayed or is not available and therefore the #DIV/0! error is corrected.
Use the yes function to fix the #DIV/0!
One of the most effective and practical ways to resolve the #DIV/0! It is using the yes function; which indicates that a certain value, in this case the cause of the error, does exist; and instead of displaying the #DIV/0! error, you can display the value zero (0) or not in the result of the formula.
What is the #VALUE! error, and how to fix formula errors in Excel?
The #VALUE error is one of the most general errors in Excel and indicates some incorrect data that may be difficult to obtain; They are usually common errors that have various solution alternatives.
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 that, although they do not seem to contain any values, do.
To fix it you can try cell replacement; in that case you must select all the reference cells or cells that affect the result of the formula, then choose the option “search and replace”; there you can replace a cell or all those with blanks.
Another way to fix this is by using filters, to make sure that no traces are left behind that could affect the results; To do this, you must go to the “sort and filter” option and then choose the “filter” option.
Then you must uncheck the “select all” box and check the “blank spaces” option. It is important to delete each white space and suppress them, in order to delete any characters that may cause the error.
Error #N/A
The #N/A error indicates that Excel was unable to find the referenced value, or as its initials denote, “not available.” To avoid this error you can use the “SI.ERROR function”; thus managing to determine in which cell the #N/A error exists.
Another possibility to correct this error is to check that the lookup value does originally exist; that is, check the cell from which the data is being taken as a reference for a formula.
It’s also important to review the cell format; for example, it is possible that the data considered for the formula refers to numbers; if any cell contains the value in text settings, it must be modified.