Formulas not working in Calc that worked in Excel

[rpestinger]

I have a spread sheet that worked under Excel but has problems with Calc. I saved the spread sheet in Excel XML format and some columns worked while others do not using the same formula. When I saved it to ODF none of the columns calculated, they all show #VALUE! I am using the latest version 4.2.6.3 on Windows 7 64 bit system. I tried the same spread sheet on my Ubuntu 14.04 using Libre Calc with the same results. A typical formula I use is =SUM(LEN(J2:J402)-LEN(SUBSTITUTE(J2:J402,“W”,""))), I am adding up the number of times a single W appears in a column. With the exception of a column identifier some columns work, using the same formula, while others do not.

Thanks in advance for any help.

Richard

[karolus]

The Formula works for me, also after close and reopen a Document.

Be care to enter this kind of Formula as Array-formula:
ctrl+shift+enter respectivly [x]Array-option in FormulaWizard

[rpestinger]

Problem solved. It was an array problem that somehow became a problem in Calc. It was an array in Excel then for some reason in lib it was no longer an array. I clicked on the problematic formulas went to function and checked the array box, clicked okay and all was good.

Thanks
Richard