Formula is calculated wrong

Topics: Developer Forum, User Forum
Mar 23, 2011 at 8:27 PM
Edited Mar 23, 2011 at 8:34 PM

Hi,

The Excel sheet has a formula in a cell with the value D26 + D27 + D24*D7*SUM(D28,D30,D32) + D25*D7*SUM(D29,D31,D33) and its calculated value in the spreadsheet is 2001. But PHPEXcel getCalculatedValue returns 612.

I used the testFormula function to parse the formula and the results are given below.

D23 Value is =D26 + D27 + D24*D7*SUM(D28,D30,D32) + D25*D7*SUM(D29,D31,D33)
D23 Expected Value is 2001.0094285714285
Parser Stack :-
Array
(
    [0] => Array
        (
            [type] => Cell Reference
            [value] => D26
            [reference] => D26
        )

    [1] => Array
        (
            [type] => Cell Reference
            [value] => D27
            [reference] => D27
        )

    [2] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] =>
        )

    [3] => Array
        (
            [type] => Cell Reference
            [value] => D24
            [reference] => D24
        )

    [4] => Array
        (
            [type] => Cell Reference
            [value] => D7
            [reference] => D7
        )

    [5] => Array
        (
            [type] => Binary Operator
            [value] => *
            [reference] =>
        )

    [6] => Array
        (
            [type] => Cell Reference
            [value] => D28
            [reference] => D28
        )

    [7] => Array
        (
            [type] => Cell Reference
            [value] => D30
            [reference] => D30
        )

    [8] => Array
        (
            [type] => Cell Reference
            [value] => D32
            [reference] => D32
        )

    [9] => Array
        (
            [type] => Operand Count for Function SUM()
            [value] => 3
            [reference] =>
        )

    [10] => Array
        (
            [type] => Function
            [value] => SUM(
            [reference] =>
        )

    [11] => Array
        (
            [type] => Binary Operator
            [value] => *
            [reference] =>
        )

    [12] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] =>
        )

    [13] => Array
        (
            [type] => Cell Reference
            [value] => D25
            [reference] => D25
        )

    [14] => Array
        (
            [type] => Cell Reference
            [value] => D7
            [reference] => D7
        )

    [15] => Array
        (
            [type] => Binary Operator
            [value] => *
            [reference] =>
        )

    [16] => Array
        (
            [type] => Cell Reference
            [value] => D29
            [reference] => D29
        )

    [17] => Array
        (
            [type] => Cell Reference
            [value] => D31
            [reference] => D31
        )

    [18] => Array
        (
            [type] => Cell Reference
            [value] => D33
            [reference] => D33
        )

    [19] => Array
        (
            [type] => Operand Count for Function SUM()
            [value] => 3
            [reference] =>
        )

    [20] => Array
        (
            [type] => Function
            [value] => SUM(
            [reference] =>
        )

    [21] => Array
        (
            [type] => Binary Operator
            [value] => *
            [reference] =>
        )

    [22] => Array
        (
            [type] => Binary Operator
            [value] => +
            [reference] =>
        )

)
D23 Calculated Value is 612.713

 

In the Evaluation Log I see the SUM is evaluated to 0.

[118] => Input - Sheet Model!D23 -> Input - Sheet Model!D32 -> Evaluation Result for HLOOKUP() function call is a #Not Yet Implemented error
[119] => Input - Sheet Model!D23 -> Evaluation Result for cell Input - Sheet Model!D32 is a string with a value of 1.3117142857142856

[120] => Input - Sheet Model!D23 -> Evaluating Function SUM() with 3 arguments
[121] => Input - Sheet Model!D23 -> Evaluating SUM( 0, 13.042857142857143, 1.3117142857142856 )
[122] => Input - Sheet Model!D23 -> Evaluation Result for SUM() function call is an integer number with a value of 0
[123] => Input - Sheet Model!D23 -> Evaluating 37.5 * 0
[124] => Input - Sheet Model!D23 -> Evaluation Result is a floating point number with a value of 0

[205] => Input - Sheet Model!D23 -> Input - Sheet Model!D33 -> Evaluation Result for HLOOKUP() function call is a #Not Yet Implemented error
    [206] => Input - Sheet Model!D23 -> Evaluation Result for cell Input - Sheet Model!D33 is a string with a value of 0.8
    [207] => Input - Sheet Model!D23 -> Evaluating Function SUM() with 3 arguments
    [208] => Input - Sheet Model!D23 -> Evaluating SUM( 0, 3.2, 0.8 )
    [209] => Input - Sheet Model!D23 -> Evaluation Result for SUM() function call is an integer number with a value of 0
    [210] => Input - Sheet Model!D23 -> Evaluating 212.5 * 0
    [211] => Input - Sheet Model!D23 -> Evaluation Result is a floating point number with a value of 0
    [212] => Input - Sheet Model!D23 -> Evaluating 612.713 + 0
    [213] => Input - Sheet Model!D23 -> Evaluation Result is a floating point number with a value of 612.713

 

Eventhough we see the error '#Not Yet Implemented" for the HLOOKUP function, PHPExcel fetches the correct lookup value, but the SUM is calculated wrong.

When I replace the formula with =D26 + D27 + D24*D7*(D28+D30+D32) + D25*D7*(D29+D31+D33), it works perfectly fine. How can this be fixed? I can not modify the formula as instructed by the client.

How can I fix this issue?

 

Thanks

DDEi