Forum Discussion
Excel bug incorrect calculation
same data different results, it is probably a floating point multiplication error but from paid enterprise application made to work with data I expected more
3 Replies
- SergeiBaklanDiamond Contributor
Excel:
Google Sheet:
Zoho:
OnlyOffice:
I guess same story will be with other spreadsheet software, at least for majority of it.
Actually =50*2.05 is bit less than 102.5. We may check by
=(50*2.05 -102.5)*1e10which returns -0.000142109 both for Excel and Google Sheet.
If unzip Excel, above number is kept as
<f>50*2.05</f><v>102.49999999999999</v>Power Query also return number less that 102.5
but Power Query uses banking rounding, i.e. to nearest even. If we add 1 in formula the number will be rounded to 104, not 103
MROUND rounds up if value is equal or greater than half of multiplier, otherwise rounds down.
- _jrCopper Contributor
But then why ROUND rounds correctly to nearest one and interestingly my google sheet shows correct result
- m_tarlerSilver Contributor
Yes I'm sure it is a floating point issue but to make things more interesting I repeated your example on row 15 but then the Function ROUND() worked fine (see row 16). I further used the Excel Labs in grid tool to show the calculations and in cell B15 it shows different result than the grid (see yellow notification and how it shows the correct 103. But in cell D15 I included the calculation directly inside the MROUND() function and both the grid and Excel Labs agree on the wrong result:
I know this doesn't help you but I thought it was interesting.