Error with formula calculation

Topics: User Forum
Oct 19, 2010 at 1:39 PM
Edited Oct 19, 2010 at 4:43 PM

Hello!

I am using PHP Excel 1.7.4. / PHP 5.3.3. and I have Excel 2007 for Windows and 2008 for Mac.

I write a xls file in this way:

 

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="testfile.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

I do some calculations with several sheets and then this:

$objPHPExcel->setActiveSheetIndex($sheetIndex)
    ->setCellValue('B'.$counter, $key)
    ->setCellValue('C'.$counter, '=AVERAGE(Test1:Test'.$sheetNumber.'!H'.$counter.')')
    ->setCellValue('D'.$counter, '=AVERAGE(Test1:Test'.$sheetNumber.'!I'.$counter.')')
    ->setCellValue('E'.$counter, '=AVERAGE(Test1:Test'.$sheetNumber.'!J'.$counter.')')
    ->setCellValue('F'.$counter, '=AVERAGE(Test1:Test'.$sheetNumber.'!K'.$counter.')')
    ->setCellValue('G'.$counter, '=AVERAGE(Test1:Test'.$sheetNumber.'!L'.$counter.')');

The AVERAGE will be changed in my case to (german) MITTELWERT.

Now I have the problem, that every cell has an error #BEZUG! (english term: #REF!) But if I click into a cell, the formula is right, and if I click in this field, so I can edit the formula, but do not edit it and press the return key, it calculates correctly the value with this formula.

So the formula is right, what's wrong here? Same behavior in Excel 2007 and Excel 2008 (Mac).

Thank you in advance for your help & Best regards.

Oct 21, 2010 at 5:19 PM

Hello. I still have the problem and I created a full little example which will show the problem / error:

I have three sheets Pager1, Pager2 and PagerAll. Pager1 and Pager 2 the values 10 and 20, PagerAll should calculate the average = 15. But this is not working...

<?php
require_once 'Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();

$objWorksheet = $objPHPExcel->createSheet(0);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 10);
$objPHPExcel->getActiveSheet()->setTitle("Pager1");

$objWorksheet = $objPHPExcel->createSheet(1);
$objPHPExcel->setActiveSheetIndex(1)->setCellValue('A1', 20);
$objPHPExcel->getActiveSheet()->setTitle("Pager2");

$objWorksheet = $objPHPExcel->createSheet(2);
$objPHPExcel->setActiveSheetIndex(2)->setCellValue('A1', '=AVERAGE(Pager1:Pager2!A1)');
$objPHPExcel->getActiveSheet()->setTitle("PagerAll");

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="testfile.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>
Perhaps someone can help me?

Coordinator
Oct 22, 2010 at 7:52 AM

While the PHPExcel calculation engine is fairly comprehensive now, it doesn't yet support 3D references such as Pager1:Pager2!A1.

When saving the file using the Excel2007 writer, PHPExcel attempts to execute the formula so that the result can be stored as part of the cell information written to the xlsx, but this formula with its 3D reference will generate an error in the calculation engine, so no result can be written. The default behaviour in this case is to store the error (as a #REF! error, because it is a Reference problem). The Excel2007 format also stores the formula as part of the cell information in the xlsx file, which is valid for Excel itself... hence forcing a recalculation within Excel itself, which results in the recalc correcting the result.