#VALUE! error in PHP excel, but not when opening the file in excel.

Topics: Developer Forum
May 17, 2014 at 1:24 PM
Hi All,
I have been banging my head against a wall here for the last few days and thought I should just come and ask and see if any one could help me before I send my computer out the window.
Basically I am running a pretty complicated excel sheet and now it needs to move into an online system. There are two values that get collected from the sheet. The first works perfectly but the second one always comes out with a #value! error. If I save the sheet through php excel and then open it in excel, it calculates my value fine. If I save this file and then reload it php excel it still gives the same value error.
This is a dump from the "testformula" function I found in response to someone else who had similar errors:
14:07:19 Create new PHPExcel object
Formula Value is=(D8-VLOOKUP(D8,$H$15:$J$129,2))/(VLOOKUP(D8,$H$15:$J$129,3)-VLOOKUP(D8,$H$15:$J$129,2))*(M23-M22)+M22 Expected Value is 0 Parser Stack :- Array ( [0] => Array ( [type] => Cell Reference [value] => D8 [reference] => D8 ) [1] => Array ( [type] => Cell Reference [value] => D8 [reference] => D8 ) [2] => Array ( [type] => Cell Reference [value] => $H$15 [reference] => $H$15 ) [3] => Array ( [type] => Cell Reference [value] => $J$129 [reference] => $J$129 ) [4] => Array ( [type] => Binary Operator [value] => : [reference] => ) [5] => Array ( [type] => Value [value] => 2 [reference] => ) [6] => Array ( [type] => Operand Count for Function VLOOKUP() [value] => 3 [reference] => ) [7] => Array ( [type] => Function [value] => VLOOKUP( [reference] => ) [8] => Array ( [type] => Binary Operator [value] => - [reference] => ) [9] => Array ( [type] => Cell Reference [value] => D8 [reference] => D8 ) [10] => Array ( [type] => Cell Reference [value] => $H$15 [reference] => $H$15 ) [11] => Array ( [type] => Cell Reference [value] => $J$129 [reference] => $J$129 ) [12] => Array ( [type] => Binary Operator [value] => : [reference] => ) [13] => Array ( [type] => Value [value] => 3 [reference] => ) [14] => Array ( [type] => Operand Count for Function VLOOKUP() [value] => 3 [reference] => ) [15] => Array ( [type] => Function [value] => VLOOKUP( [reference] => ) [16] => Array ( [type] => Cell Reference [value] => D8 [reference] => D8 ) [17] => Array ( [type] => Cell Reference [value] => $H$15 [reference] => $H$15 ) [18] => Array ( [type] => Cell Reference [value] => $J$129 [reference] => $J$129 ) [19] => Array ( [type] => Binary Operator [value] => : [reference] => ) [20] => Array ( [type] => Value [value] => 2 [reference] => ) [21] => Array ( [type] => Operand Count for Function VLOOKUP() [value] => 3 [reference] => ) [22] => Array ( [type] => Function [value] => VLOOKUP( [reference] => ) [23] => Array ( [type] => Binary Operator [value] => - [reference] => ) [24] => Array ( [type] => Binary Operator [value] => / [reference] => ) [25] => Array ( [type] => Cell Reference [value] => M23 [reference] => M23 ) [26] => Array ( [type] => Cell Reference [value] => M22 [reference] => M22 ) [27] => Array ( [type] => Binary Operator [value] => - [reference] => ) [28] => Array ( [type] => Binary Operator [value] => * [reference] => ) [29] => Array ( [type] => Cell Reference [value] => M22 [reference] => M22 ) [30] => Array ( [type] => Binary Operator [value] => + [reference] => ) ) Calculated Value is #VALUE! Evaluation Log:
Fatal error: Cannot access private property PHPExcel_Calculation::$debugLog in XXX
May 19, 2014 at 8:21 AM
I have further narrowed down the problem to one of the lookups that the formula does. Specifically this one:
VLOOKUP(D13,Loadings!$I$12:$K$201,2,FALSE)

Basically that is looking up another value on a separate sheet. any ideas why that lookup might not work? Cell D13 is a string, the value that's being returned should be an integer.
As I say excel (and open office ) do this lookup fine, so I'm guessing this is a quirk of the phpexcel code that maybe others know about?