CODEWITHSUNDEEP
phpexcel
Stuart Allsopp
Stuart Allsopp

Reputation: 35

phpExcel 1.8 Named Ranges in Formula

I am using PhpExcel 1.8.0 on Php 5.5

I am trying to get phpexcel to a whole bunch of heavy lifting when it comes to calculations. I am generating a sheet that contain named ranges that span a single column but multiple rows (RANGE_1 = A2:A6, RANGE_2 = B2:B6). I then have another set of cells (C2:C6) where the formula is RANGE_1+RANGE_2 in each cell. When you load the generated file in Excel, it correctly calculates the values in column C depending on what is in each row. THIS IS GREAT.

But, when I getCalculatedValue of (C2) it return 0 and not the expected result of (A2+B2). Is there anything I am obviously and clearly doing wrong, or do I need to convert my named ranges in the formula to actual cell references (that would be really tedious as it works really well).

I appreciate any help on this.

Upvotes: 1

Views: 664

Answers (1)

Mark Baker
Mark Baker

Reputation: 212412

In MS Excel itself, your formula in C2:C6 would be a range formula, typically depicted as 

{=(RANGE_1+RANGE_2)}

(with the braces) in the formula bar; and entered using shift-ctrl-enter rather than simply enter

Named ranges can be used in general formulae, e.g.

=SUM(RANGE_1)

But Range formulas are not currently supported by PHPExcel, whether using named ranges or direct entry of ranges

The only way to achieve what you are trying to do would be to have a different formula in each of cells C2 to C6, something like:

=SUM(A2,B2)

While I'm currently re-writing the calculation engine for the next major version of PHPExcel to support range formulas, it's not going to be available as a production release till the second half of next year

Upvotes: 1

Related Questions