Nested IF producing blank result

Topics: Developer Forum, User Forum
Oct 29, 2010 at 12:06 AM

Hi Guys

I'm trying to create a nested if statement that does this with a percentage:

If the calculation returns an error, display "-"

If the result is larger than 999%, display "new"

Otherwise display the % value.

This code works:

$objPHPExcel->getActiveSheet()->setCellValue('E'.$currentrow, '=IF(ISERROR((C'.$currentrow.'-D'.$currentrow.')/D'.$currentrow.'),"-",(C'.$currentrow.'-D'.$currentrow.')/D'.$currentrow.')');
$objPHPExcel->getActiveSheet()->getStyle('E'.$currentrow)->getNumberFormat()->setFormatCode('\+0%_ ;\-0%');

But adding the nested IF just ends up giving me a blank cell:

$objPHPExcel->getActiveSheet()->setCellValue('E'.$currentrow, '=IF(ISERROR((C'.$currentrow.'-D'.$currentrow.')/D'.$currentrow.'),"-",IF((((C'.$currentrow.'-D'.$currentrow.')/D'.$currentrow.')>999),"new",((C'.$currentrow.'-D'.$currentrow.')/D'.$currentrow.')))');
$objPHPExcel->getActiveSheet()->getStyle('E'.$currentrow)->getNumberFormat()->setFormatCode('\+0%_ ;\-0%');

However if I paste the formula directly into the spreadsheet:


It works!

Is this something I'm doing wrong or is there an issue with nested IFs?

Many thanks in advance


Nov 1, 2010 at 10:55 PM
Edited Nov 1, 2010 at 10:58 PM

Nested IFs should work: please see my response to this query for an example of how to enable calculation engine debugging in PHPExcel. Output from the parser stack and log should help identify the cause of any problem.

Nov 2, 2010 at 1:36 AM

Hi Mark

Well after testing both version of the formula produce the same result in the debugger:

[44] => Worksheet!E7 -> Evaluation Result for IF() function call is a floating point number with a value of -0.6160708709549828

But the nested IF version still results in an empty cell in Excel, when you click the cell there is no value or formula displayed. Is there anything else I could investigate?