excel formula not returns to right value

**Zaid3010** · 04-20-2024, 11:43 PM

Hello Guys

I have a problem with my Excel. I just recognize this error a few days ago

When I do the calculation by using the formula, the answer is not return to the right value. Please ref to below example

I made simple calculation

100+0.3 with simple formula and the answer is 100.3 ...when I paste as value the answer is 100.30
However when I do the reverse.. 100.30-100.00, the answer become 0.299999999999997. Why its not return to 0.3?

100.0000000000000000 0.3000000000000000
100.3000000000000000 0.2999999999999970

**AliGW** · 04-21-2024, 06:23 AM

Google: Microsoft Excel floating point error and read all about it.

Try this:

=ROUND(C6-C5,1)

**Zaid3010** · 04-22-2024, 02:29 PM

Thank You AliGW

when I search at google.. I found how to resolve my problem

"How do I fix floating point error in Excel?
On the File tab, click Options, Advanced, scroll down to 'When calculating this workbook', and check 'Set precision as displayed'. This feature forces numbers to be as precise as they appear on your worksheet."

**Zaid3010** · 04-22-2024, 02:30 PM

Originally Posted by AliGW

Google: Microsoft Excel floating point error and read all about it.

Try this:

=ROUND(C6-C5,1)

Thank You AliGW

when I search at google.. I found how to resolve my problem

"How do I fix floating point error in Excel?
On the File tab, click Options, Advanced, scroll down to 'When calculating this workbook', and check 'Set precision as displayed'. This feature forces numbers to be as precise as they appear on your worksheet."

**MrShorty** · 04-22-2024, 02:38 PM

Setting precision as displayed is usually not the best solution for floating point errors. A better solution is to use ROUND() functions or other programming strategies for dealing with floating point errors.

**TMS** · 04-22-2024, 02:51 PM

Probably not a good idea.

j.e. mcgimpsey
January 8, 2012 at 3:35 pm | #
Your example demonstrates a common fundamental misunderstanding of Excel – changing the displayed number of digits doesn’t do anything to the stored value which is used in calculations.

Setting Precision as Displayed is usually a REALLY bad idea – since XL will actually change the values you enter, unless you set up your workbook very carefully, you’ll never know where the precision was lost. You’re usually much better off using the ROUND() function, e.g.: =ROUND(A1,2) + ROUND(A2, 2), which at least hints at where potential inaccuracies may creep in. Your hypothetical accountant will never be able to find out why he’s one off if the base data is permanently lost.

See: https://www.dustinwheelercpa.com/201...played-option/