Sum rounding errors

[KParkerBIUK]

Hello.
Using the sum function on the sheet, I get odd rounding errors while adding small values.
Integers seem to be fine, but if I use small values of 0.2 etc I randomly get incorrect results.
This can be seen on the example page:
1
1
0.2
0.1
Sum: 2.3000000000000003

I created a number of sum formula's dynamically, so it would be annoying to add the values into the code.
Also how do you completely remove all of the context menus, I could not get the ContextMenuEnabled="false" to validate.

Kevin,

PS, Thank you for providing such an excellent package.

[anmcgrath]

Hey @KParkerBIUK

The rounding errors are due to floating point math not being perfect. Excel does have some of the same issues although it seems to handle it better.

In Excel if you have the cell A1 with a formula =SUM(1, 1, 0.2 ,0.1) and A2 with the formula =(4.6/2 - A1) = 0 you will get FALSE due to the rounding errors (at least in the version I have access to).

Having said that it would be nice if you could do the sum in your example and it would display as 2.3 rather than all the decimal places.

Looks like Excel has a max number of digits of 15 and the general format will be default show 9 digits? So we could implement something like that here.

---

With your second question, this works for me:

<Datasheet
    Sheet="_sheet"
    MenuOptions="new SheetMenuOptions(){ ContextMenuEnabled=false }"/>

[KParkerBIUK]

Hello,

Thank you for the feedback,
I understand the rounding errors, although It would be nice to hide these from the user,
the excel round function =Round(value,2) would be nice, or even a way to limit the number of digits shown in the number format as you mentioned.

Thank you for the Menu options, somehow missed the need to add new..

Kevin

[anmcgrath]

@KParkerBIUK I've added a parameter in #154 that controls the rounding applied to numbers in the sheet. It's set to 15 by default. You can see in my example site that this fixes the display issues you had, although the number is still stored with the floating point errors. In the future it might be useful to also round the errors when editing the value.