Cannot evaluate heavily nested formula

Topics: User Forum
Nov 18, 2010 at 9:33 AM

I have to process xls files that sometimes have heavily-nested 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 9: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?

Nov 18, 2010 at 10:30 AM

Oops! I copied that out of OpenOffice.org, which auto-converts 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 10:05 PM

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

Nov 22, 2010 at 12:18 AM

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 12:11 PM

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