{"id":65969,"date":"2026-07-05T08:00:39","date_gmt":"2026-07-04T23:00:39","guid":{"rendered":"https:\/\/norimen.net\/?p=65969"},"modified":"2026-07-03T00:21:07","modified_gmt":"2026-07-02T15:21:07","slug":"excel-rounding-error-ieee-754-floating-point","status":"publish","type":"post","link":"https:\/\/norimen.net\/en\/excel-rounding-error-ieee754-floating-point\/","title":{"rendered":"Excel Quirks | Why Calculation Results Drift Due to Accumulated Fractional Places, and a Look at IEEE 754"},"content":{"rendered":"

Hello, everyone.<\/p>\n

This is Enta.<\/p>\n

Have you ever set up a quantity tally in Excel only to find that \u201cwhen you add everything up, it\u2019s off by 1 yen from your manual calculation\u201d?<\/p>\n

Even when calculating the cost of slope construction, if you keep applying formulas like \"area \u00d7 unit price \u00d7 consumption tax,\"There is always a difference of 1 yen or 1 m\u00b2 in the last line.<\/strong>It's a phenomenon.<\/p>\n

This isn't an Excel bug, but...IEEE 754: The Global Standard for Floating-Point Numbers<\/strong>That's why lol<\/p>\n

I got really pissed off about this a while back and looked it up, so I thought I'd share it with you all lol<\/p>\n

That said, don't people use Excel much for calculations these days?<\/p>\n

I'm still an Excel fan!<\/p>\n

This one can do just about anything, so... lol<\/p>\n

\n

But that's beside the point.<\/p>\n

This time,The True Nature of Accumulated Rounding Errors in Excel (and Many Other Software Programs)<\/strong>,IEEE 754 Double-Precision Floating-Point Specifications<\/strong>So,<\/p>\n

The Correct Workaround Using the ROUND Function<\/strong>I'll explain my experience of struggling with quantity calculations.<\/p>\n

Since this is clearly stated in Microsoft's official documentation, the source is 100%\u2014no doubt about it.<\/p>\n

\"A<\/p>\n

The Truth Behind Excel's Quirks | IEEE 754 Specification<\/h2>\n

To put it simply,Calculation errors in Excel are not bugs, but rather the result of strictly adhering to the IEEE 754 standard for floating-point arithmetic.<\/strong>That's it.<\/p>\n

It\u2019s like, \u201cHuh?\u201d isn\u2019t it?<\/p>\n

 <\/p>\n

Microsoft states the following regarding Excel's calculation accuracy: Microsoft Excel is designed based on the IEEE 754 standard, which defines how floating-point numbers are stored and calculated. Excel can store numbers ranging from 1.79769313486232E308 to 2.2250738585072E-308, butThe number of significant digits must be 15 or fewer.<\/strong>...is limited to.<\/p>\n

Microsoft Learn Official Documentation: \"Using floating-point operations can result in inaccurate results in Excel\"<\/a><\/p>\n

In other words, Excel internally...All numbers are handled in binary.<\/strong>Therefore, when converting a number written in decimal to binary,Rounding error<\/strong>...is born.<\/p>\n

The essence of this phenomenon is that these effects accumulate as calculations are performed repeatedly.<\/p>\n

Why Does Error Occur When Converting from Decimal to Binary?<\/h3>\n

A finite decimal number such as \"0.1,\"In binary, it becomes a repeating decimal that goes on indefinitely.<\/strong>That is the cause.<\/p>\n\n\n\n\n\n\n\n\n\n
Decimal<\/th>\nBinary Representation<\/th>\nNotes<\/th>\n<\/tr>\n<\/thead>\n
0.5<\/td>\n0.1<\/td>\nCan express it perfectly<\/td>\n<\/tr>\n
0.25<\/td>\n0.01<\/td>\nCan express it perfectly<\/td>\n<\/tr>\n
0.1<\/strong><\/td>\n0.0001100110011\u2026 (repeating)<\/strong><\/td>\nInfinite loop, rounding error occurs<\/strong><\/td>\n<\/tr>\n
0.2<\/strong><\/td>\n0.0011001100110\u2026 (repeating)<\/strong><\/td>\nIbid.<\/strong><\/td>\n<\/tr>\n
0.3<\/td>\n0.0100110011001\u2026 (repeating)<\/td>\nIbid.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

In IEEE 754 double precision (64-bit), this infinite loopStore after truncating to a certain number of digits<\/strong>As a result, a slight error (approximately \u00b12.8 \u00d7 10\u207b\u00b9\u2077) occurs (Microsoft Learn).<\/p>\n

\"How<\/p>\n

Specific Examples | Common Discrepancies in Excel<\/h2>\n

Prerequisite Knowledge | The Meanings of TRUE and FALSE<\/h3>\n

Before we get into specific examples, in Excel... TRUE \/ FALSE<\/strong> Let's review that.<\/p>\n

It\u2019s a bit late to be saying this, but since this is the absolute basics for everyone, you should definitely make sure to remember it!<\/p>\n

In Excel \"=\" and \">\" and \"<"<\/strong> When you use it, the result is TRUE (True, Equal, Valid)<\/strong> \u304b FALSE (false, not equal, or not true)<\/strong> ...and it comes back.<\/p>\n\n\n\n\n\n\n\n
Mathematical Formula<\/th>\nMeaning<\/th>\nResults<\/th>\n<\/tr>\n<\/thead>\n
=1=1<\/code><\/td>\nIs 1 equal to 1?<\/td>\nTRUE<\/strong><\/td>\n<\/tr>\n
=1=2<\/code><\/td>\nAre 1 and 2 equal?<\/td>\nFALSE<\/strong><\/td>\n<\/tr>\n
=5 > 3<\/code><\/td>\nIs 5 greater than 3?<\/td>\nTRUE<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

In other words \u300c=A1=0.3<\/code> \"Returns FALSE\" = \"The value in A1 is not equal to 0.3\"<\/strong> That is what it means. Please keep this in mind as you look at the examples below.<\/p>\n

*For formatting purposes, the equations are written using the full-width equals sign (=). When entering them into Excel, please replace it with the half-width \"=\" sign.<\/p>\n

Example 1 | The Mystery of Why 0.1 + 0.2 \u2260 0.3<\/h3>\n

Please try entering the following into an Excel cell:<\/p>\n\n\n\n\n\n\n
Cell<\/th>\nMathematical Formula<\/th>\nDisplay Results<\/th>\nInternal value<\/th>\n<\/tr>\n<\/thead>\n
A1<\/td>\n= 0.1 + 0.2<\/code><\/td>\n0.3<\/strong><\/td>\n0.30000000000000004\u2026<\/td>\n<\/tr>\n
A2<\/td>\n=A1=0.3<\/code><\/td>\nFALSE<\/strong><\/td>\n(Strictly speaking, they are not equal.)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Even though the display shows the correct value of \u201c0.3,\u201d=A1=0.3<\/code> When comparing them, becomes FALSE<\/strong>. That's just how IEEE 754 works, lol.<\/p>\n

It's so annoying, isn't it? lol<\/p>\n

Example 2 | Adding 100 Causes the Number to Deviate from an Integer<\/h3>\n

=SUM(A1:A100)<\/code> When you sum multiple cells as shown, the minor errors in each cell willStacks up to 100 pieces<\/strong>\uff1a<\/p>\n

    \n
  • Add 0.1 \u00d7 100 one by one \u2192 Result: 9.99999999999998\u2026<\/li>\n
  • Expected value of 10<\/strong> may not match exactly<\/li>\n<\/ul>\n

    When calculating quantities for civil engineering projects, 100-row summary of \"Area \u00d7 Unit Price\"<\/strong> If you do that, on the last line... A discrepancy of a few to several dozen yen<\/strong> That's why it happens.<\/p>\n

    \"[Example<\/p>\n

    Example 3 | Accumulation of Fractional Values in Slope Area Calculations (A Common Occurrence on Slope Construction Sites)<\/h3>\n

    Specific scenarios that commonly arise when calculating the area for slope construction:<\/p>\n

      \n
    • Framework Engineer:Use the square root function in Excel to calculate areas and then add them together<\/strong><\/li>\n
    • In the final SUM row, the manual calculation and A discrepancy of 0.1 to 0.5 m\u00b2<\/strong><\/li>\n<\/ul>\n

      This is the kind of situation where you end up wasting half a day without knowing why.<\/p>\n

      I remember using this to do calculations over and over again back in the day! lol<\/p>\n

      Workaround | Round Each Time Using the ROUND Function<\/h2>\n

      Simply selecting \"Reduce the number of decimal places\" in the display format is not enough<\/h3>\n

      To reduce the number of decimal places using Excel's \"Format Cells\" feature,<\/p>\n

      The values are rounded only for display purposes; the underlying values still contain errors.<\/strong>\u3002<\/p>\n

      If this is carried over to the next calculation, the error will remain, so this does not provide a fundamental solution.<\/p>\n

      The real solution is to round the value itself within the function.<\/strong>That.<\/p>\n

      When to Use the ROUND Function<\/h3>\n\n\n\n\n\n\n\n\n\n\n
      Function<\/th>\nOperation<\/th>\nUses<\/th>\n<\/tr>\n<\/thead>\n
      =ROUND(value, num_digits)<\/code><\/td>\nRounding<\/td>\nGeneral Purpose \/ Amount Calculation<\/strong><\/td>\n<\/tr>\n
      =ROUNDDOWN(value, num_digits)<\/code><\/td>\nTruncation<\/td>\nCost Estimation That Errs on the Side of Caution<\/td>\n<\/tr>\n
      =ROUNDUP(value, num_digits)<\/code><\/td>\nRounding up<\/td>\nQuantity of Materials to Order (to Avoid Shortages)<\/td>\n<\/tr>\n
      =INT(value)<\/code><\/td>\nIntegerization (negative numbers are floor functions)<\/td>\nIf you want to cap the value at an integer<\/td>\n<\/tr>\n
      =FLOOR(value, unit)<\/code><\/td>\nRound down to the specified unit<\/td>\nRounding to the nearest 10 yen or 100 yen<\/td>\n<\/tr>\n
      =CEILING(value, unit)<\/code><\/td>\nRound up to the specified unit<\/td>\nSame as above, to prevent shortages<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      \"The<\/p>\n

      Always do it this way when preparing cost estimates and preliminary calculations!<\/h3>\n

      Here\u2019s the process I follow for construction cost estimates and preliminary calculations:<\/p>\n

        \n
      • Amount calculations are always =ROUND(Quantity * Unit Price * 1.10, 0)<\/code><\/strong> and round to the nearest yen each time<\/li>\n
      • Quantity Calculations (Area and Volume) are =ROUND(value, 2)<\/code> ...rounded to two decimal places<\/strong> Roll it up and secure it<\/li>\n
      • Do not recalculate in the last row of the summary<\/strong>(Simple sum of values rounded off along the way)<\/li>\n<\/ul>\n

        With this, you can create a statement of account that matches your manual calculations down to the last yen.<\/p>\n

        You can also use the \"Excel Calculation Precision Options\" at the same time<\/h2>\n

        In Excel, there is The \"Calculate Based on Number of Displayed Digits\" option<\/strong>You can also find it here (File \u2192 Options \u2192 Advanced \u2192 When calculating this workbook).<\/p>\n

        If you turn this ON,Calculate by rounding the internal values according to the cell's display format<\/strong>They'll do it for you. However,<\/p>\n

          \n
        • Applies to the entire book (cannot be applied selectively)<\/li>\n
        • There is a possibility that existing calculation results could change drastically.<\/li>\n
        • It's safer not to use it in cost estimates shared within the team.<\/strong><\/li>\n<\/ul>\n

          To be honest, in reality, I don't use this option, and instead,It's safer to control them individually using the ROUND function<\/strong>I'd recommend this to people like that.<\/p>\n

          The Same Applies Outside of Excel | The Limitations of IEEE 754 Compliance<\/h2>\n

          The floating-point error in IEEE 754 is,This is not a problem specific to Excel; it occurs in all major spreadsheet programs, such as Google Sheets, Numbers, and OpenOffice.<\/strong>I will.<\/p>\n

          Furthermore, the same specification applies to most programming languages, such as C, Python, JavaScript, and SQL.<\/p>\n

          \"IEEE<\/p>\n

          In other words,Not \"It's inaccurate because it's Excel,\" but a globally standardized specification<\/strong>. As a civil engineer, you should understand this and use the ROUND function\u2014that\u2019s the correct approach.<\/p>\n

          5 Key Points to Keep in Mind<\/h2>\n

          When using Excel for area calculations, quantity calculations, and similar tasks, keep the following five points in mind.<\/p>\n

            \n
          • \u2705 Amount calculations are always =ROUND(\u2026,0)<\/code><\/strong> Round to the nearest yen<\/li>\n
          • \u2705 Quantity calculations are =ROUND(\u2026,2)<\/code><\/strong> and set to two decimal places<\/li>\n
          • \u2705 Rounding in the display format does not change the underlying value.<\/strong>Being mindful of...<\/li>\n
          • \u2705 =A1=0.3<\/code> Avoid direct comparisons such as<\/strong>(The Pitfall of a \"FALSE\" Result)<\/li>\n
          • \u2705 Do not use the \"Accuracy Option\" in cost estimates used by the team<\/strong><\/li>\n<\/ul>\n
            \n

            If you're unsure, first consider whether to insert a single layer of the ROUND function.<\/p>\n

            Simply inserting one ROUND eliminates 99% of the deviation caused by IEEE 754.<\/p>\n

            It's not that Excel has \"quirks,\" but rather,Quirks of Floating-Point Arithmetic Around the World<\/strong>We need to understand and master it.<\/p>\n

             <\/p>\n

            See you later.<\/p>\n

            People often question the necessity of placement tolerances during concrete placement, but there\u2019s always only one answer!<\/a><\/p><\/blockquote>\n