0

This question is similar to In Excel, when I enter 22222.09482 then I see 22222.0948199999 number in the formula bar but it isn't the same. It's about how to verify with precision.

A simple calculation seems to be incorrect, or more accurately, it seems to be imprecise. I verified this using an IF formula. Also tested this in Excel. Basically, I noticed that when increasing the number of significant figures or decimal points the result doesn't appear to be the same.

//Why is this? Have I done something wrong? Can anyone replicate this on their end?//

It seems this appears to be related to the Floating-point arithmetic (see link as pointed out by Cybernetic.Nomad in comments below).

However, the goal here is to verify calculations when Excel and Google Sheet give imprecise results. I thought a simple IF formula can do this but it doesn't due to the issue above and illustrated below. The only option as per the comments is combining IF with ROUND but that only gives precision to a specified decimal point.

19157.39-19052 should and must equal 105.39. However, Google Sheets and Excel seems to disagree giving 105.3899999999999 as per screen shots below: enter image description here enter image description here

Please see example Google Sheet below (you may comment on this sheet as well). https://docs.google.com/spreadsheets/d/1UZQ0FukVw0smbtUJYhZQxwRhmqdEycQI93SrYV_mbQk/edit?usp=sharing

questioner56
  • 67
  • 1
  • 1
  • 7
  • 1
    [This may prove of interest](https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result) – cybernetic.nomad Jun 16 '23 at 15:44
  • 1
    Excel is only precise up to a certain decimal place which means it will round up. I don’t see how a whole number minus 19157.39 could ever equal 105.49. Excel is accurate but it was never designed for this type of precision. If you need it use a more appropriate tool. – Ramhound Jun 16 '23 at 15:53
  • @Ramhound thank you that was a typo. I've corrected it to now show 105.39 – questioner56 Jun 16 '23 at 15:59
  • 1
    With Excel office 365 I get the 105.39. https://imgur.com/PfKhEYh But yes I still get the False on the equating. https://imgur.com/rc10Zhq – Scott Craner Jun 16 '23 at 16:04
  • Exactly, thank you @ScottCraner. I think if you keep adding decimal points you would get the same result as I have above – questioner56 Jun 16 '23 at 16:09
  • 2
    You will need to use ROUND() to round to the decimal you want to compare: `=ROUND(B5=B1,2)` Then they will be equal. https://imgur.com/a/a8FhGZS Bottom line this is something we must deal with. If precision matters round to the precision you want. – Scott Craner Jun 16 '23 at 16:09
  • 1
    And you are correct, if I expand it will show the `9999` – Scott Craner Jun 16 '23 at 16:11
  • 1
    @questioner56 - Just round the results to 3 decimal places. – Ramhound Jun 16 '23 at 16:13
  • @Ramhound, cybernetic.nomad & ScottCraner, thank you for your comments. I've used IF with Round formula as it seems like the best option at the moment. Though, hypothetically speaking, if the exact figure was needed to be verified (without rounding), I'm guessing there is no workaround or would have to use different software. – questioner56 Jun 20 '23 at 12:12
  • 1
    You need to use a fixed-point decimal-precision data type. Databases like [MySQL](https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html) and [MS Access](https://eggerapps.at/mdbviewer/docs/en/field-types.html) support this, but spreadsheet apps mostly don't; they use floating-point. The solution generally employed by accountants is to encode monetary values in cents instead of dollars, or whatever unit of maximum precision is needed for their use case (e.g. thousandths of a cent), so that all values used in calculations are integers; and then convert to dollars as the *final* step. – Jivan Pal Jun 20 '23 at 13:42
  • @JivanPal thank you for this comment! The cents or thousandths of a cent is an interesting approach. Weird how in cybernetic.nomad's link, https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result Excel 2016 doesn't appear to apply to Excel 2016 – questioner56 Jun 21 '23 at 22:07

0 Answers0