Excel Vs C# Number Differences

Question

Can anyone shed light on why I might be seeing very small (10^-08) number differences when using exactly the same numbers in Excel vs C#??

I have a formula and use the same inputs. In Excel I get one number - In C# I get another. The difference is tiny.

I am using doubles in C# and doing division. I have tried using decimals which did not make much of a difference

EDIT: This is driving me NUTS - I ahve spent all morning on this - Any ideas??

Answer 1

The difference is because of different MidPoint Rounding rules in c# and excel.

Try Math.Round(someNumber,precision,MidpointRounding.AwayFromZero);

Answer 2

With such small magnitudes of difference (10^-08, you state), I suspect that intermediate calculations are causing the problem. Note that double-precision values are 64 bit, but the registers can work with 80 bits of precision. So, if you have a code sequence where the compiler will keep all your intermediate calculations on the registers, you will actually get better precision than if the same calculations are made across different points in your code, forcing the intermediate results to be held in 64 bit storage locations.

If you store values within Excel cells as part of your calculations, this, too, will result in truncating your intermediate calculations to 64 bits of precision.

You really need to show your code: show both (a) the Excel calculations (is it a worksheet formula or are you making programmatic assignments to the cell values?) and (b) the C# calculations that you are making. If you do that, then we should be able to help you more precisely. But with the information you've given so far, we can only make broad guesses.

-- Mike

Answer 3

This topic has driven me nuts before as well =).

A few years ago, I discovered that Excel's worksheet =ROUND() function produced different results than VBA's ROUND() function. I also discovered that VBA and MySQL version 4.x (can't remember the exact version; before 5.x) used the exact same algorithm to round floating-point number types. They seemed to be using a flawed version of "banker's rounding." MySQL eventually changed their rounding in future versions, but someone pointed out that the similarity between the way that VBA and MySQL 4.x implemented ROUND was likely due to their both relying on the way that the C programming language (likely used to create VBA and MySQL) implemented Round.

Both VBA and MySQL 4.x simply implemented what C used to implement rounding. However, many contemporary programming languages have chosen to roll their own rounding methods for various reasons; sometime's it to be in accordance with the IEEE spec and sometime it's to match what they think the user will expect. In the case of VBA and MySQL 4.x, a flawed version of banker's rounding was never expected by the user (hence your frustration), so future languages implemented new versions of rounding or provided alternatives (such as the decimal type in C#) so that the user would be able to produce expected values and have more control over the whole process.

You might try looking at this article for further elucidation on the subject in reference to VBA's rounding: http://www.vb-helper.com/howto_round_to_specified_digits.html

Answer 4

Are you sure you are seeing the entire number in Excel?

Numbers formatted as "General" will be displayed with ~12 digits of precision (or fewer if the column is not wide enough for ~12 digits). For example, if you put the formula "=PI()" into a cell with the "General" format (the default in Excel) and make the column wide enough, you will see 3.141592654. Note that if the column is not wide enough, you will see even fewer digits of precision.

Now, format that cell with the custom number format "0.00000000000000000" and you will see 3.14159265358979000 (if the column is wide enough).

Note that Excel actually stores the value of =PI() internally with more than 15 digits of precision. You can see this by entering "=(PI()-3.14159265358979)" into a cell - be sure to include the parenthesis in the formula.

Now, just for fun, enter "=PI()-3.14159265358979" into a cell and you will see that you get zero. In some cases, such as adding or subtracting where the result is "almost zero", Excel will actually convert the result to 0.0 (this can drive you crazy if you don't know it is happening).