1

Hi i have come across an issue comparing numbers calculated in the MS Excel and PHP.

The problem i'm having that MS Excel is truncating numbers up to max 15 digits if i understand correctly so i can not compare PHP and MS Excel results: for instance:

so 12.34567890123456789 would be truncated and equal to 12.34567890123450000,

or

12345.67890123456789012345 would be truncated and equal 12345.67890123450000000

in MS Excel.

How do i achieve the same in PHP so the numbers are truncated to max 15 digits?

I have found a suggestion to use function bcdiv() which allows to truncate numbers to specified number of decimal places, however numbers seem to loose digits in the middle, if more then 10 decimal places are set in the function and it is not the same as in MS EXCEL:

test

echo 123.4567890123456789;
echo bcdiv(123.4567890123456789, 1, 15);

//result

123.4567890123456789
123.4567890123500000000  // have lost digit 4 in the middle of the number

Does anyone know how to truncate numbers in PHP same as in EXCEL?

anonymous007
  • 319
  • 1
  • 3
  • 12

2 Answers2

2

Both PHP and Excel internally use the binairy IEEE 754 double precision format. However the numbers Excel calculates with are not the numbers that it displays. See:

https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

So you can force PHP to do the same weird thing that Excel does, but only when displaying numbers, not when calculating with them.

I find that PHP does round to roughly 15 digits as well, so I wouldn't bother to precisely emulate Excel's output unless you have a very good reason to do so.

The question is somewhat misleading because echo 123.4567890123456789; results in 123.45678901235 when I test it (PHP 5.6.36).

The reason the digit 4 becomes a digit 5 is that the digits behind the 4, namely '56789', make 4 bigger than 4.5 and it is therefore rounded up. So digit 4 is not lost.

More sources:

http://php.net/manual/en/language.types.float.php

https://en.wikipedia.org/wiki/IEEE_754

https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

KIKO Software
  • 15,283
  • 3
  • 18
  • 33
  • This explains what the problem is, but doesn't not answer the question of how to emulate it in PHP. – miken32 Jul 11 '18 at 17:27
  • You have trouble extracting the leading 15 figures from a number? – KIKO Software Jul 11 '18 at 17:29
  • I'm not the one asking "Does anyone know how to truncate numbers in PHP same as in EXCEL?" – miken32 Jul 11 '18 at 17:31
  • It seems the question is somewhat misleading when it comes to the truncation results reported. This could be PHP-version dependent though. – KIKO Software Jul 11 '18 at 17:53
  • Thanks for the answer, i can see the number in my example is rounded however was expecting function to truncate the number instead of rounding it, therefore thought it lost one digit. The only reason i'm trying to emulate the excel behaviour is to check the correctness of calculation in PHP, I'm aware that excel is using IEEE 754 standard, just looking to emulate same in PHP so I can test. so the best thing you can do is to round the numbers truncate them to certain amount of decimal places however I was looking "How to truncate to certain amount of digits" (not decimal places) – anonymous007 Jul 12 '18 at 08:08
  • 1
    The problem is that you don't want to truncate "to certain amount of digits", you want the same as in Excel, where the number is 15. That number is almost in the precision range of PHP which is 15.95 digits. The build-in conversion routine in PHP, from the binary format to the decimal format, works differently from the routine in Excel. If we weren't so close the precision limit we would be able to work around that, but not when it's very close. There's no room to work with anymore. Why do you need 15 digit precision in results? For me 123.45678901235 is the same as 123.45678901234 (apples?). – KIKO Software Jul 12 '18 at 08:38
1

Unfortunately, that's not how floating point numbers work in PHP. The digits you see are indeed configurable but it's only a display value:

$foo = 1/3;
$bar = 0.3333;
ini_set('precision', 9);
var_dump($foo, $bar, $foo==$bar);
ini_set('precision', 4);
var_dump($foo, $bar, $foo==$bar);

double(0.333333333)
double(0.3333)
bool(false)
double(0.3333)
double(0.3333)
bool(false)

The standard way to compare floats is to establish a threshold below which they're considered equal:

// Totally untested function copied from elsewhere!!
function equalFloats($a, $b, $threshold = 0.0000001) {
    return abs(($a-$b)/$b) < $threshold;
}

$foo = 1/3;
$bar = 0.3333;
var_dump($foo, $bar, equalFloats($foo, $bar), equalFloats($foo, $bar, 0.001));

double(0.33333333333333)
double(0.3333)
bool(false)
bool(true)

Arbitrary precision libraries are certainly an alternative but they need to be used correctly:

var_dump(bcdiv(123.4567890123456789, 1, 15)); // Float (precision already lost)
var_dump(bcdiv('123.4567890123456789', 1, 15)); // String (OK)

string(19) "123.456789012350000"
string(19) "123.456789012345678"
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • Thanks for the answer, knowing that bcdiv() works differently when the numbers are passed as strings actually helps to bring the results closer to what i'm getting in excel. – anonymous007 Jul 12 '18 at 08:29