internal error - nested IF+MROUND formula

[burdziakm]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Nested IF+MROUND formula:

=IF(MROUND(IF(B4<2200,2.24,(B4*1.2)/1000),0.47)<50,MROUND(IF(B4<2200,2.24,(B4*1.2)/1000),0.47),IF(MROUND(IF(B4<2200,2.04,(B4*1.2)/1000),0.47)<2.6,MROUND(2.04,0.47),MROUND(50,0.47)))

Should return numerical value. It works in LibreOffice, Microsoft Office and Google Sheets.

What is the current behavior?

Throws exception:

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: kalkulator-krotki!B5 -> internal error in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:390 Stack trace: #0 /var/www/html/index.php(12): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue() #1 {main} thrown in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php on line 390

What are the steps to reproduce?

<?php

require 'vendor/autoload.php';

// Create new Spreadsheet object
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setLoadSheetsOnly(["kalkulator-krotki"]);
$spreadsheet = $reader->load("sheet.xlsx");
$spreadsheet = $spreadsheet->getActiveSheet();

// add code that show the issue here...
$value = $spreadsheet->getCell('B5')->getCalculatedValue();
echo $value;

PhpSpreadsheet-bug-example.zip

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Tested on XLSX written in LibreOffice on macOS and on XLSX format exported from Google Sheets.

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.27.0, PHP 7.4

[MarkBaker]

As per the PhpSpreadsheet documentation
When writing a formula to a cell, formulae should always be set as they would appear in an English version of Microsoft Office Excel, and PhpSpreadsheet handles all formulae internally in this format. This means that the following rules hold:

• Decimal separator is . (period)
• Function argument separator is , (comma)
• Matrix row separator is ; (semicolon)
• English function names must be used

If you set a formula in the MS Excel GUI, then it will display using the GUI's localisation settings; but PhpSpreadsheet is not locale-aware, so en_US rules apply when setting a formula (unless you enable locale for formulae as described in the documentation).

[burdziakm]

Yes, I know that. Both Google Sheets and Libre Office were set to American English and used english formula names.

[MarkBaker]

PhpSpreadsheet's calculation engine has a built-in lazy evaluation (branch pruning) for IF functions, that isn't always correct; and I think that may be the cause of the problems here with the nested references to cell B4.

You can disable lazy evaluation with the fiollowing lines:

    $calculationEngine = Calculation::getInstance($spreadsheet);
    $calculationEngine->disableBranchPruning();

And the result will then be correct.

[burdziakm]

Yes, this solved problem! Thank You ☺️

[thisisjeffsnow]

PhpSpreadsheet's calculation engine has a built-in lazy evaluation (branch pruning) for IF functions, that isn't always correct; and I think that may be the cause of the problems here with the nested references to cell B4.

You can disable lazy evaluation with the fiollowing lines:

    $calculationEngine = Calculation::getInstance($spreadsheet);
    $calculationEngine->disableBranchPruning();

And the result will then be correct.

disableBranchPruning() solved a very similar issue I was also facing with complex nested formulas. Your reply was one of the first to show when searching the issues list for the internal error when using nested IFs. Thank you for providing this solution!