Differences in Excel Vs C # Numbers

Question

Can anyone shed some light on why I can see very small (10 ^ -08) difference in numbers when using exactly the same numbers in Excel vs C # ??

I have a formula and use the same inputs. In Excel, I get a single number. In C #, I get something else. The difference is tiny.

I use doubles in C # and do the division. I tried using decimals that didn't matter much.

EDITOR: It makes me NUTS - I spent all morning on this - Any ideas?

Answer 1

The difference is that C # uses different MidPoint rounding rules and is superior.

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

Answer 2

With such small difference values ​​(10 ^ -08, you declare), I suspect that intermediate calculations are causing the problem. Note that the double precision values ​​are 64 bits, but the registers can work with an accuracy of 80 bits. So, if you have a code sequence in which the compiler will store all your intermediate calculations in registers, you will get better accuracy than if the same calculations were performed at different points in your code, forcing intermediate results to be held at 64 bits.

If you save the values ​​in Excel cells as part of your calculations, this will also reduce the intermediate calculations to 64 bits of accuracy.

You really need to show your code: show (a) Excel calculations (is it a worksheet formula or are you doing software assignments to cell values?) And (b) C # calculations that you do. If you do, then we can help you more accurately. But with the information that you have indicated so far, we can only make broad guesses.

- Mike

Answer 3

This section also made me dunk =).

A few years ago, I discovered that the Excel worksheet function =ROUND() gives different results than the VBA ROUND() function. I also found that VBA and MySQL version 4.x (I don’t remember the exact version, before 5.x) used the same algorithm to round the types of floating point numbers. They seemed to use the erroneous version of "rounding up a banker." MySQL eventually changed its rounding in future versions, but someone noted that the similarities between the way VBA and MySQL 4.x are implemented by ROUND are probably due to the fact that both of them rely on the way the C programming language (probably , used to create VBA and MySQL) implemented Round.

Both VBA and MySQL 4.x just realized that C is used to implement rounding. However, many modern programming languages ​​have decided to use their rounding methods for various reasons; sometime it will meet the IEEE specification and someday to meet what they think the user expects. In the case of VBA and MySQL 4.x, the incorrect version of banker rounding was never expected from the user (hence your disappointment), therefore, in future languages ​​new versions of rounding or provided alternatives are implemented (for example, the decimal type in C #) so that the user can produce the expected values ​​and have more control over the whole process.

You can try looking at this article for further clarification on this subject regarding VBA rounding: http://www.vb-helper.com/howto_round_to_specified_digits.html

Answer 4

Are you sure you see the whole number in Excel?

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

Now format this cell with a custom format number of “0.00000000000000000” and you will see 3.14159265358979000 (if the column is wide enough).

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

Now, just for fun, type "= PI () - 3.14159265358979" in the cell and you will see that you get zero. In some cases, such as adding or subtracting when the result is “almost zero”, Excel actually converts the result to 0.0 (this can drive you crazy if you don't know what is happening).