
I have to process xls files that sometimes have heavilynested formulas. Here's an example:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C39;$Definitions.$C$3;$Definitions.$D$3);$Definitions.$C$4;$Definitions.$D$4);$Definitions.$C$5;$Definitions.$D$5);$Definitions.$C$6;$Definitions.$D$6);$Definitions.$C$7;$Definitions.$D$7);$Definitions.$C$8;$Definitions.$D$8);$Definitions.$C$9;$Definitions.$D$9)
Trying to evaluate this gives me:
Formula Error: Wrong number of arguments for SUBSTITUTE() function: 7 given, either 3 or 4 expected
By my count, no single instance of SUBSTITUTE has more than 3 arguments. Could this be a bug in the formula parser? Is there any hope of getting this fixed? :)


Coordinator
Nov 18, 2010 at 8:42 AM

You're using ; as an argument separator rather than ,
Have you set the locale for the calculation engine, or are you using the default en_us?



Oops! I copied that out of OpenOffice.org, which autoconverts the separator to semicolons. In Excel it shows up as:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C11,Definitions!$C$3,Definitions!$D$3),Definitions!$C$4,Definitions!$D$4),Definitions!$C$5,Definitions!$D$5),Definitions!$C$6,Definitions!$D$6),Definitions!$C$7,Definitions!$D$7),Definitions!$C$8,Definitions!$D$8),Definitions!$C$9,Definitions!$D$9)
I am using the default en_us, and it works fine for all other formulas I've encountered.


Coordinator
Nov 18, 2010 at 9:05 PM

It should work, I'll take a look and see if there's anything odd happening internally



Just so you know, now that I've discovered getOldCalculatedValue(), I actually don't have an urgent need for this to be fixed.
(On the off chance that you're really busting your hump trying to solve this one :)


Coordinator
Dec 24, 2010 at 11:11 AM

Hopefully, this issue is resolved with the latest changes to Calculation.php. The code can be found in the
SVN Repository

