Array Formula (CSE Formula) Support

Topics: User Forum
Jan 17, 2012 at 6:40 AM
Edited Jan 17, 2012 at 7:11 AM

I am looking to write a single-cell array formula (Ctrl+Shift+Enter) for a range of cells.  I am using PHPExcel 1.7.6 and writing to an xml based Excel version (2007 or 2010 depending on the user) on a Windows platform.  I have been searching through all the forums and blogs, but I only found one vague reference mentioning that the user didn't think array formulas are supported in PHPExcel.  I would really like to avoid adding 31 extra columns to aggregate daily shipping data.  Thanks.

~Michael

Coordinator
Jan 17, 2012 at 1:09 PM

Array formulas are partially supported.

Try setting

PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_ARRAY);

before calling getCalculatedValue()

 

But writing array formulas is only supported for Excel2007

Jan 17, 2012 at 10:49 PM

Mark,

Sorry for such a simple questions, but how do I turn the following equation into an array formula

$objPHPExcel->getActiveSheet()->setCellValue('A1','=SUM(IF(MOD(ROW(A1:A10),2)=0,1,0))');

I am writing to a sheet using Excel2007.  I still want other cells in my worksheet to calculate normally.  Thanks.

Coordinator
Jan 17, 2012 at 11:48 PM
Edited Jan 17, 2012 at 11:49 PM

Try using

$objPHPExcel->getActiveSheet()->setCellValue('A1','{=SUM(IF(MOD(ROW(A1:A10),2)=0,1,0))}');

as it would appear in Excel itself.

No guarantees, because setting array formulas hasn't been tested yet.

 

Jan 18, 2012 at 12:01 AM
Edited Jan 18, 2012 at 12:02 AM

Would I need to call a "calculate" function?  When I write to the spreadsheet, I get it in pure text format.  I need to manually enter the cell, remove the braces, and CSE to get it to calculate.  A less ideal solution would be to set "=sumifs()", but when I try this, PHPExcel produces a corrupt file.  Thanks.