Bad results

Topics: User Forum
Oct 8, 2008 at 8:34 AM

Hello,

I created an Excel file with 2 sheets the first with calculations the second recovering the results of the first.

It is ok, but I encounter a problem:


1st named sheet “Calcul”:

$objPHPExcel->getActiveSheet()->setCellValue('C11', '=IF(C10<>0;C9/C10;"")' );
Or
$objPHPExcel->getActiveSheet()->setCellValue(‘C1’,’=IF(A1<A2;"<";IF(A1>A2;">";"")');

With excel5 : ok
With excel2007: 0 (alphabetical)

2nd sheet:

$objPHPExcel->getActiveSheet()->setCellValue(‘B2’, '=Calcul!C11’);

With excel5 : #N/A 
With excel2007: ok 

Can anyone help me with this issue?

Thanks,


Developer
Oct 8, 2008 at 4:54 PM
1.
As for the problem with Excel2007, in your formula the arguments always need to be separated by ',' (comma) and not ';' semicolon. Like in English version of Excel:

$objPHPExcel->getActiveSheet()->setCellValue('C11', '=IF(C10<>0,C9/C10,"")' );

Related post:
http://www.codeplex.com/PHPExcel/Thread/View.aspx?ThreadId=32906

2.
PHPExcel_Writer_Excel5 does not seem to support external sheet references in formulas. Problem must be that the PEAR Spreadsheet_Excel_Writer (which PHPExcel_Writer_Excel5 is based on) does not support external sheet references when creating BIFF8 (Excel 97 - Excel 2003) files, but only when creating BIFF5 files (Excel 5.0 - Excel 95).

Because external references were working with PHPExcel_Writer_Excel5 using PHPExcel 1.5.0, but stopped working in PHPExcel 1.5.5 when Excel type creation was swithced from BIFF5 to BIFF8. I am not sure what the problem is. Must investigate and return to this issue later unless someone else knows what the problem is?

Related posts:
http://www.codeplex.com/PHPExcel/Thread/View.aspx?ThreadId=16136
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=3893