Calculation error, round and set precision as displayed are no help

Mrmoe

New Member
Joined
Jan 12, 2019
Messages
1
I have created an excel file which is used to perform a simple calculation. The values will vary depending on the types of category data - there are 12 in total. I have been working my way through to verify the excel calculations of each group using a manual calculator. I have found an issue at the 10th of 12 unfortunately.

The data/calculation is as follows
A1 B1 C1 D1
[TABLE="width: 467"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: right"]19.0000[/TD]
[TD="class: xl63, width: 64, align: right"]2.0000[/TD]
[TD="class: xl63, width: 64, align: right"]19.1190[/TD]
[TD="class: xl64, width: 275, align: right"]5.94999999999999000000[/TD]
[/TR]
</tbody>[/TABLE]

D1 contains the following formula =((C1-A1)/B1)*100. I need to display the result to one decimal place. The correct result is 5.95 which when rounded to one decimal place gives 6.0. However as the excel calculated result is 5.94999999999999000000 it can never round it to 6.0 which is the correct result. Instead it will display 5.9 even if I use the round function or if I click set precision as displayed option and format the cell to one decimal place. I'm not sure what to do or if I have missed something really simple. Any help appreciated.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I think you are getting down in the weeds in to the realm of discussing how excel handles IEEE Standard for Double Precision Floating Point numbers. Not super elegant, but how about using an expedient hack similar to this?

=ROUND(ROUND(D1,4),1)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top