Analytics Tools

SPREADSHEET FUNCTIONS REPLACEMENT

From time and time again people have complained about the usage of ‘ISERROR’ function in Xcelsius. Some say that it keeps giving them errors while others say that they are just not able to export the model. I am presently using Xcelsius 2008 Version 5.0.0.99, and I have never faced any such issues. But since this has been brought up so many times we thought of finding a workaround. There is a simple way to avoid ISERROR and that is by using COUNT function.

This is how it works..

Suppose you are expecting an error in Cell D5, and if you are using ‘ISERROR’ formula in E5, replace it with this formula “=IF(COUNT(D5)=0,TRUE,FALSE)” .Whenever there is an error in cell D5 count of that will become 0 else it will be one. Refer to the picture below.

There are some other functions which have replacements (As mentioned in the user guide)

1. Replace DATEDIF() with date arithmetic

2. Replace ISTEXT() with IF(TEXT(value)="",1,0) & ISNUMBER() with NOT() of the same if function..

3. Replace ODD() with MOD() and ROUNDUP()

4. Replace SUBSTITUTE() with FIND() with REPLACE().