Hi Jan Kok,
Hi Jan Kok,
Hi Barbar Raza,
It's unfortunately not fixed yet.
ROUND(534,185;2) ==> 534,18
but
ROUND(499,185;2) ==> 499,19
I hope you can find what's going wrong with the ROUND-functionality.
Kind regards,
Jan Kok
Hi Jan Kok,
Hi,
These issues are caused by .NET Framework.
1 - The previous two issues are caused by double precision. Please check “Floating-Point Values and Loss of Precision” on the doc: http://msdn.microsoft.com/en-us/library/system.double.aspx
For example : double x = 18750 * 0.6033;
The actual value of x is 11311.874999999998, the display string value(11311.875) is same as x.ToString(“R15”).
If we directly call Math.Round(x,2, MidpointRounding.AwayFromZero), it will return unexpected value. So we have to process double value before call this method.
2 - The latest issue is more strange, please try the following code:
double x = Math.Round(534.185, 2, MidpointRounding.AwayFromZero);
double y = Math.Round(499.185, 2, MidpointRounding.AwayFromZero);
The x value is 534.18, not 534.19.
We will look into it later.
Hi,
Hi Shakeel,
The first testresults are promising with Aspose.Cells version 6.0.0.2. Are you now using decimals instead of doubles?
We did some testing here and noticed that rounding with decimals works:
Math.Round(534.185m, 2, MidpointRounding.AwayFromZero)
==> 534.19
Math.Round(499.185m, 2, MidpointRounding.AwayFromZero);
==> 499.19
Kind regards,
Jan
Hi,
It’s great to know, results are promising now. However, I am not sure, if decimals or doubles are being used for rounding. I have therefore forwarded your comment to Aspose.Cells development team. We will update you asap.
Hi Jan,
You are right.
we used decimals instead of doubles if the double value less then decimal.MaxValue.
Hi Shakeel,
It's getting better, but unfortunately I found still a rounding problem. The value 0.415 is after rounding 0.41 instead of 0.42. This happens in one of our excel-sheets. I have tested this with version 6.0.0.0 and 6.0.0.2 of Aspose.Cells. The problem happens in version 6.0.0.2.
For reproduction you can use the excel-file attached and code below. What you see is that K32 (see variable valueK32) contains the number 0.410 instead of 0.420.
FileStream stream = File.Open(@"E:\rekenschemaGVW_2010v1_0310.xlsx", FileMode.Open);<span style="color: rgb(43, 145, 175);">Workbook</span> workbook = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">Workbook</span>(stream);
#region
Worksheet invoerWorksheet = workbook.Worksheets[“invoer”];
invoerWorksheet.Cells[“B2”].PutValue(20100101);invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B7"</span>].PutValue(1989); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B8"</span>].PutValue(1989); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B9"</span>].PutValue(1989); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B10"</span>].PutValue(1989); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B17"</span>].PutValue(1825); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B19"</span>].PutValue(1825); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B21"</span>].PutValue(<span style="color: rgb(163, 21, 21);">"m2"</span>); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B22"</span>].PutValue(1087); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B23"</span>].PutValue(19); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B25"</span>].PutValue(1); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B26"</span>].PutValue(1); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B27"</span>].PutValue(-25); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B28"</span>].PutValue(<span style="color: rgb(163, 21, 21);">"staal"</span>); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B29"</span>].PutValue(50); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B30"</span>].PutValue(30); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B31"</span>].PutValue(20); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B32"</span>].PutValue(27); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B33"</span>].PutValue(22); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B34"</span>].PutValue(17); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B35"</span>].PutValue(47); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B36"</span>].PutValue(27); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B37"</span>].PutValue(17); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B38"</span>].PutValue(27); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B39"</span>].PutValue(7); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B40"</span>].PutValue(5); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B41"</span>].PutValue(1); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B42"</span>].PutValue(1); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B43"</span>].PutValue(1); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B44"</span>].PutValue(543.5); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B45"</span>].PutValue(326.1); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B46"</span>].PutValue(217.4); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B48"</span>].PutValue(3000); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B50"</span>].PutValue(1); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B51"</span>].PutValue(1); invoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B52"</span>].PutValue(1); workbook.CalculateFormula(); <span style="color: rgb(43, 145, 175);">Worksheet</span> uitvoerWorksheet = workbook.Worksheets[<span style="color: rgb(163, 21, 21);">"uitvoer"</span>]; <span style="color: blue;">string</span> valueB23 = uitvoerWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"B23"</span>].StringValue; <span style="color: rgb(43, 145, 175);">Worksheet</span> rekenbladWorksheet = workbook.Worksheets[<span style="color: rgb(163, 21, 21);">"rekenblad"</span>]; <span style="color: blue;">string</span> valueT43 = rekenbladWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"T43"</span>].StringValue; <span style="color: blue;">string</span> valueS43 = rekenbladWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"S43"</span>].StringValue; <span style="color: blue;">string</span> valueK32 = rekenbladWorksheet.Cells[<span style="color: rgb(163, 21, 21);">"K32"</span>].StringValue;
#endregion
Please use the other reproduction tests in this thread for testing the other cases I submitted when fixing this problem.
Kind regards,
Jan Kok
Hi Amjad Sahi,
Thanks. I just attached the file to my post (316142).
Kind regards,
Jan Kok
Hi Jan Kok,
Hello Amjad Sahi,
Which version are you using? It works with version 6.0.0.0, but not with version 6.0.0.2 (see post 312713) which resolves a rounding issues from post 312149
Kind regards,
Jan Kok
Hi,
Please also test your issue with the current latest version and let us know your feedback. If some functionality is broken, we will fix it asap.
Download:
Aspose.Cells
for .NET v6.0.0.4
Hi,
I tested it with version 6.0.0.4. Same result as version 6.0.0.2.
Version 6.0.0.0 does work in this situation the same as Excel.
Kind regards,
Jan Kok
The issues you have found earlier (filed as 28399) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.
Hi Shakeel,
Do you have any idea what causes the rounding differences yet?
Kind regards,
Jan Kok
Hi,
I have forwarded your question to team and we will update you asap.
Will you please try the latest version though to see if it works fine now? Please download:
Aspose.Cells
for .NET v6.0.1.6
Hi,
Did you find any problem?
If yes, please send your sample project to us, we will check it soon.