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

[Mrmoe]

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.

 

[rlv01]

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)