If you have an #VALUE, #DIV/0! or #NUM! error in Excel, it could be as a result of a direct link to the cell, or it could be that somewhere right in the beginning there is a problem. You may need to track back through all the cells to find where the problem cell is. There is a tool for tracing errors like #VALUE!, #DIV/0! or #NUM! direct to the problem cell.
#VALUE! or #DIV/0! or #NUM! sourceĀ in the same sheet
So below, we have a #VALUE error in the IRR calc, but where is the real problem? You can click on the cell and then CLICK FORMULAS.ERROR CHECKING and then TRACE ERROR.
You will see that a red line appears and it takes you straight to the source of the error. In this case one of the inputs has a divide by zero in it. You can correct this.
#VALUE! or #DIV/0! or #NUM! source in a different sheet
In the above case, the problem cell is in the same sheet, so Excel takes you straight to it. However, if the problem is in a separate sheet you need to trace a bit back yourself.
As shown below, the trace error ends at a cell that is linked to another sheet (note the dashed line showing a link to another sheet). You need to follow these links to see where the problem is (learn how to navigate using this dashed line in the Fundamentals course). You may need to run the trace error again on the other sheet but it will be quicker than following everything manually.
Learn how to get formulas to ignore these error message with IFERROR (or the older but still useful ISERROR).
Related
Difference between IFERROR and ISERROR in Excel
How to safely delete sheets so there are no REF errors