Calculation results differ from Excel 365

Aspose.Cells Product Family
1

Hi,

Using Aspose Cells for Java, version 25.7.

Sample code:

Workbook workbook = new Workbook("iferror.xlsx");
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.get("S1-8.2 - T.401082");
Cells cells = worksheet.getCells();
Cell e8 = cells.get("E8");
Cell f8 = cells.get("F8");
Cell g8 = cells.get("G8");
Cell h8 = cells.get("H8");

System.out.println("before recalc");
System.out.println("e8.getValue() = " + e8.getValue());
System.out.println("f8.getValue() = " + f8.getValue());
System.out.println("g8.getValue() = " + g8.getValue());
System.out.println("h8.getValue() = " + h8.getValue());
workbook.calculateFormula();

System.out.println("after recalc");
System.out.println("e8.getValue() = " + e8.getValue());
System.out.println("f8.getValue() = " + f8.getValue());
System.out.println("g8.getValue() = " + g8.getValue());
System.out.println("h8.getValue() = " + h8.getValue());

Resulting output:

e8.getValue() = 12
f8.getValue() = 12
g8.getValue() = Coverage rate 80 to 100%
h8.getValue() = 1
e8.getValue() = 12.0
f8.getValue() = 
g8.getValue() = 
h8.getValue() =

As you can see, the values calculated by Aspose Cells differ from those calculated by Excel 365.

Kind regards,
Taras
repro.zip (61.6 KB)

2

@TarasTielkes

Hi Taras,

It appears you are encountering discrepancies between the values calculated by Aspose.Cells and those in Excel 365. This issue can arise due to differences in calculation settings or formula handling between the two platforms.

Cause: The discrepancies may stem from how each platform interprets formulas or handles calculation options.

Solution: Here are some steps to troubleshoot and resolve the issue:

  1. Check Calculation Options: Ensure that the calculation options in Aspose.Cells are set correctly. You can specify options like CalculationOptions.IgnoreError or CalculationOptions.Recursive to see if they affect the results.

  2. Formula Parsing: If your formulas involve complex expressions or specific functions, consider using FormulaParseOptions along with CalculationOptions when calculating the formulas. This can help ensure that the formulas are interpreted correctly.

  3. Update Aspose.Cells: Make sure you are using the latest version of Aspose.Cells, as updates often include fixes for calculation discrepancies.

  4. Debugging: Print out the formulas being calculated and compare them with what Excel is using. This can help identify if there are any differences in the formulas themselves.

Here’s a modified version of your code that includes calculation options:

Workbook workbook = new Workbook("iferror.xlsx");
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.get("S1-8.2 - T.401082");
Cells cells = worksheet.getCells();
Cell e8 = cells.get("E8");
Cell f8 = cells.get("F8");
Cell g8 = cells.get("G8");
Cell h8 = cells.get("H8");

System.out.println("before recalc");
System.out.println("e8.getValue() = " + e8.getValue());
System.out.println("f8.getValue() = " + f8.getValue());
System.out.println("g8.getValue() = " + g8.getValue());
System.out.println("h8.getValue() = " + h8.getValue());

CalculationOptions options = new CalculationOptions();
options.IgnoreError = true; // Example option
workbook.calculateFormula(options);

System.out.println("after recalc");
System.out.println("e8.getValue() = " + e8.getValue());
System.out.println("f8.getValue() = " + f8.getValue());
System.out.println("g8.getValue() = " + g8.getValue());
System.out.println("h8.getValue() = " + h8.getValue());

Next Steps: Please try these steps and let me know if the issue persists. I am here to assist you further!

Best regards!

3

This seems to be a regression starting from 25.6.
When using Aspose Cells for Java 25.5, the output is closer to expectations.

4

@TarasTielkes,

Thanks for the template XLSX file.

After initial testing, I am able to reproduce the issue as you mentioned by using your template XLSX file and sample code snippet. I found Aspose.Cells formula calculation engine’s results differ from Excel 365.

We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-46449

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

5

Might be related to the fix you did for CELLSJAVA-46362

6

@TarasTielkes,

Thank you for providing the additional details. We have recorded it under the ticket (“CELLSJAVA-46449”) in our database. Kindly allow us some time to evaluate the issue thoroughly. We will keep you updated here as soon as we have further information.

7

@TarasTielkes,

This is to inform you that your issue (Ticket ID: “CELLSJAVA-46449”) has been resolved. The fix/enhancement will be included in an upcoming release (Aspose.Cells v25.8) that we plan to release either this weekend or during the next week of August 2025. You will be notified when the next version is released.

1 Like
8

Hi @amjad.sahi,

Thank you for the quick response, and for the quick fix :+1:

Out of curiosity, cell you tell us what the problematic formula pattern or part was that was causing the difference in calculation between Excel and Aspose Cells? Perhaps this is some formula construct that we can avoid in our workbooks, until we are able to upgrade to the new version.

Thanks again, and kind regards,
Taras

9

@TarasTielkes,

Let us take careful review and gather details on the specific formula(s) involving explicit array literals (if applicable) that may be causing the issue. We will get back to you and provide you with complete details soon.

1 Like
10

@TarasTielkes

The bug exists for not one special function or data, but for some common scenarios that use range of data. For the specific template provided in this thread, the issue was caused by the combination of SUMIF and IFS functions. We are afraid there is no general solution by changing the formulas as workaround for this issue. We plan to publish next official version 25.8 in next week, so hopefully you may get the fix soon.

11

The issues you have found earlier (filed as CELLSJAVA-46449) have been fixed in Aspose.Cells for Java 25.8.