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.