turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 156K Members
- 4,681 Online
- 38.3K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Really weird decimal amounts

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
## Really weird decimal amounts

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-09-2018 05:02 PM

Hi

I was disturbed with a very strange small decimal amounts (0.0000000000011) somehow added "from the sky" to the result of my formulas . The problem started from the cell highlighted in red in below screen shots.

Help from any one would be appreciated. The original file is attached for your investigation.

A | B | C | D | E5 = E4-B5+C5 |

13,469.580000000000000 | ||||

- | 13,469.580000000000000 | |||

5.89 | 5.890000000000000 | 13,475.470000000000000 | ||

9.68 | 9.680000000000000 | 13,485.150000000000000 | ||

1.17 | 1.170000000000000 | 13,486.320000000000000 | ||

4.86 | 4.860000000000000 | 13,491.180000000000000 | ||

(13,000.00) | (13,000.000000000000000) | 491.180000000011000 |
||

0.38 | 0.380000000000000 | 491.560000000011000 | ||

0.66 | 0.660000000000000 | 492.220000000011000 | ||

(492.22) | (492.220000000011000) | - | ||

- | - |

Dr | Cr | Balance | ||

17,424.40000000000000000000000000000000000 | ||||

17,424.40000000000000000000000000000000000 | ||||

7.620000000000000000000 | 7.620000000000000000 | 17,432.02000000000000000000000000000000000 | ||

12.530000000000000000000 | 12.530000000000000000 | 17,444.55000000000000000000000000000000000 | ||

1.500000000000000000000 | 1.500000000000000000 | 17,446.05000000000000000000000000000000000 | ||

6.280000000000000000000 | 6.280000000000000000 | 17,452.33000000000000000000000000000000000 | ||

13,000.000000000000000000000 | 13,000.000000000000000000 | 30,452.33000000000000000000000000000000000 | ||

(30,000.000000000000000000000) | (30,000.000000000000000000) | 452.33000000000200000000000000000000000 |
||

3.120000000000000000000 | 3.120000000000000000 | 455.45000000000200000000000000000000000 | ||

8.380000000000000000000 | 8.380000000000000000 | 463.83000000000200000000000000000000000 | ||

(294.250000000000000000000) | (294.250000000000000000) | 169.58000000000200000000000000000000000 | ||

(169.580000000002000000000) | (169.580000000002000000) | (0.00000000000025579538487363600000000) |
||

- | (0.00000000000025579538487363600000000) |

Labels:

- Tags:
- small decimal amount

6 Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-09-2018 07:07 PM

SolutionHello,

this probably is caused by the floating point precision issue. Read up about that here:

Best Response confirmed by Grayson Le (Occasional Contributor)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-09-2018 07:14 PM

Hi Grayson

I'm also going in circles with your mysterious numbers so no answer from me I'm afraid.

Question though, could you just reduce to two decimal places rather than doing miniscule fractions of cent?

Just another observation for whatever it's worth, you haven't put the debits in the correct column as they are all in the credits column as well as some other strange things in your formula such as column B having a + in front (e.g. =+A6).

Anyway best of luck Grayson, hope it works out!

Cheers

Damien

Highlighted
##
##### Re: Really weird decimal amounts

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-09-2018 08:32 PM

Or watch the video with the ever eloquent Tom Scott.

"And at that point, they will have started to learn about floating point numbers and they will be starting to tear their hair out." OUCH!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2018 05:40 PM

Thank you all very much.

I got the explanation and solution. Perfect.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2018 06:25 PM

Hi Grayson

Great to hear. If you can mark the solution (unless you got the answer from somewhere else), then other forum users will know what the answer is!

All the best pal.

Cheers

Damien

Great to hear. If you can mark the solution (unless you got the answer from somewhere else), then other forum users will know what the answer is!

All the best pal.

Cheers

Damien

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2018 06:36 PM

Below is the solution (extracted from the blog, hopefully no copyright problem).

------------------------------------------

. . .

Let us go back to my very first example where my conditional formatting seemingly did not work. I know now that was due to the fact that the numbers I was using to calculate the absolute difference did not have exact binary equivalents. This resulted in 1.3240 –

There are two basic ways in which you can compensate for some of the errors due to floating point calculation. The first method is to use the **ROUND() function**. The ROUND() function can be used to round the numbers to the number of decimal places that is required in your calculations. For my *absolute difference* column, I only require 4 decimals of precision. So I change the formula in the *absolute difference *column from:

=ABS(A2-B2)

To:

=ROUND(ABS(A2-B2),4)

My conditional formatting rule works as expected now since 0.0049999999999999 has been rounded to 0.0050.

The second method to prevent rounding errors from affecting your work is by using the **Precision as displayed** option. This option forces the value of each number in the worksheet to be the displayed value. To turn on this option, follow these steps:

- Click Microsoft Office Button -> Excel Options -> Advanced
- In the
**When calculating this workbook**section, select the workbook you want, and then select the**Set precision as displayed**check box. - Click OK.

Going back to my absolute difference example, I set the number format to show four decimal places, and then I turn on *Precision as displayed* option. Since the display value is the actual value in the cell now, my conditional formatting works properly!

It is important to note that once the workbook is saved, all accuracy beyond four decimal places will be lost. This option affects the active workbook including all worksheets. **You cannot undo this option and recover the lost data so save your workbook prior to enabling this option. This option is generally not recommended unless you are sure more precision will not ever be needed for your situation.**

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft