IF() statements with zeroes = Out of Memory

Topics: Developer Forum, Project Management Forum, User Forum
May 8, 2008 at 3:29 PM
Edited May 8, 2008 at 3:32 PM
So I have a document that I'm doing several functions like this: =IF(B5>0,D5/B5-1,"n/a")

You can see that the condition is checking to see if the cell is greater than zero, and if it's not it defaults to "n/a". This does not work at all however, as I get the following errors:

08-May-2008 09:19:00 Error Caught: 2 Division by zero /Users/cendrizzi/Zend/workspaces/DefaultWorkspace/R3D0/ThirdPartyLibs/php_excel/Classes/PHPExcel/Calculation.php(673) : runtime-created function 1 Array
08-May-2008 09:19:02 PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 80367424 bytes) in /Users/cendrizzi/Zend/workspaces/DefaultWorkspace/R3D0/include/cor/class/COR.class.php on line 323

So phpexcel seems to be calculating it anyway, and ignoring the condition. Looking through the code, it looks like A LOT of work has gone into this function stuff.

I'm not yet totally aware of how phpexcel works, but I was most troubled to see this didn't even work on the spreadsheet (as opposed to just spitting it out to html as I do by default). Why is phpexcel parsing and computing functions on the spreadsheet? Doesn't excel do it for you if you just pass it through to the cell?

Thanks,
Clark
Coordinator
May 9, 2008 at 6:24 AM
Seems like you are having 2 problems.
1) The "division by zero" warning is actually the PHP compiler that's going "wrong": it yields this message everytime it suspects a possible division by zero. It does not execute the code, but does warn you about a possible bug.

Try the following code on PHP to reproduce this behaviour:
<?php
function STATEMENT_IF($condition, $onTrue, $onFalse) {
   return $condition ? $onTrue : $onFalse;
}
 
$x = create_function('', 'return STATEMENT_IF(0>0, 5/0, "n/a");');
echo call_user_func($x);
?>

I suppressed this error in the calculation engine, so you should not be seeing it anymore.

2) Can you provide some more details on your script? (number of rows, number of columns, PHP version, PHPExcel version, ...)
Coordinator
May 9, 2008 at 5:18 PM

cendrizzi wrote:
I'm not yet totally aware of how phpexcel works, but I was most troubled to see this didn't even work on the spreadsheet (as opposed to just spitting it out to html as I do by default). Why is phpexcel parsing and computing functions on the spreadsheet? Doesn't excel do it for you if you just pass it through to the cell?

Because if you want to read an Excel spreadsheet, and write it out to HTML (showing values rather than formulae), and don't have MS Excel installed to do all the work for you, then you need PHPExcel to do the calcs.
Coordinator
May 9, 2008 at 7:35 PM
You can disable formula calculation (see documentation). Under the hood, OpenXML supports caching formula results which allows faster opening of Excel documents in the real Excel.

Can you send me your script (maarten ---AT--- phpexcel ---DOT--- NET)? Maybe there's an easy solution to your problem.
May 9, 2008 at 8:34 PM
Thanks for the comments. Disabling functions should work for doing the spreadsheet, I'll have to test that.

I will provide some examples at the start of next week.