ROUND Function getCalculatedValue() error

Topics: Developer Forum, User Forum
Jul 31, 2013 at 9:28 AM
Edited Jul 31, 2013 at 9:29 AM

I am having a problem with the getCalculatedValue() returning the wrong result when using the ROUND function. Please see below for the code being run:
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Add some data
$objPHPExcel->getActiveSheet()->setCellValue('A1', '=ROUND(96.239,2)');
echo $objPHPExcel->getActiveSheet()->getCell('A1')->getCalculatedValue();
On my test Windows server I am getting 96.24 as expected. However, on my live Linux server I am getting a quite unexpected: 96.23999999999999

Live Linux server:
PHP 5.3.27
PHPExcel v1.7.9

Test offline XAMPP server running on Windows 7 Professional
PHP 5.4.4
PHPExcel v1.7.9

Any thoughts on this would be much appreciated.

Many thanks

Jul 31, 2013 at 1:39 PM
All I can suggest is that you apply a formatting mask to the cell: this looks to be a case of floating point accuracy when trying to represent decimal numbers in binary
Jul 31, 2013 at 2:38 PM
Edited Jul 31, 2013 at 2:39 PM
Hi Mark

Thank you for your response. I have tried adding:
before and after setting the cell value, but with no success I'm afraid. I also tried using apostrophes rather than quotation marks around the format code. I am not sure if this is the correct approach for setting the mask as suggested or if you would suggest something else?

I tried a few other tests to try to identify where the cause lies and got an interesting result. If I run an echo ROUND(96.239,2); on a blank php page the rounding works fine. However, once I include the phpexcel files, initialise a new instance and run the same pure PHP ROUND function it will make the same floating point error. So something during the initialising of a new phpexcel instance is causing the PHP ROUND function to behave differently... I'm not sure if this gives anyone any ideas?

Rounding error occurs:
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

echo "<br />".ROUND(96.239,2);
No Rounding error:

echo "<br />".ROUND(96.239,2);

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
Jul 31, 2013 at 3:54 PM
Edited Jul 31, 2013 at 3:57 PM
You'll need to use getFormattedValue() rather than getCalculatedValue() if you're using a format mask; and this will return a PHP string: it's the equivalent of number_format() in PHP.

Note that PHPExcel emulates the functionality of Excel's ROUND() function, which isn't the same as PHP's round() function.

PHPExcel's calculation engine sets the precision to 14dp, and it may be this difference that is causing an issue. However, adjusting that value may lead to reduced accuracy elsewhere with other functions

However, this looks like a standard computer floating point precision issue: and (as such) is way outside the scope of PHPExcel to fix; but feel free to take a look at the ROUND() function and see if you have any bright ideas.
Jul 31, 2013 at 6:02 PM
Thanks Mark. With your change I have been able to override the formatting of the round result. Unfortunately, I am not able to apply that solution to my site at the moment as I do not wish to force any specific formatting on the user. I will have to look to expand my code to allow the user to apply number formatting to individual cells...

Back to the ROUND issue itself, I was not able to find PHPExcel's ROUND function. In the Calculation.php file the ROUND function call appears to be:
'functionCall'      =>  'round',
rather than what I would have expected:
'functionCall'      =>  'PHPExcel_Calculation_MathTrig::ROUND',
Perhaps someone can give me a better idea on where to find the PHPExcel ROUND function. For now I have added a new function in Calculation > MathTrig.php which makes use of BC math (and registered a function Call for this in Calculation.php).
function BCROUND($number, $scale=0) {
                $fix = "5";
                for ($i=0;$i<$scale;$i++) $fix="0$fix";
                $number = bcadd($number, "0.$fix", $scale+1);
                return    bcdiv($number, "1.0",    $scale);
This gets around the problem, but is not ideal as any other use of ROUND elsewhere in my application will default to the broken version and not my new function. I have reported this to my server host to see if this can be fixed via apache.

As a final point, I would note that PHPExcel alters the functioning of the native ROUND PHP function. If PHPExcel is not initialised the rounding issue does not exist. So if anyone knows how PHPExcel changes the functioning of native functions that could be really helpful.

Many thanks once again Mark.