Description: ------------ I used =SUBTOTAL(9,A1:A2) in formula, got #Value! error on opening generated XLS, text of formula was OK, and when I pressed enter on the formula in Excel it was accepted OK. I then did a sample XLS, one formula as generated by PEAR package, and another corrected in Excel, and viewed the file using BIFFviewer, noticed the PHP token for the "A1:A2" was x65 whereas Excel used x25 - I changed th line in parser.php $class = 2; to $class = 0; and it now generates SUBTOTAL formula correctly! Reproduce code: --------------- <?php require_once 'Excel/Writer.php'; include "../inc.inc"; $wb = new Spreadsheet_Excel_Writer(); $wb->send('subtot.xls'); $ws =& $wb->addWorksheet('sub total'); for ($i=1; $i<= 5; $i++) { $ws->write($i-1,0,$i); $ws->write($i-1,1,$i*2); } $ws->writeFormula(5,0,'=SUBTOTAL(9,A1:A5)'); $ws->writeFormula(5,1,'=SUBTOTAL(9,B1:B5)'); $wb->close(); ?>

I hate messing with things I don't understand. However, I can confirm that the fix mentioned above $class = 0; also worked for me on a similar problem formula syntax waqs correct but excel outputs VALUE error. Here is my code: $worksheetScores->writeFormula($row, $col, "=RANK({$xlsC}2, B2:{$endCol}2)"); Example Output: =RANK(B2,B2:W2)

I did change $class to zero whichsolved my problem for writeFormula(22,2,"=COUNTIF(C1:C20,1)"); But for writeFormula(23,2,"=CORREL(C1:C20,D1:D20)"); I had to swirch it back to $class=2; I do not understand which token I do get in the function. Somehow their must be a test whether one or more ranges should be used. All the best John Val

This bug has been fixed in CVS. If this was a documentation problem, the fix will appear on pear.php.net by the end of next Sunday (CET). If this was a problem with the pear.php.net website, the change should be live shortly. Otherwise, the fix will appear in the package's next release. Thank you for the report and for helping us make PEAR better. Temporary fix for mostly used formulas. Might break more complex ranges such as C20,D1:D20.