Wednesday, July 7, 2010

Formula problem in Open Office and Microsoft Office

Be careful when using formula for different type of spreadsheet software such as Open Office 3 and Microsoft Office 2003. Although using the same XLS file extension, the result in formula will be different. The error was slightly simple, but will cause you a lot of problems at the end. The error I found recently:

=IF(A1=0,"",A1)

In Open Office the formula result when A1 = 0 is 0
In Microsoft Office the formula result when A1 = 0 is ""

The result "" will cause error when you try to use it with other formula such as multiply or any other math formula.

Just revise the "" to 0 (zero)

=IF(A1=0,0,A1)

And everything will be fine.

Already tried to open the XLS (Open Office version) in Office 2007, and still got the same "" result. So, it's better to change to string conversion to zero instead.

3 comments:

Innovation management said...

Great post!! Thanks for sharing such an wonderful information ...

Custom LAN/WAN Applications said...

Great!!! You have shared a good post, and your this post taught me some useful formulas of MS Excel..
thank you for the post..

Ecommerce website developers said...

I must say that you have good post here, that is very informative. You guys are doing great work here. I have learnt many things from it. I hope to see some more articles from you. Thanks for sharing.