phpEXCEL getCalculatedValue not returning desired results

Question

I have tried using the debug tool posted in reply to similar questions but was unable to find a solution to my problem. My excel sheet looks as follows: G36 = SUM(G24:G28). G24->G28 = SUM(B24:G24) and so on.

It returns formula with getValue() and #Value with getCalculatedValue()

Following is the output of the debug tool.

Formula Value is=SUM(G28:G34) Expected Value is UNKNOWN Parser Stack :- Array ( [0] => Array ( [type] => Cell Reference [value] => G28 [reference] => G28 ) [1] => Array ( [type] => Cell Reference [value] => G34 [reference] => G34 ) [2] => Array ( [type] => Binary Operator [value] => : [reference] => ) [3] => Array ( [type] => Operand Count for Function SUM() [value] => 1 [reference] => ) [4] => Array ( [type] => Function [value] => SUM( [reference] => ) ) Calculated Value is 0 Evaluation Log: Array ( [0] => Testing cache value for cell Worksheet!G36 [1] => Retrieving value for cell Worksheet!G36 from cache )

function testFormula($sheet,$cell) {
$formulaValue = $sheet->getCell($cell)->getValue();
echo 'Formula Value is' , $formulaValue , PHP_EOL;
$expectedValue = $sheet->getCell($cell)->getOldCalculatedValue();
echo 'Expected Value is '  , ((!is_null($expectedValue)) ? $expectedValue : 'UNKNOWN') , PHP_EOL;


$calculate = false;
try {
    $tokens = PHPExcel_Calculation::getInstance(
        $sheet->getParent()
    )->parseFormula(
        $formulaValue,
        $sheet->getCell($cell)
    );
    echo 'Parser Stack :-' , PHP_EOL;
    print_r($tokens);
    echo PHP_EOL;
    $calculate = true;
} catch (Exception $e) {
    echo 'PARSER ERROR: ' , $e->getMessage() , PHP_EOL;

    echo 'Parser Stack :-' , PHP_EOL;
    print_r($tokens);
    echo PHP_EOL;
}

if ($calculate) {
    PHPExcel_Calculation::getInstance(
        $sheet->getParent()
    )->getDebugLog()
    ->setWriteDebugLog(true);
    try {
        $cellValue = $sheet->getCell($cell)->getCalculatedValue();
        echo 'Calculated Value is ' , $cellValue , PHP_EOL;

        echo 'Evaluation Log:' , PHP_EOL;
        print_r(
            PHPExcel_Calculation::getInstance(
                $sheet->getParent()
            )->getDebugLog()
            ->getLog()
        );
        echo PHP_EOL;
    } catch (Exception $e) {
        echo 'CALCULATION ENGINE ERROR: ' , $e->getMessage() , PHP_EOL;

        echo 'Evaluation Log:' , PHP_EOL;
        print_r(
            PHPExcel_Calculation::getInstance(
                $sheet->getParent()
            )->debugLog
            ->getLog()
        );
        echo PHP_EOL;
    }
}
}


    $sheet = $objPHPExcel->getActiveSheet();
    testFormula($sheet,'G36');

Following is the output after I implemented the changes recommended by @MarkBaker in his answer below.

Formula Value is=SUM(G28:G34) Expected Value is UNKNOWN Parser Stack :- Array ( [0] => Array ( [type] => Cell Reference [value] => G28 [reference] => G28 ) [1] => Array ( [type] => Cell Reference [value] => G34 [reference] => G34 ) [2] => Array ( [type] => Binary Operator [value] => : [reference] => ) [3] => Array ( [type] => Operand Count for Function SUM() [value] => 1 [reference] => ) [4] => Array ( [type] => Function [value] => SUM( [reference] => ) ) Calculated Value is 0 Evaluation Log: Array ( [0] => Testing cache value for cell Worksheet!G36 [1] => Worksheet!G36 => Evaluating Cell G28 in current worksheet [2] => Worksheet!G36 => Testing cache value for cell Worksheet!G28 [3] => Worksheet!G36 -> Worksheet!G28 => Evaluating Cell D28 in current worksheet [4] => Worksheet!G36 -> Worksheet!G28 => Testing cache value for cell Worksheet!D28 [5] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluating Cell B28 in current worksheet [6] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluation Result for cell Worksheet!B28 is a floating point number with a value of 10000 [7] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluating Cell C28 in current worksheet [8] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluation Result for cell Worksheet!C28 is a floating point number with a value of 140 [9] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluating 10000 * 140 [10] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!D28 => Evaluation Result is a floating point number with a value of 1400000 [11] => Worksheet!G36 -> Worksheet!G28 => Evaluation Result for cell Worksheet!D28 is a floating point number with a value of 1400000 [12] => Worksheet!G36 -> Worksheet!G28 => Evaluating Cell F28 in current worksheet [13] => Worksheet!G36 -> Worksheet!G28 => Testing cache value for cell Worksheet!F28 [14] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluating Cell D28 in current worksheet [15] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Testing cache value for cell Worksheet!D28 [16] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Retrieving value for cell Worksheet!D28 from cache [17] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluation Result for cell Worksheet!D28 is a floating point number with a value of 1400000 [18] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluating Cell E28 in current worksheet [19] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluation Result for cell Worksheet!E28 is a string with a value of "3%" [20] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluating 1400000 * "3%" [21] => Worksheet!G36 -> Worksheet!G28 -> Worksheet!F28 => Evaluation Result is a a #VALUE! error [22] => Worksheet!G36 -> Worksheet!G28 => Evaluation Result for cell Worksheet!F28 is a #VALUE! error [23] => Worksheet!G36 -> Worksheet!G28 => Evaluating 1400000 + "#VALUE!" [24] => Worksheet!G36 -> Worksheet!G28 => Evaluation Result is a #VALUE! error [25] => Worksheet!G36 => Evaluation Result for cell Worksheet!G28 is a #VALUE! error [26] => Worksheet!G36 => Evaluating Cell G34 in current worksheet [27] => Worksheet!G36 => Evaluation Result for cell Worksheet!G34 is a NULL value [28] => Worksheet!G36 => Evaluating Range "Worksheet!G28" : "Worksheet!G34" [29] => Worksheet!G36 => Testing cache value for cell Worksheet!G28 [30] => Worksheet!G36 => Retrieving value for cell Worksheet!G28 from cache [31] => Worksheet!G36 => Testing cache value for cell Worksheet!G29 [32] => Worksheet!G36 -> Worksheet!G29 => Evaluating Cell D29 in current worksheet [33] => Worksheet!G36 -> Worksheet!G29 => Testing cache value for cell Worksheet!D29 [34] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluating Cell B29 in current worksheet [35] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluation Result for cell Worksheet!B29 is a floating point number with a value of 5000 [36] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluating Cell C29 in current worksheet [37] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluation Result for cell Worksheet!C29 is a floating point number with a value of 102 [38] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluating 5000 * 102 [39] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!D29 => Evaluation Result is a floating point number with a value of 510000 [40] => Worksheet!G36 -> Worksheet!G29 => Evaluation Result for cell Worksheet!D29 is a floating point number with a value of 510000 [41] => Worksheet!G36 -> Worksheet!G29 => Evaluating Cell F29 in current worksheet [42] => Worksheet!G36 -> Worksheet!G29 => Testing cache value for cell Worksheet!F29 [43] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluating Cell D29 in current worksheet [44] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Testing cache value for cell Worksheet!D29 [45] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Retrieving value for cell Worksheet!D29 from cache [46] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluation Result for cell Worksheet!D29 is a floating point number with a value of 510000 [47] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluating Cell E29 in current worksheet [48] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluation Result for cell Worksheet!E29 is a string with a value of "3%" [49] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluating 510000 * "3%" [50] => Worksheet!G36 -> Worksheet!G29 -> Worksheet!F29 => Evaluation Result is a a #VALUE! error [51] => Worksheet!G36 -> Worksheet!G29 => Evaluation Result for cell Worksheet!F29 is a #VALUE! error [52] => Worksheet!G36 -> Worksheet!G29 => Evaluating 510000 + "#VALUE!" [53] => Worksheet!G36 -> Worksheet!G29 => Evaluation Result is a #VALUE! error [54] => Worksheet!G36 => Evaluating Function SUM() with 1 argument [55] => Worksheet!G36 => Evaluating SUM( { "#VALUE!"; "#VALUE!"; ; ; ; ; } ) [56] => Worksheet!G36 => Evaluation Result for SUM() function call is an integer number with a value of 0 ) 14:16:49 Write to Excel2007 format

Answer 1

The evaluation entry Retrieving value for cell Worksheet!G36 from cache tells you that the calculation engine is retrieving the data from a previous request to getCalculatedValue() that has been cached.

Perhaps you should try flushing the calculation cache and then rerunning the calculation through the logger.

You can flush the calculation cache using

PHPExcel_Calculation::getInstance(
    $objPHPExcel
)->flushInstance();

then you will be able to recalculate the formula in its entirety, and see how the calculation is being performed

EDIT

To set a cell containing a percentage value using PHPExcel do:

$objPHPExcel->getActiveSheet()
    ->getCell("E$a")
    ->setValue($taxrate / 100);
$objPHPExcel->getActiveSheet()
    ->getCell("E$a")
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);

For a $taxrate value of 3, this will store 0.03 in the cell, but display it as 3%. 0.03 is the correct value to use in the formula expression.