SUMPRODUCT: Cyclic Reference in Formula

Topics: Developer Forum
Jul 20, 2010 at 10:57 AM

Hi

I wrote a script which is generating Excel 2007 file that contains SUMPRODUCT calcuations in second worksheet refering to first worksheet and since two days I am trying to find out how to make it working.

Unfortunately script finishes with one of two errors:

Uncaught exception 'Exception' with message 'Cyclic Reference in Formula' in C:\php\Classes\PHPExcel\Calculation.php:2088 Stack trace:
#0 C:\php\Classes\PHPExcel\Cell.php(285): PHPExcel_Calculation->calculateCellValue(Object(PHPExcel_Cell), true)
#1 C:\php\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(969): PHPExcel_Cell->getCalculatedValue()
#2 C:\php\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(910): PHPExcel_Writer_Excel2007_Worksheet->_writeCell(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), 'C2', Array, Array)
#3 C:\php\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(82): PHPExcel_Writer_Excel2007_Worksheet->_writeSheetData(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet), Array)
#4 C:\php\Classes\PHPExcel\Writer\Excel2007.php(258): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array)
#5 E:\Scripts\sumprod_crf.php(120): PHPExcel_Writer_Excel2007->save('E:\Scripts\...')
#6 {main}
thrown in C:\php\Classes\PHPExcel\Calculation.php on line 2088

When I changed addressing from range C to D (for values to add) in first worksheet in script then the script returned Invalid cell coordinate (the rest of error message was the same like above).

I have no clue why it is not calculating, the formula generated by scripts works perfectly when I copy it to Excel manualy.

I would be gratefful if you could have a look on this problem.

PHP ver 5.2.8 @ Apache @ Windows2003, PHPExcel ver 1.7.3 c (also tested with SVN 56728).

 

 

<?php

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', 1);

/** Include path **/
ini_set('include_path', ini_get('include_path').';C:\php\PHPExcel\Classes');

/** PHPExcel */
include 'PHPExcel.php';


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

$wks0 = 'Invoices';
$wks1= 'Sums';

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle($wks0);

// Add some data
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Names');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Alpha');
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Beta');
$objPHPExcel->getActiveSheet()->setCellValue('A4', 'Gamma');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Delta');
$objPHPExcel->getActiveSheet()->setCellValue('A6', 'Alpha');
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'Alpha');
$objPHPExcel->getActiveSheet()->setCellValue('A8', 'Gamma');
$objPHPExcel->getActiveSheet()->setCellValue('A9', 'Delta');

$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Values to check');
$objPHPExcel->getActiveSheet()->setCellValue('B2', -1);
$objPHPExcel->getActiveSheet()->setCellValue('B3', 1);
$objPHPExcel->getActiveSheet()->setCellValue('B4', 7);
$objPHPExcel->getActiveSheet()->setCellValue('B5', -2);
$objPHPExcel->getActiveSheet()->setCellValue('B6', -3);
$objPHPExcel->getActiveSheet()->setCellValue('B7', 2);
$objPHPExcel->getActiveSheet()->setCellValue('B8', 19);
$objPHPExcel->getActiveSheet()->setCellValue('B9', 2);

$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Values to add');
$objPHPExcel->getActiveSheet()->setCellValue('C2', 200);
$objPHPExcel->getActiveSheet()->setCellValue('C3', 150);
$objPHPExcel->getActiveSheet()->setCellValue('C4', 100);
$objPHPExcel->getActiveSheet()->setCellValue('C5', 250);
$objPHPExcel->getActiveSheet()->setCellValue('C6', 100);
$objPHPExcel->getActiveSheet()->setCellValue('C7', 200);
$objPHPExcel->getActiveSheet()->setCellValue('C8', 150);
$objPHPExcel->getActiveSheet()->setCellValue('C9', 50);

// Create a new worksheet, after the default sheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle($wks1);

// Add some data
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Names');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Alpha');
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Alpha');
$objPHPExcel->getActiveSheet()->setCellValue('A4', 'Beta');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Beta');
$objPHPExcel->getActiveSheet()->setCellValue('A6', 'Gamma');
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'Gamma');
$objPHPExcel->getActiveSheet()->setCellValue('A8', 'Delta');
$objPHPExcel->getActiveSheet()->setCellValue('A9', 'Delta');

$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Groups');
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'Positive');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Negative');
$objPHPExcel->getActiveSheet()->setCellValue('B4', 'Positive');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'Negative');
$objPHPExcel->getActiveSheet()->setCellValue('B6', 'Positive');
$objPHPExcel->getActiveSheet()->setCellValue('B7', 'Negative');
$objPHPExcel->getActiveSheet()->setCellValue('B8', 'Positive');
$objPHPExcel->getActiveSheet()->setCellValue('B9', 'Negative');

$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Result');

$start_row = 1;    //for $wks0
$rown = 10;        //for $wks0
$start_row_2 = 1;  //for $wks1
$rown_2 = 10;      //for $wks1

// Add SUMPRODUCT calculations
for ($i=$start_row_2 + 1; $i<$rown_2; $i++) {

///*
  if ($i % 2) {
      $cFormula = "=SUMPRODUCT(('" . $wks0 . "'" . '!$A$' . ($start_row+1) . ':$A$' . ($rown-1) . "='" . $wks1 . "'" . '!$A' . $i . ")*('" . $wks0 . "'" . '!$B$' . ($start_row+1) . ':$B$' . ($rown-1) . "<0)*('" . $wks0 . "'" . '!C$' . ($start_row+1) . ':C$' . ($rown-1) . '))';    
    } else {
      $cFormula = "=SUMPRODUCT(('" . $wks0 . "'" . '!$A$' . ($start_row+1) . ':$A$' . ($rown-1) . "='" . $wks1 . "'" . '!$A' . $i . ")*('" . $wks0 . "'" . '!$B$' . ($start_row+1) . ':$B$' . ($rown-1) . ">=0)*('" . $wks0 . "'" . '!C$' . ($start_row+1) . ':C$' . ($rown-1) . '))';
  }
//*/

/* This is parsed but last substract which is not conditional in formula is refering to null cells.
  if ($i % 2) {
      $cFormula = "=SUMPRODUCT(('" . $wks0 . "'" . '!$A$' . ($start_row+1) . ':$A$' . ($rown-1) . "='" . $wks1 . "'" . '!$A' . $i . ")*('" . $wks0 . "'" . '!$B$' . ($start_row+1) . ':$B$' . ($rown-1) . "<0)*('" . $wks0 . "'" . '!D$' . ($start_row+1) . ':D$' . ($rown-1) . '))';
    } else {
      $cFormula = "=SUMPRODUCT(('" . $wks0 . "'" . '!$A$' . ($start_row+1) . ':$A$' . ($rown-1) . "='" . $wks1 . "'" . '!$A' . $i . ")*('" . $wks0 . "'" . '!$B$' . ($start_row+1) . ':$B$' . ($rown-1) . ">=0)*('" . $wks0 . "'" . '!D$' . ($start_row+1) . ':D$' . ($rown-1) . '))';
  }
*/

/* This is not parsed - here last substract is conditionalized and returns always TRUE so it multiplies previous substracts by 1.
  if ($i % 2) {
$cFormula = "=SUMPRODUCT(('" . $wks0 . "'" . '!$A$' . ($start_row+1) . ':$A$' . ($rown-1) . "='" . $wks1 . "'" . '!$A' . $i . ")*('" . $wks0 . "'" . '!$B$' . ($start_row+1) . ':$B$' . ($rown-1) . "<0)*('" . $wks0 . "'" . '!C$' . ($start_row+1) . ':C$' . ($rown-1) . "='" . $wks0 . "'" . '!C$' . ($start_row+1) . ':C$' . ($rown-1) . '))';
    } else {
$cFormula = "=SUMPRODUCT(('" . $wks0 . "'" . '!$A$' . ($start_row+1) . ':$A$' . ($rown-1) . "='" . $wks1 . "'" . '!$A' . $i . ")*('" . $wks0 . "'" . '!$B$' . ($start_row+1) . ':$B$' . ($rown-1) . ">=0)*('" . $wks0 . "'" . '!C$' . ($start_row+1) . ':C$' . ($rown-1) . "='" . $wks0 . "'" . '!C$' . ($start_row+1) . ':C$' . ($rown-1) . '))';
  }
*/

  echo $cFormula. "<BR />";
  $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $cFormula);
}

// Save Excel 2007 file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
echo date('H:i:s') . " Writing to Excel2007 format done" . "<BR />";


// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB" . "<BR />";


 

 

Coordinator
Jul 20, 2010 at 8:55 PM

Like Excel itself, PHPExcel provides two methods for handling cyclic references in formulae.

The default is to throw an exception whenever a cyclic reference is found.

The alternative is to set a limit for the number of iterations before the calculation engine will return a calculated value.
You can set this limit using:

PHPExcel_Calculation::getInstance()->cyclicFormulaCount = 10;

Setting this value to 0 will revert to the default of throwing an exception

Jul 21, 2010 at 6:41 AM

Thank you MarkBaker for this suggestion but it doesn't work.

The point is that there is no cycle reference in my formula.

PHP converts it to following string:

=SUMPRODUCT(('Invoices'!$A$2:$A$9='Sums'!$A2)*('Invoices'!$B$2:$B$9>=0)*('Invoices'!C$2:C$9))

 This formula is not raising cycle reference warning when I paste it into Excel. It works exactly as I need.

 

The PHP code attached in my first post is complete Excel 2007 test sample, so you can just copy-paste-run it to see how PHP is building formulas and errors thrown from it.

Commented sets of formulas in for... loop are my attempts to diagnose other mentioned error: Invalid cell coordinate. You can also easily trigger it by replacing in // Add some data following section:

$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Values to add');
$objPHPExcel->getActiveSheet()->setCellValue('C2', 200);
$objPHPExcel->getActiveSheet()->setCellValue('C3', 150);
$objPHPExcel->getActiveSheet()->setCellValue('C4', 100);
$objPHPExcel->getActiveSheet()->setCellValue('C5', 250);
$objPHPExcel->getActiveSheet()->setCellValue('C6', 100);
$objPHPExcel->getActiveSheet()->setCellValue('C7', 200);
$objPHPExcel->getActiveSheet()->setCellValue('C8', 150);
$objPHPExcel->getActiveSheet()->setCellValue('C9', 50);

into:
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'Values to add');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 200);
$objPHPExcel->getActiveSheet()->setCellValue('D3', 150);
$objPHPExcel->getActiveSheet()->setCellValue('D4', 100);
$objPHPExcel->getActiveSheet()->setCellValue('D5', 250);
$objPHPExcel->getActiveSheet()->setCellValue('D6', 100);
$objPHPExcel->getActiveSheet()->setCellValue('D7', 200);
$objPHPExcel->getActiveSheet()->setCellValue('D8', 150);
$objPHPExcel->getActiveSheet()->setCellValue('D9', 50);

and uncommenting this:

  if ($i % 2) {
      $cFormula = "=SUMPRODUCT(('" . $wks0 . "'" . '!$A$' . ($start_row+1) . ':$A$' . ($rown-1) . "='" . $wks1 . "'" . '!$A' . $i . ")*('" . $wks0 . "'" . '!$B$' . ($start_row+1) . ':$B$' . ($rown-1) . "<0)*('" . $wks0 . "'" . '!D$' . ($start_row+1) . ':D$' . ($rown-1) . '))';
    } else {
      $cFormula = "=SUMPRODUCT(('" . $wks0 . "'" . '!$A$' . ($start_row+1) . ':$A$' . ($rown-1) . "='" . $wks1 . "'" . '!$A' . $i . ")*('" . $wks0 . "'" . '!$B$' . ($start_row+1) . ':$B$' . ($rown-1) . ">=0)*('" . $wks0 . "'" . '!D$' . ($start_row+1) . ':D$' . ($rown-1) . '))';
  }
The difference is now that data are populated in first worksheet in A, B and D column. C column is empty. Selection of last substract is from column D in such situation.
So logically the same situation but error is diffrent.
Coordinator
Jul 25, 2010 at 12:09 PM

Will take a closer look. It may be that the cyclic reference trap is misreading the worksheet reference

Jul 30, 2010 at 8:16 AM

Hi

error_reporting(E_ALL); 
or even
error_reporting(2048); 

corrupts xls file on my box.

I assume that PHPExcel is relying on php exceptions to create a valid file.
Coordinator
Jul 30, 2010 at 12:12 PM
JohnGriffiths2 wrote:

I assume that PHPExcel is relying on php exceptions to create a valid file.

 Yes, PHPExcel throws exceptions when it encounters a problem, and your code needs to catch these and handle them in an appropriate manner. e.g.

try {
   $objPHPExcel = PHPExcel_IOFactory::load($fileName);
} catch (Exception $e) {
   die('Error reading '.$fileName.': '.$e->getMessage());
}
Jul 30, 2010 at 2:50 PM

You're too right, the error handling was added to find some information regarding an error.

Unfortunatly the act of adding error handling changes exception handling so that the output file was corrupt therefore no information about the original error.

Adding exception handling changes nothing because with it I get a valid file but still no information about the original error.

Heisenbugs

 

Coordinator
Jul 30, 2010 at 3:53 PM

@Slapi

I believe this is the same problem as leosilvarj's problem with SUMIF http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=221732

Both reference a range in another worksheet; and searching through the code logic, it's treating the start cell of the range correctly as being in Invoices, but the end cell of the range as being in the current worksheet (Sums) when it should also be in Invoices.... and this is causing conflicts in the calculation engine.

I've made a code change to the calculation engine to ensure that it picks up the correct worksheet for the end of range, and it should be available in the daily downloads tomorrow (check date to be sure you have the latest version after today). Can you please try running your script against this patched code to see if it resolves your problem.