FAQ
Edit report at http://pear.php.net/bugs/bug.php?id=3203&edit=1

ID: 3203
Comment by: dcc24
Reported By: dustin at carrollhq dot com
Summary: Formulas not opening properly in Excel
Status: Verified
Type: Bug
Package: Spreadsheet_Excel_Writer
Operating System: irrelevant
Package Version: 0.9.2
PHP Version: 4.3.1
New Comment:

These type of formulas are called "array formulas" or "cse formulas" in
Excel. An array formula is a formula that works with an array, or
series, of data values rather than a single data value. To enter a
formula as an array formula, you have to type the formula in the cell
and press the CTRL SHIFT and ENTER keys at the same time rather then
just ENTER (hence CSE).

I cannot find a way in Excel even with VBA to declare a formula or the
contents of a cell as an array formula unless you press CTRL SHIFT
ENTER.

I think we are out of luck in getting these fixed as MS didn't create a
way other than CTRL SHIFT ENTER to declare an array formula.

http://office.microsoft.com/en-us/excel/ha010872901033.aspx


Previous Comments:
------------------------------------------------------------------------

[2009-11-29 02:34:06] cschmitz

<div id="changeset">
<span class="removed">-Status: Open</span>
<span class="added">+Status: Verified</span>
<span class="removed">-Package Version:</span>
<span class="added">+Package Version: 0.9.2</span>
</div>I can confirm this with mgillow's test script.

------------------------------------------------------------------------

[2008-09-01 05:46:54] mgillow

I have this issue where I'm trying to SUM a selection of cells where
one or more has a string value.
If I use the =SUM(A1:C1) notation, it works fine, but using this one
=SUM(A1,B1,C1) gives the #VALUE error until you re-write it in Excel at
which point it works fine.

My test case is as follows:

<?
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();

$worksheet =& $workbook->addWorksheet("test");

$worksheet->write(0, 0, 1);
$worksheet->write(0, 1, A);
$worksheet->write(0, 2, 2);

$worksheet->writeFormula(0, 3, "=SUM(A1:C1)"); // Works OK
$worksheet->writeFormula(0, 3, "=SUM(A1,B1,C1)"); // Gives #Value

$workbook->send('test.xls');
$workbook->close();
?>

------------------------------------------------------------------------

[2008-03-22 09:52:25] doconnor

Works for me with OpenOffice Calc (2.3.1)

function xls_test()
{
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer('blah.xls');
$worksheet =& $workbook->addWorksheet('whatever');
$worksheet->write(0, 0, 3);
$worksheet->write(1, 0, 4);
$worksheet->writeformula(2, 0, "=SUM(A1:A2)");
$workbook->close();
}

------------------------------------------------------------------------

[2008-03-19 13:43:00] amilling

Mine works for SUM but SUBTOTAL gives #VALUE!

Work around: after the Excel is generated, manually find and replace
SUBTOTAL with SUBTOTAL.

------------------------------------------------------------------------

[2008-03-01 04:10:28] thomas

This bug can be reproduced with a trivial formula, viewed with Excel
Viewer 8.0:

function xls_test()
{
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet('whatever');
$worksheet->write(0, 0, 3);
$worksheet->write(1, 0, 4);
$worksheet->writeformula(2, 0, "=SUM(A1:A2)");
$workbook->send('blah.xls');
$workbook->close();
}

I'm using Spreadsheet_Excel_Writer 0.9.1 on debian. The result yo see
is 0

------------------------------------------------------------------------

The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://pear.php.net/bugs/bug.php?id=3203

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppear-bugs @
categoriesphp
postedFeb 9, '10 at 11:32p
activeFeb 9, '10 at 11:32p
posts1
users1
websitepear.php.net

1 user in discussion

Dcc24: 1 post

People

Translate

site design / logo © 2022 Grokbase