Calculation Issues

How to Fix Excel Floating-Point Errors in 0.1 Number Series

Chanuka Geekiyanage Oct 16, 2026 869 views

Question details

Users need to resolve microscopic decimal inaccuracies when attempting to create a precise sequence of numbers with a 0.1 increment.

How to Fix Excel Floating-Point Errors in 0.1 Number Series

Product: Microsoft Excel
Device & OS: not provided
Scenario: Generating a sequential number series with decimal increments (e.g., 0.1) by dragging a formula or using a fill series command.
Observed behavior: Excel displays unintended long decimal values, such as -5.50000000000001 or -2E-14, instead of exact 0.1 increments due to how computers store binary floating-point numbers.

Before you start

Verify your Excel version, as the standard ROUND function works in all versions (including Excel 2016), while the dynamic SEQUENCE formula requires Microsoft 365 or Excel 2021.

Solution 1Recommended

Use the ROUND Function in Your Formula

The most universally compatible method to force Excel to correct the decimal precision manually and eliminate floating-point artifacts.

Because Excel stores fractions like 0.1 in binary format, tiny discrepancies compound during repetitive addition. Wrapping your arithmetic inside a ROUND function forces Excel to evaluate only the specified decimal places.

Enter the starting value

Select the cell where you want your sequence to begin (e.g., A1) and type the starting number.

Apply the ROUND formula

In the cell directly below (e.g., A2), input the formula =ROUND(A1+0.1, 1). This ensures the sum is strictly calculated to one decimal place.

Fill the series

Click the small green square at the bottom-right corner of cell A2 and drag it downward to generate the remainder of your perfectly rounded number series.

Compatibility: This method is highly recommended as it works smoothly in older editions like Excel 2016 and Excel 2019.

Solution 2

Generate the Series with the SEQUENCE Function

For users on modern Excel versions, you can instantly spill an entire sequence without dragging cells manually.

Solution 3

Copy and Paste the Series as Values

Convert dynamic formulas into static text to lock in the correctly displayed numbers and prevent future background calculation errors.

Professional Spreadsheet Alternative

Create Precise Number Series in WPS Spreadsheet

WPS Office provides a highly capable spreadsheet application that fully supports standard rounding formulas, allowing you to sidestep floating-point calculation errors easily.

1. Open a new worksheet: Launch WPS Office and open a new or existing Spreadsheet document.
2. Set the starting point: Type your starting sequence number in the first cell, for example, cell A1.
3. Apply the rounding formula: In cell A2, type =ROUND(A1+0.1, 1) to define your precise fractional increment.
4. Drag to fill: Click the fill handle on the bottom-right of cell A2 and drag downwards to instantly generate an error-free sequence.

Easily eliminate floating-point calculation errors using standard ROUND logic.Flawless compatibility with Microsoft Excel .xlsx format and core formulas.Lightweight, 100% free to download, and incredibly user-friendly.Familiar tabbed interface so you can transition with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show long decimals like -5.50000000000001 instead of exact numbers?

This occurs because Excel utilizes the IEEE 754 standard for binary floating-point arithmetic. Many decimal fractions, such as 0.1, cannot be represented perfectly in binary. As Excel repetitively adds these binary approximations, microscopic inaccuracies compound and become visible as long decimal artifacts.

Does the SEQUENCE function work in Excel 2016?

No, the SEQUENCE function is a dynamic array feature introduced in Microsoft 365 and Excel 2021. If you are running older versions like Excel 2016 or 2019, you must rely on standard formulas like =ROUND() and drag the fill handle manually.

Can I permanently fix floating-point errors in Excel settings?

You can enable 'Set precision as displayed' under File > Options > Advanced > When calculating this workbook. However, this is generally not recommended because it permanently alters the underlying values in your entire workbook to match their display format, which can cause severe data loss in other sensitive financial or scientific calculations.

Excel & Data Problems