Formulas are not fully calculated in large workbook unless it is opened in Excel

Aspose.Cells Product Family
1

Hi, my company is using Cells for Java v23.9 to run calculations on a rather large workbook set for Automatic calculation. We map user inputs to an Inputs tab, run workbook.CalculateFormula, then map the calculated outputs to our API response.

The workbook.CalculateFormula method appears to only do a partial calculation. When we open the workbook, it shows the initial calculation, then automatically calculates some additional formulas, but we have to force full calculation or manually touch an uncalculated cell in order to get the final full calculated values.

Whereas when we manually add the user inputs to the workbook, the full calculations are done automatically with no additional calculations needed.

Our issue is that we need the fully calculated values to map with and are unable to obtain them programmatically. I have tried testing with multiple variations of CalculationOptions and FormulaSettings as well as trying v24.4. None of these variations have produced the results we are looking for. However, when we programmatically force full calculation, we are able to get the first and second calculated values, but still not the fully calculated values.

The calculations missing from the first and second round of Calculate are formulas using SUMPRODUCT, so I am wondering if there is something within those formulas that Aspose is having an issue with? This is an example of one of the of formulas: =(SUMPRODUCT(XLOOKUP('LDF Tables'!$E$4:$H$4,$E$5,$I$5,0),XLOOKUP('LDF Tables'!$E$5:$H$5,$I$9:$J$9,$I$10:$J$10,0),'LDF Tables'!$E10:$H10)+SUMPRODUCT(XLOOKUP('LDF Tables'!$E$4:$H$4,$E$6,$I$6,0),XLOOKUP('LDF Tables'!$E$5:$H$5,$I$9:$J$9,$I$10:$J$10,0),'LDF Tables'!$E10:$H10))

I am obtaining a copy of the workbook that I can share with you, but thought I’d ask about the above formula in the meantime.

Any help is much appreciated!

2

@daniellabianca,

Could you please try calling workbook.RefreshDynamicArrayFormulas(true) before using workbook.CalculateFormula() in code if it makes any difference? In case, you still find the issue, kindly zip and attach your template Excel file, we will check your issue soon.

3

@amjad.sahi, thank you for the quick response. I tried calling workbook.RefreshDynamicArrayFormulas(true) before workbook.CalculateFormula(), but there was no difference in the output.

I am attaching an extract of the calculator with the formulas we are having this issue with. Even at this reduced scale, the calculation is not complete.

In the Acct Inc LDFs tab, set the value of E10 to “600000”. When you calculate in Excel, the value of C16 is “9325106.245”. When we calculate with Aspose, the value of C16 is “3251062.448”

LookupExample.zip (29.8 KB)

4

@daniellabianca,

Thanks for the template file.

After an initial test, I was able to reproduce the issue as you mentioned by using your template Excel file and following sample code. I found formulas are not fully calculated correctly.

Workbook workbook = new Workbook("e:\\test2\\LookupExample.xlsx");
Worksheet worksheet = workbook.getWorksheets().get("Acct Inc LDFs");
worksheet.getCells().get("E10").putValue(600000);
workbook.refreshDynamicArrayFormulas(true);
workbook.calculateFormula();
System.out.println(worksheet.getCells().get("C16").getDoubleValue());//3251062.4474999993

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-45971

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.

1 Like
5

@amjad.sahi, thank you for creating a ticket. I will wait to hear back from your team on an update. I noticed the Issue ID is for .NET, and wanted to note that we are using Cells for Java, in case that affects your testing.

6

@daniellabianca,

Thank you for providing further details.

We assumed that you are using Aspose.Cells for .NET. Anyways, we did test your scenario using Aspose.Cells for Java with your template Excel file and reproduced the same issue. We have corrected and changed the ticket type to Java in the database (I have also updated my previous reply accordingly). We will look into your issue soon. Once we have new updates or figure it out, we will let you know.

7

@amjad.sahi , I wanted to follow up to see if this issue had been looked at yet. Thanks!

8

@daniellabianca,

Your issue is still “open” and has not been resolved yet. Your ticket was logged under normal support, which will be handled on a first-come, first-serve basis as per the support policies. Hopefully, we could schedule it soon. As soon as the ticket analysis is complete, we will share updates with you.

We are sorry for the inconvenience caused to you.