Formula doesn't work in version 1.7.2

Topics: Developer Forum
Jan 18, 2010 at 10:56 AM

Hi,

Last week we updated our phpexcel version with the new version that was available in the source code. Since then we have trouble with our old code for adding formulas.
The formulas are build up dynamically and are set after the formula is create.

$divisionResult =  '=IF('.$this->cell[$column-1].$row.' > 0; ('. $this->cell[$column-2].$row .'*1000) / ' . $this->cell[$column-1].$row . ';0)';
$sheet->setCellValue($this->cell[$column].$row, $divisionResult);

I also tried the following that didn't work:

$sheet->setCellValue($this->cell[$column].$row, '=IF(C16 > 0; C16/F16; 0');

If I write the following, the calculation works:

$sheet->setCellValue($this->cell[$column].$row, '=C16/F16';

Any ideas how this can be solved?

Thanks

 

 

Coordinator
Jan 18, 2010 at 11:24 AM

Hopefully you used commas

$sheet->setCellValue($this->cell[$column].$row, '=IF(C16 > 0, C16/F16, 0');

rather than semi-colons

$sheet->setCellValue($this->cell[$column].$row, '=IF(C16 > 0; C16/F16; 0');
You also seem to be missing a closing bracket in the formula
Jan 18, 2010 at 11:55 AM
Edited Jan 18, 2010 at 1:40 PM

Since which version is this changed? ( ',' instead of ';' )?

We used version 1.6.x and there we had no problems with the ';' .

 

 

Coordinator
Jan 18, 2010 at 1:26 PM

As far as I'm aware, using a ; argument separator has never worked correctly. I've backtracked to PHPExcel 1.6.6 using the formula =SUM(A1;A2;A3) and consistently received a #N/A error, where using a comma separator =SUM(A1,A2,A3) gives the correct result if I execute the getCalculatedValue() from within PHPExcel itself. Excel is still able to open any generated xls file using Excel 2003 where a ; is used as an argument separator, although if you look at the cell formula in Excel, it has been changed to use a comma (on my system) rather than a semi-colon.

Excel itself uses a comma internally, but displays the argument separator based on the locale settings: this character is determined by the List Separator setting for your system, which is specified in the Regional Settings dialog box, accessible via the Control Panel.

 

PHPExcel's internal formula parser was re-written for release 1.7.0, and currently enforces a comma separator between function arguments.

Formulae written to file are always in English, but Excel allows you to enter them according to a locale setting, or can render them in various languages when they are displayed. I'm assessing the work required in making formulae locale configurable (e.g. to allow use of the German ANZAHL() or Finnish LASKE() rather than the English COUNT()) and may well be able to modify the argument separator based on a locale setting in the same way.

Jan 18, 2010 at 1:42 PM

Okay, Thanks for the quick response.

I already changed all our formulas to a comma instead of a ;.

Now everything works fine.