Hi, we’ve encounterd an issue in the calculation of the attached excel using version 16.11.0 and 17.1.8.
The error is highlighted for example in red at cell ConsolidationRiskGroups!c4
Can you verify?
It’s not the first error of this type that we suffer, and it’s difficult to verify all produced reports.
Thanks in advance.
Regards
Hi there,
Thank you for sharing the sample. I have evaluated the presented scenario while using the latest version of Aspose.Cells for .NET 17.2.0 and the following piece of code. I have noticed that the value of cell ConsolidationRiskGroups!c4 in Excel is 27,008,111.57 but when read via Aspose.Cells, the value is 25,805,084.57. I have raised this incident as CELLSNET-45144 in our bug tracking system. Please spare us little time to properly analyze the case and revert back with updates in this regard.
C#
var book = new Workbook(dir + “Rep+S.14.01+Life+Obligations+Analysis_FY16+Local_AVIV_Manual_error.xlsm”);
book.CalculateFormula(new CalculationOptions() { PrecisionStrategy = CalculationPrecisionStrategy.Decimal});
var cells = book.Worksheets[“ConsolidationRiskGroups”].Cells;
Console.WriteLine(cells[“C4”].Value);
Hi, I advice you that with version 8.2.3.1 the error is not present. Can this help you?
Regards Silvano
Hi Silvano,
Thank you for the additional information. I have checked the releases from Aspose download section, and I was not able to find the release Aspose.Cells for .NET 8.2.3.x. In fact, Aspose.Cells was upgraded from 8.2.2.0 to 8.3.3.0 with no releases in between. Could you please recheck the release version that used to produce correct results? If possible, please share the assembly in an archive for further testing.
Hi, sorry I mistake to write. The version is 8.3.2.1
Regards
Hi, I think you are done some confusion.
The correct result caculated by Excel is 25805084,58
The wrong result calculated by Aspose.Cells is 27008111,57
Regards Silvano
Hi Silvano,
I am afraid, I am seeing the value of cell ConsolidationRiskGroups!c4 as 27,008,111.57 in Excel 2010 & 2016, and I have logged the incident based on these observations. After reading your recent post, I have checked the results in Excel 2007 as well and found that the value of same cell in Excel 2007 is 25805084.58 (what Aspose.Cells is returning). I have recorded these observations to the aforementioned ticket and have requested the concerned member of the product team to consider these findings as well as your recent comments during the investigation. In the meanwhile, could you please share the version of Excel application installed on your side along with the locale of your machine?
Note: I have attached screenshots from different versions of Excel application here for your reference.
Hi, in locale we have Excel 2016 and Excel 2010.
Regards Silvano
Hi again,
Thank you for sharing the Excel versions. Could you please also share the Format set on your machine. It is accessible via Region dialog from system’s control panel? I have attached the snapshot from my machine for reference.
Hi, in image attached is my setup.
But, why this question? Excel can calculate different results if settings are different?
Hi again,
Please note, change of machine’s region/locale can only affect number formats (group & decimal separators), not the calculated results of any formula. In this case, I am not able to see the results as you have mentioned here so I am trying to mimic the exact circumstances, however, the results are still same on my end even though I have changed the format of my machine to Italian. I will check back with the product team in this regard, and share their findings as soon as possible. In the meanwhile, if you can, please share a screenshot of the sample spreadsheet loaded in Excel on your side after downloading the sample spreadsheet from this thread. This is just to confirm that we are operating on the same sample.
Thank you for your continues support in isolating the problem cause for this scenario.
Hi, I’ve download the excel attached in thist thread as you ask and post two screenshot. One in red with the error, and one in green with correct calc done by Excel.
Regards
Hi again,
Thank you for sharing the screenshots. Let me get in touch with the product team to know their findings about this incident. In case the product team requires any further information, I will let you know here. Please note, the ticket attached to this thread is currently pending for analysis and is in the queue with other priority tasks, however, I have already requested the concerned member of the product team to schedule the analysis at earliest possible, and share the preliminary analysis results for your reference.
Hi Silvano,
This is to update you that we have looked further into the matter logged earlier as CELLSNET-45144. Please note, the value 27,008,111.57 is stored in the spreadsheet. As soon as you recalculate the formulas (in Excel) the value changes to 25,805,084.58. Please note, you have to click on the “Calculate Now” button on Excel interface so that all formulas are calculated again. Same is the behavior of Aspose.Cells APIs, that is; if you fetch the value of the cell after calculating the formulas, it is 25,805,084.58 against the latest revision of the API. As the said value matches with Excel (after calculation) therefore the presented behavior is correct, and is not a bug on the part of Aspose.Cells APIs.
Could you please test the case against latest version of Aspose.Cells for .NET 17.2.0 and following code?
C#
Console.WriteLine(CellsHelper.GetVersion());
var book = new Workbook(dir + “Rep+S.14.01+Life+Obligations+Analysis_FY16+Local_AVIV_Manual_error.xlsm”);
book.CalculateFormula();
var cells = book.Worksheets[“ConsolidationRiskGroups”].Cells;
Console.WriteLine(cells[“C4”].Value);
Hi again,
Adding more to my previous response, under some scenarios the regional settings can influence the formula calculation, such as: the formulas use formatting related functions such as TEXT or the formulas need to parse numeric value from string DateTime and so on. However, for this template file, we do not find such kind of functions.
Hi, I’ve tried the new version with not a deterministic result.
With the file previously attached to you, that was generated through the previous version of Aspose but opened in manual mode with excel to color the cell with error and saved it, with the new version 17.2 is ok.
If I recalculate formula with version 17.2 on the file directly generated from previous version, no change is done. Can you verify this strange problem?
I attach the file.
Regards
Hi again,
Thank you for sharing the fresh sample. I have reevaluated the case against the newly shared sample and latest version of Aspose.Cells for .NET 17.02.1, and I am able to notice the problem, that is; Excel shows the result for problem cell as 25,805,084.58 after clicking “Calculate Now” button but when read via Aspose.Cells, the value is 27,008,111.57. I have updated the details of previously logged ticket CELLSNET-45144, and have requested the concerned member of the product team to reschedule the analysis at earliest possible. As soon as we get further updates, we will post here for your kind reference.
Hi again,
This is to inform you that the ticket logged earlier as CELLSNET-45144 has been marked resolved. We will shortly share the fix here for your testing.
Hi,
Thanks for using Aspose.Cells.
Please download and try the following latest fix
and let us know your feedback.