Why Excel Shows 0.30000000000000004 and How to Fix It

If you've ever seen a number like 0.30000000000000004, or an Excel rounding error such as -2.77556E-17 where you expected 0, nothing is wrong with your formula. It's a side effect of how computers store decimal numbers, called a floating-point error. This guide explains why it happens, where it causes real problems in Excel, and how to fix it with ROUND.

Why 0.1 + 0.2 isn't exactly 0.3#

Computers store numbers in binary, using only 0s and 1s. Whole numbers convert perfectly, but most decimals don't. Just as 1/3 becomes 0.33333... forever in decimal, 0.1 becomes an endless pattern in binary. The computer has to cut it off, so it stores a number extremely close to 0.1, but not exactly 0.1.

Add two of these approximations and the tiny gaps can show up. 0.1 + 0.2 is stored as 0.30000000000000004. That's the number most programming languages display for this sum.

Excel follows the same standard, called IEEE 754, used by almost all software. So this isn't an Excel bug, and you'll find the same behaviour in other spreadsheets and programs.

Why you usually don't see it in Excel#

Excel displays at most 15 significant digits. The error in 0.30000000000000004 is in the 17th digit, so Excel shows 0.3. Most of the time, the difference is invisible.

It becomes visible when a result should be exactly 0. A well-known example is this formula:

=1*(0.5-0.4-0.1)

You'd expect 0, but in a General-format cell Excel shows -2.77556E-17. That's scientific notation for -0.0000000000000000277556, a tiny leftover from the binary approximations.

The 15-digit limit has a second effect: Excel can't keep more than 15 digits of any number. If you type a 16-digit card or ID number, the last digit becomes 0. Store long IDs as text instead. Very large or impossible results give a different error; see how to fix #NUM!.

Where these tiny errors cause real problems#

A difference of 0.00000000000000004 doesn't matter for a total on screen. It matters when Excel tests for an exact value:

Here's a realistic case. An invoice of 100 is paid in three parts:

The balance =B2-B3-B4-B5 looks like 0, but because the payments are decimals, it may be stored as a tiny non-zero number. A test like =IF(B2-B3-B4-B5=0,"Paid","Due") can then return Due. If you work with remainders, the guide to MOD and INT shows the same effect with MOD.

How to fix it with ROUND#

The reliable fix is to round the result to the number of decimals that matter. ROUND changes the stored value, not just how it looks:

=ROUND(B2-B3-B4-B5,2)

This returns exactly 0. Now the status test works:

=IF(ROUND(B2-B3-B4-B5,2)=0,"Paid","Due")

The result is Paid. The syntax is =ROUND(number, num_digits): 2 for cents, 0 for whole numbers. The ROUND function guide also covers ROUNDUP and ROUNDDOWN.

For money, round at each step where a new amount is created, such as tax on a line item: =ROUND(B2*C2,2). Then totals add up to exactly what's shown. The guide to building an invoice with formulas follows this pattern.

How to compare numbers safely#

When you compare two calculated numbers, round both sides to the same number of decimals:

=ROUND(A2,2)=ROUND(B2,2)

Or test whether the difference is very small, using a tolerance (an amount small enough to ignore):

=ABS(A2-B2)<0.000001

ABS removes the minus sign, so the test works whichever number is bigger. Both formulas return TRUE for values that are equal for practical purposes. This matters in IF, COUNTIF and conditional formatting rules.

Why formatting isn't enough#

Formatting a cell to two decimals changes only what you see. The cell may show 0.00, but the stored value is still -2.77556E-17, and every formula that reads the cell uses that stored value. That's why an IF test can fail on a cell that clearly shows 0.00.

Excel has an option called Set precision as displayed (Windows: File > Options > Advanced; Mac: Excel > Preferences > Calculation). It makes Excel store every number exactly as it's shown. But the change is permanent: digits beyond the format are thrown away and can't be recovered. Rounding with formulas is safer, because you choose where it happens and the original data stays intact.

A different rounding error: totals that are off by a cent#

Not every rounding problem is floating point. A common one comes from formatting alone. Say you split 100 three ways with =100/3 in three cells, formatted to two decimals:

The total is correct, but anyone adding the visible numbers gets 99.99, so the sheet looks wrong. Round each share instead: =ROUND(100/3,2) stores exactly 33.33, and the total becomes 99.99, matching what's shown. Then decide where the missing cent goes, for example by making the last share =B1-B2-B3, where B1 holds the full amount. Now the shares add up to exactly 100.00 on screen and in the formula.

Practise it#

Practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet: you'll round prices, tax and totals and see each result checked instantly. Then practise TRUE and FALSE comparisons to build tests that don't break on tiny differences. Try the invoice exercise on this page first.