Handling Excel Errors
Often Excel formulas return unexpected values that are either outright errors or deviate from what one would expect.
All such actual or perceived errors can be divided in the following three categories ordered from least severe to most severe:
The following screenshot shows a simple example for each error category using native Excel formulas.
The Diagnosed Error in cell D4 is the easiest type of error since it gives a hint for what caused the failure.
In the shown example, Excel returns the visible text label #DIV/0!, which tells in a not particularly verbose fashion that the failure is caused by an attempt to divide a number by 0.
Unfortunately, native Excel formulas only very seldomly return this type of diagnostic, perhaps because it is difficult to squeeze meaningful explanation in some short label for errors caused by more complex conditions.
The Unknown Error in cell I4 is quite dreadful as it gives no hint whatsoever for what went wrong.
In the shown example, Excel returns the visible text label #VALUE!, which carries little to no information, even though the failure is clearly due to trying to divide a number by some text.
Apparently, the Excel developers could not come up with some short text label like the #DIV/0! that could be used to describe the case here.
Lastly, the Wrong Result in cell N4 is the absolute nightmare of every Excel user because the formula returns a seemingly valid value, which only after closer inspection is seen to be wrong.
Unfortunately, the VLOOKUP formula in cell N4 not only fails to raise any alarms, but it also does not inform the user with regard to the steps it undertakes to calculate the displayed result of 3 million.
On a first glance, it is quite perplexing to understand why the population for London is calculated as 3 million, even though the table defines London's population as 9 million.
The only course of action in dealing with this error is to look up the documentation for the VLOOKUP function and try to understand it in depth with the hope of discovering what causes the wrong result.
The answer of course is that the VLOOKUP formula expects up to 4 arguments, but the last argument is the optional Range_lookup boolean that is set to TRUE by default if not explicitly supplied.
But, Range_lookup being TRUE means that only an approximate match is applied, which works in a sensible way only if the city names at the left column are sorted.
In this case, the city names are not sorted, with the erroneous output number of 3 million being the result.
Let us now see how Deriscope handles these three types of errors.
The following screenshot shows a simple example for each error category using Deriscope formulas.
The Diagnosed Error in cell D4 is produced because the function attempts to calculate the logarithm of -1, a mathematically impossible task in the world of real numbers.
The Deriscope formula returns the text &ERROR!_D4:1.1, which is the of an , of which the contents can be seen inside the by selecting the cell D4 while the wizard is open.
In this case, the object is of and therefore contains only some readable message that appears at the very bottom of the wizard explaining that it cannot compute the logarithm of the negative number -1, as shown below:
The Unknown Error should never happen with Deriscope Excel formulas.
Of course it is unavoidable in practice, but best efforts are taken that a meaningful diagnostic message is returned in all situations.
The more demanding case of the Wrong Result in cell N4 emerges because the formula is supposed to return the price as of 9-Mar-22 of a zero bond with notional = 100 and maturity = 9-Mar-23 using a yield curve built with a flat 1% rate.
The time distance from 9-Mar-22 to 9-Mar-23 is one year => the discount factor implied by a flat 1% rate ought to be close to 0.9 and therefore the price of the zero bond should be close to 99.
Paradoxically, the function in cell N4 returns a much bigger number close to 99.99.
Fortunately the user has a chance to understand and correct this error because Deriscope exposes several details regarding the various objects that participate in this function's outcome.
In situations like this, it is highly recommended to inspect the actual contents of the involved objects using the wizard because they may sometimes contain data that deviate from one's expectations.
In the current case, one could decide to inspect the contents of the Yield Curve object in cell N9.
Selecting the cell N9, the following appears in the wizard:
To the trained eye, the highlighted value Flat Rate= 0.0001 strikes as unnaturally small, given the input rate of 1%.
This observation tells the user to revisit the spreadsheet input for the flat rate, where one may now realize that the entry of 1% in cell N14 should be changed to 1 because the key in cell M14 ends with (%), which means the numbers are interpreted as percentages.
As a last resort, Deriscope allows the user to inspect a log of the algorithmic steps that were undertaken in the actual calculation.
It is clearly not very simple to look through the hundrends of steps involved in a pricing routine, but in certain circumstances it is better than nothing.
This method works by selecting the cell N4 that contains the pricing result, while the wizard is open.
The following message appears at the wizard:
Then one must click on the blue hyperlink label here in the highlighted phrase to get to the following:
In 99% of the cases, one is advised to ignore the appeared text and jump straight to the main pricing routine by clicking on the circled hyperlink label here to get to the following:
The displayed information is quite readable and follows the thread of the execution logic with blue-colored labels being hyperlinks to more detailed information.
While not guarranteed, it is possible that by perusing all these data, something odd would spring to one's attention that would lead to the cause of the wrong result.
Last course of action.
If all rescue attempts have been proven futile, it is a good idea to seek advice from a colleague or from this software's vendor.
The latter can be most easily accomplished through the Excel facility.
The offending formula can be also sent to Deriscope support using the method.