The value is the result of dividing the sum of 2.01 and 2.52 by 2 (2.01 + 2.52) / 2. Excel displays this value as 2.265 and when formatted to 2 digits, it's 2.27. However, the value stored in the file is 2.2649999999999997. When I recreate this is C#, I also get that value in my variable, not 2.265. I understand this is due to floating point precision issues with the division of 4.53 by 2.
double result = (2.01 + 2.52) / 2;
Console.WriteLine(result);
The Console displays 2.265 but the value shown in the QuickWatch debugger shows 2.264999999999997. I suspect the conversion of the value to a string on the in the WriteLine method is correcting for the floating point precision error.
When I apply Math.Round(result, 2, MidpointRounding.AwayFromZero), the result comes back as 2.26 not 2.27 as I expected. It seems like it looks at the first number to the right of the digit I want rounded, sees it's a 4 and ignores everything else to the right of it. The problem is that those 9's are only there because of the precision problem and need to be included, or better yet, the value should be 2.265.
What I have done in my code, is to read the text value from the Excel spreadsheet "2.2649999999999997", convert that to a double 2.264999999999999 and then to a string, which gives me "2.265". I then convert that back to a double 2.265 so that I can apply the Math.Round to it and get the expected result of 2.27. Here's the full code:
double result = Convert.ToDouble(((2.01 + 2.52) / 2).ToString());
Console.WriteLine(Math.Round(result, 2, MidpointRounding.AwayFromZero));
Is my approach for floating point precision and rounding issues, relying on ToString to clean it up, the correct one? If not, how should I have done it?
First: The problem is hard. Because 4.53/2 = 2.265. This rounds to 2.27. However the tiniest rounding error in the calculation resulting in a smaller result (2.264999999....) will lead to a rounding to 2.26. This is what is happening here. To solve this problem you need to have a floating point arithmetic which has the same internal rounding errors as Excel does.
From this document https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel it appears as if Excel uses a modified version of IEEE 754, while C# uses IEEE 754. I do not know where the differences are, but it appears as if internally Excel generates different rounding errors.
This document describes the differences: https://support.microsoft.com/en-us/kb/78113/en-us (For example, Excel does not use denormalized numbers. This implies a different behavior for rounding errors for numbers < 2).
So I assume you cannot solve this using "double"
Update
However, now that I understand that the issue is not the arithmetic, but the way Excel displays the number, maybe this is a solution
Math.Round(Math.Round(result, 3, MidpointRounding.AwayFromZero), 2, MidpointRounding.AwayFromZero)
First round to 3, then to 2 digits. It appears to me that Excel is doing this.