Nested IF producing blank result

Topics: Developer Forum, User Forum
Oct 28, 2010 at 11:06 PM

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:

=IF(ISERROR((C7-D7)/D7),"-",IF((((C7-D7)/D7)>999),"new",((C7-D7)/D7)))

It works!

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

Many thanks in advance

Lucas

Coordinator
Nov 1, 2010 at 9:55 PM
Edited Nov 1, 2010 at 9: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 12: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?

Cheers

 

Lucas