unable to call formula with excel5

Topics: User Forum
Sep 24, 2009 at 8:13 AM


I am using PHPExcel with excel5.

With version 1.67 formula worked ok, but with large files I had problems of memory.

When I am using version 1.7 the problem with memory was solved  but I get warning messages: Unable to call PHPExcel_Calculation_Functions::MATCH/INDEX ....

What is the problem?


Sep 24, 2009 at 8:33 AM

The Calculation engine has been completely rewritten for 1.7.0, but it has been pretty comprehensively tested, and I'm not aware of any significant problems with it.

Can you provide an example spreadsheet that demonstrates this problem, or (at least) a formula that gives this error so that we can try and replicate it

Sep 24, 2009 at 8:51 AM

Example of formula:




The same excel file run okay with 1.67 and get warnings with 1.7


Sep 24, 2009 at 12:26 PM
Edited Sep 24, 2009 at 12:34 PM

I can't recreate this error at all with either MATCH or INDEX.

Both functions are returning exactly the same values as Excel itself when I use your example formulae, with no PHP errors even when running with E_ALL

A1 Value is =MATCH(A3,E5:N5,1)
Parser Stack :-

    [0] => A3
    [1] => E5:N5
    [2] => 1
    [3] => 3
    [4] => MATCH(

A1 Calculated Value is 7

Evaluation Log:

    [0] => A1 -> Evaluating Cell A3 in current worksheet
    [1] => A1 -> Evaluation Result for cell A3 is an integer number with a value of 15
    [2] => A1 -> Evaluating Cell Range E5:N5 in current worksheet
    [3] => A1 -> Evaluation Result for cells E5:N5 is a matrix with a value of { 2; 4; 6; 8; 10; 12; 14; 16; 18; 20 }
    [4] => A1 -> Evaluating Function MATCH() with 3 arguments
    [5] => A1 -> Evaluating MATCH( 15, { 2; 4; 6; 8; 10; 12; 14; 16; 18; 20 }, 1 )
    [6] => A1 -> Evaluation Result is an integer number with a value of 7


As an experiment, could you try modifying line 2678 of Calculation.php This should read:

$result = call_user_func_array($functionCall,$args); 

Change it to

$result = call_user_func_array(explode('::',$functionCall),$args);

This may break other functions, but it may also help identify a cause for this problem.

Oct 1, 2009 at 7:45 AM


After changing that line I get no error.

The match work ok but I have problem with the function index.

The formulas I have are:

A6 => =MATCH(A3,E5:N5,1)   calculated  3

B6 => =MATCH(B3,D6:D22,1) calculated  4

A10 => =INDEX(E6:N22,A6,B6)

The index should return value of (3,4) but it returns value of (4,3) -> instead of (row, colum) it returns (column,row)

How can I see the evaluation log?



Oct 1, 2009 at 9:38 AM

I don't know why the original version of call_user_func_array() wasn't working for you (perhaps a version or platform discrepancy in PHP), but at least that experiment resolves your initial issue. Unfortunately, the experiment I gave you breaks functions that directly call existing PHP functions such as TAN() rather than those explicitly coded in PHPExcel. A full fix to work with all functions should be:

if (strpos('::',$functionCall) !== false) {
   $result = call_user_func_array(explode('::',$functionCall),$args);
} else {
   $result = call_user_func_array($functionCall,$args);

I'll make this change in the Subversion code

As regards the incorrect results being returned by INDEX and MATCH, I'll take a look at these now. They may be linked to work item 10629


Oct 1, 2009 at 9:42 AM
Edited Oct 1, 2009 at 9:47 AM

The code that I use to debug test formulae, showing the evaluation log is:

$sheet = $objPHPExcel->getActiveSheet();
PHPExcel_Calculation::getInstance()->writeDebugLog = true;
$cell = 'A1';
$cellValue = $sheet->getCell($cell)->getValue();
echo '<b>'.$cell.' Value is </b>'.$cellValue."<br />\n";

echo '<b>Parser Stack :-</b><pre>';
$tokens = PHPExcel_Calculation::getInstance()->parseFormula($cellValue);
echo '</pre>';
$cellValue = $sheet->getCell($cell)->getCalculatedValue();
echo '<b>'.$cell.' Calculated Value is </b>'.$cellValue."<br />\n";
echo '<h3>Evaluation Log:</h3><pre>';
echo '</pre>';

Setting PHPExcel_Calculation::getInstance()->writeDebugLog to true generates the log, which can then be retrieved from PHPExcel_Calculation::getInstance()->debugLog


Debug logging of a calculation is disabled by default because it adds a small memory and speed overhead to calculations. If enabled, the log is flushed before each call to getCalculatedValue(), so it can only return details of one call at a time; although if a calculation is dependent on other calculated cells, it will log the entire chain.

Oct 1, 2009 at 11:08 AM


It should be strpos($functionCall,'::')

Oct 1, 2009 at 11:28 AM
zerashn wrote:


It should be strpos($functionCall,'::')