Description: ------------ The formulas produced by Spreadsheet_Excel_Writer are not produced when opened with Excel(2000). OpenOffice(1.1/1.0) has no problem and displays the formula values as expected. Excel opens by displayin the #VALUE in place of the formulas. The formulas are correct, because: 1) OOffice and Gnumeric open correctly 2) In Excel, when you click on the formlula in the edit field and hit return, then the formula pops a value ( which is correct) 3) Improting the file to OOffice and re-exporting to excel corrects this problem. I will include a sample file later

I can also confirm that this same issue arises with Office 2003.

I don't see #VALUE displayed in Excel 2003. I see a 0 displayed. If I put the formula in edit mode and then press ctrl+shift+enter then the formula is evaluated properly. Is there any short-term workaround for this even?

This doesn't happen with simple formulas [ie. writeFormula(0,0,"=SUM(C2:C10)")]. When the formulas get more complex, the evaluation of these formulas stops and returns 0. Example of an 'advanced' formula: writeFormula(1,1,"=SUM(IF('data'!D2:D5000=\"Fearphage\",IF('data'!G2:G5000=23,1,0),0))")

fearphage, can you explain to me what that formula is calculating?

I also can confirm that problem. My formula causing #VALUE! is: =IF(AND(Q18=0;Q21=0);0;Q21/(SUMIF(K19:P19;">0";K21:P21)+SUMIF(K19:P19;"<1";K18:P18))) or even in much simpler shape: =SUMIF(K19:P19;">0";K21:P21) Effect is the same: #VALUE! after sheet opened and correct result after edit and return without changing anything.

More details about this bug. Perl implementation of this class generates correct XLS files. Please check following code snippets: in PERL: #!/usr/bin/perl -w use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new("test.xls"); my $worksheet = $workbook->add_worksheet(); $worksheet->write(0, 0, "AC" ,'' ); # write_string() $worksheet->write(1, 0, "AAC" ,'' ); # write_string() $worksheet->write(2, 0, 2 ,'' ); # write_number() $worksheet->write(3, 0, 3.00001 ,'' ); # write_number() $worksheet->write(4, 0, "" ,'' ); # write_blank() $worksheet->write(5, 0, '' ,'' ); # write_blank() $worksheet->write(6, 0, undef ,''); # write_blank() $worksheet->write(7, 0 ,''); # write_blank() $worksheet->write(8, 0, '=SUMIF(A1:A2,"=AC",A3:A4)',''); $workbook->close(); in PHP: <? require('Spreadsheet/Excel/Writer.php'); $workbook = new Spreadsheet_Excel_Writer('testphp.xls'); $worksheet =& $workbook->addWorksheet(); $worksheet->write(0, 0, "AC" ,'' ); # write_string() $worksheet->write(1, 0, "AAC" ,'' ); # write_string() $worksheet->write(2, 0, 2 ,'' ); # write_number() $worksheet->write(3, 0, 3.00001 ,'' ); # write_number() $worksheet->write(4, 0, "" ,'' ); # write_blank() $worksheet->write(5, 0, '' ,'' ); # write_blank() $worksheet->write(6, 0, 'undef' ,'' ); # write_blank() $worksheet->write(7, 0 ,''); # write_blank() $worksheet->write(8, 0, '=SUMIF(A1:A2,"=AC",A3:A4)',''); $workbook->close(); ?> Hope this'll help to find source of the problem.

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

Mine works for SUM but SUBTOTAL gives #VALUE! Work around: after the Excel is generated, manually find and replace SUBTOTAL with SUBTOTAL.

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(); }

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(); ?>

I can confirm this with mgillow's test script.

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

Thank you for taking the time to report a problem with the package. This problem may have been already fixed by a previous change that is in the SVN of the package. Please checking out the SVN repository of this package and upgrade svn checkout svn.php.net/repository/pear/packages/Spreadsheet_Excel_Writer/trunk pear upgrade package2.xml or pear upgrade package.xml If you are able to reproduce the bug with the latest SVN, please change the status back to "Open". Again, thank you for your continued support of PEAR.