Calculation Issues

Fix Unexpected Decimal Digits in Excel SEQUENCE Function

Algirdas Jasaitis Sep 25, 2026 869 views

Question details

The user needs to know why generating a number sequence with decimal increments results in floating-point inaccuracies, and how to format or adjust the formula to return precise values.

How to Fix Unexpected Decimal Digits in Excel SEQUENCE Function

Product: Microsoft Excel
Device & OS: not provided
Scenario: Generating a sequence of numbers with decimal increments (e.g., 0.0001) for precise mathematical calculations or exact matching functions.
Observed behavior: The generated sequence produces values with tiny floating-point differences (such as 4.02E-12) instead of absolute zero or exact fractions, preventing accurate exact-match lookups.

Before you start

Identify the exact sequence parameters causing the calculation anomaly, and check if these output values are being used for exact-match lookups (like MATCH or VLOOKUP) where absolute precision is critical.

Solution 1Recommended

Use Integer Generation and Division

The most reliable method to prevent floating-point anomalies is to generate integer values with the SEQUENCE function and then divide the result by the necessary decimal factor.

Because binary floating-point arithmetic struggles to represent decimal fractions like 0.0001 exactly, avoiding decimals entirely during the sequence generation will yield mathematically precise results.

Select the target cell

Click on the cell where you want your sequence array to start spilling.

Modify the sequence parameters

Multiply your original start and step decimal values by a power of 10 to convert them into whole numbers. For example, change -4 to -40000 and 0.0001 to 1.

Input the integer division formula

Type the modified formula into the formula bar: =SEQUENCE(80001, 1, -40000, 1) / 10000.

Execute the formula

Press Enter. The dynamic array will generate and divide the integers, resulting in exact fractional values without floating-point errors.

Guaranteed Precision: This method completely sidesteps binary decimal representation issues during the generation phase, making the output perfectly safe for MATCH and exact XLOOKUP formulas.

Solution 2

Wrap the Sequence in a ROUND Function

Force the sequence output to a specific number of decimal places to eliminate tiny, accumulating floating-point anomalies.

Precise Array Calculations

Generate Exact Number Sequences with WPS Spreadsheet

WPS Office Spreadsheet fully supports dynamic arrays and advanced functions like SEQUENCE and ROUND, allowing you to generate precise data sets without hassle. It calculates seamlessly while maintaining full compatibility with your existing workbooks.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your spreadsheet file.
2. Enter the precise sequence formula: Select the target cell and type =SEQUENCE(80001, 1, -40000, 1) / 10000 to utilize the integer-division method.
3. Calculate the array: Press Enter to instantly spill the precise sequence values down the column.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Natively supports modern dynamic array functions including SEQUENCELightweight architecture for rapid calculation of large datasetsFree, feature-rich alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show numbers like 4.02E-12 instead of absolute zero?

Spreadsheet applications use the IEEE 754 standard for binary floating-point arithmetic. Since binary systems cannot exactly represent certain base-10 decimals (like 0.0001), microscopic rounding errors occur and accumulate during calculation, resulting in scientific notation like 4.02E-12 instead of absolute zero.

Will formatting the cells to 'Number' fix this calculation error?

No. Changing the cell format to display fewer decimal places merely hides the underlying floating-point error on your screen. The actual stored value remains imprecise, which means exact match lookup formulas will still fail.

Does the 'Precision as displayed' setting resolve the SEQUENCE decimal issue?

While enabling 'Set precision as displayed' forces stored values to match their visual format, it is heavily discouraged because it permanently truncates and alters all numerical data across your entire workbook. Using localized functions like ROUND is vastly safer.

Can I use these sequence fixes in older spreadsheet versions?

The SEQUENCE function is only available in Microsoft 365, Excel 2021, and modern spreadsheet software like WPS Office. If you use an older version, you must use alternative row generation methods like ROW() combined with basic arithmetic, which will also require the ROUND function to maintain decimal precision.

Excel & Data Problems