use "=offset(A5,0,3)",have a error,why,how to do?

Topics: Developer Forum, Project Management Forum, User Forum
Nov 1, 2009 at 11:09 AM

phpexcel 1.7.1


D:\MYOA\webroot\module\phpexcel\Classes\PHPExcel\Calculation\Functions.php on line 10119

Warning: Invalid argument supplied for foreach() in D:\MYOA\webroot\module\phpexcel\Classes\PHPExcel\Calculation\Functions.php on line 10126

Notice: Undefined variable: endColumnKey in D:\MYOA\webroot\module\phpexcel\Classes\PHPExcel\Calculation\Functions.php on line 10130

Nov 2, 2009 at 12:30 AM

Latest code (available tomorrow am) should work correctly with the OFFSET() function

Nov 2, 2009 at 1:23 PM


use "=offset(A5,0,3)", OK!

but "=SUM(C2:OFFSET(A2,0,3))" error!


Fatal error: Uncaught exception 'Exception' with message 'Cell coordinate can not be zero-length string.' in D:\MYOA\webroot\module\phpexcel\Classes\PHPExcel\Cell.php:517 Stack trace: #0 D:\MYOA\webroot\module\phpexcel\Classes\PHPExcel\Calculation.php(2580): PHPExcel_Cell::coordinateFromString('') #1 D:\MYOA\webroot\module\phpexcel\Classes\PHPExcel\Calculation.php(1935): PHPExcel_Calculation->_processTokenStack(Array, 'A12', Object(PHPExcel_Cell)) #2 D:\MYOA\webroot\module\phpexcel\Classes\PHPExcel\Calculation.php(1802): PHPExcel_Calculation->_calculateFormulaValue('=SUM(C5:OFFSET(...', 'A12', Object(PHPExcel_Cell)) #3 D:\MYOA\webroot\module\phpexcel\Classes\PHPExcel\Cell.php(301): PHPExcel_Calculation->calculateCellValue(Object(PHPExcel_Cell), true) #4 D:\MYOA\webroot\module\phpexcel\Tests\ PHPExcel_Cell->getCalculatedValue() #5 {main} thrown in D:\MYOA\webroot\module\phpexcel\Classes\PHPExcel\Cell.php on line 517

can your help me!

Nov 2, 2009 at 2:14 PM

That formula is never going to work in PHPExcel without a complete rewrite of the calculation engine.

All PHPExcel functions return values (or arrays of values), so OFFSET(A2,0,3) will return the contents of cell A5, which is normally the behaviour that is required when OFFSET() is used.

SUM(C2:OFFSET(A2,0,3)) requires the OFFSET() function to return the cell reference for A5, not the value contained there, effectively giving you the formula SUM(C2:A5), so it all comes down to a question of context.


Used in most contexts, OFFSET() should return values; but in a few contexts, it should return a reference instead. The parser in PHPExcel is a basic LR (Left to Right) parser rather than a more sophisticated LALR (Look-Ahead Left to Right) parser: the latter would be able to identify the context, and whether any given function should return a value of a reference; but this would require a lot more code to implement within the parser itself, and would come with a performance overhead (not to mention a complete rewrite of the function library as well).


Nov 3, 2009 at 3:24 AM
Edited Nov 3, 2009 at 3:25 AM


but : OFFSET(A2,0,3))=D2 or A5??

if "D2" then  SUM(C2:OFFSET(A2,0,3))->SUM(C2:D2)  it's ok. 

if "A5" then SUM(C2:OFFSET(A2,0,3))->SUM(C2:A5) it's error.


in excel2003    '=SUM(C2:OFFSET(A2,0,3))' is OK


Nov 3, 2009 at 8:57 AM

Apologies, yes.

Syntax for OFFSET is rows then columns rather than columns then rows, so OFFSET(A2,0,3))=D2

But the fact still remains that (without significant rewrite of the calculation engine) PHPExcel returns the value of the cell, not the address/reference of the cell; but SUM(C2:OFFSET(A2,0,3)) requires the reference to give C2:D2



Nov 3, 2009 at 9:19 AM


that SUM(C2:OFFSET(A2,0,3)) don't  work work  correctly in  PHPExce?

 have other way?

Nov 3, 2009 at 9:37 AM

I'm looking at alternative possibilities

Setting a cell (e.g. B1) value to


and then using


may work

But I've not tested it yet

Nov 3, 2009 at 2:20 PM
Edited Nov 3, 2009 at 2:41 PM




$ActiveSheet->getCell('P5')->getCalculatedValue() = #N/A

it's error


Nov 3, 2009 at 3:42 PM
Edited Nov 3, 2009 at 3:44 PM
tn987 wrote:


it's error


I have no idea why, I wasn't even sure whether it would retun anything or simply go into a permanent loop, although you haven't imlemented it as I expected.


Without setting up a test spreadsheet and stepping through the code a line at a time, I'm unlikely to be able to provide you with an answer.


While the PHPExcel calculation engine and function library attempts to replicate Excel's behaviour in most regards, we make no guarantess that it will work identically to Excel in every regard; and definitely not when using the more esoteric functions such as OFFSET and INDIRECT. I'll have a look and see what I can do, but to provide any solution I'm going to have to convert the calculation engine to a Look-Ahead parser, which is a complicated task, will take some time, and will adversely affect performance and memory usage.

Nov 30, 2010 at 8:01 PM

1 Year Later.

Is there any OFFSET() functionality? Or an alternative?

Nov 30, 2010 at 11:19 PM
noeleri wrote:

1 Year Later.

Is there any OFFSET() functionality? Or an alternative?

 Fixes were implemented to the OFFSET() in the 1.7.3 release. Do these solve your problem?