Functions can help you automate your calculations and pull important insights from your data set. However, if something goes wrong with these functions, errors occur and make your spreadsheet less reliable. These errors usually have a cascading effect, especially if your functions refer to and depend on each other. In this post, we'll look at five of the most common errors your spreadsheet formula might throw at you and how to troubleshoot them.
The infamous #NAME error is also the easiest to troubleshoot. This error is often the result of a typed error in the formula. This could be a spelling mistake in the function name, the range, or even the sheet name. Unbalanced brackets or quotes could also be at fault. To rectify the #NAME error, take a look at your function and check for possible spelling mistakes, typographical errors, or open brackets and make sure you've furnished all necessary arguments as per the syntax.
A #VALUE error usually means one of the two things: The function does not have sufficient arguments, or the arguments in the function are not the right data type. For instance, if you intend to add values in cell C5 by the value in cell C6, they must both be numbers. If one of the cells is a text string, then the spreadsheet throws a #VALUE error. To troubleshoot the function, check that all necessary arguments of a function are furnished and in the right format.
This is technically not an error. Your formula is working properly, but there is no value available for the spreadsheet to return as the answer. For instance, if you use a VLOOKUP function in a table to return the sales numbers for transactions handled by Adriana, and there are no records of Adriana in the tables, the formula will throw an #N/A error.
#CIRCULARREF is an error that results when functions directly or indirectly refer to themselves. For instance, if you're trying to create a formula in cell A1 that calculates the sum of multiple cell values, and you try to include A1 in the calculation, you'll get the #CIRCULARREF error. To troubleshoot, edit the formula and remove the function's reference to itself. If left unchecked, these functions could overwork and slow down your spreadsheets.
There are quite a few formulas in Zoho Sheet that support array results. But existing content in a spreadsheet can obstruct spill results. When this happens, Zoho Sheet throws a #SPILL error. To troubleshoot, select the cell with the #SPILL error and click the error icon to view possible resolutions. Simply selecting and moving the cells obstructing the results, or deleting their contents, can resolve the #SPILL error.