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.

9

@amjad.sahi, do you have any updates yet? Is there any way this ticket can be escalated to high priority support?

10

@daniellabianca,

Your issue is “In Progress”, which means we are working on it. Once we have an update, we will let you know here. We cannot commit to an ETA at the moment, as issues are fixed on a first-come, first-served basis in normal support.

If the issue is real blocker for you, 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.

11

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

12

I am noticing that calling calculateFormula() is more than 10x slower for a large spreadsheet (haven’t tried others) after switching from 24.5 to 24.6. Is this a known consequence of this fix? The spreadsheet is proprietary so I can’t share it here, so before I try to create an example to reproduce it and submit a new ticket, I wanted to check first if this performance drop is expected with 24.6.

FWIW, I posted a profiler image that shows where most of the time is spent (this is with 24.6)
image.png (43.4 KB)

Here is with 24.5
image.png (47.6 KB)

13

@twadzins
Yes, the fix requires more complicated logics and checks for calculating array formulas. It make the time cost increased when comparing with old versions. Anyways, if you can provide us the template file to reproduce the issue, we will check it thoroughly to confirm whether it is influenced by the extra logics and checks only, and we will investigate whether we can improve the performance for it.

14

@twadzins,

Moreover, if you are unable to share your original Excel file due to proprietary reasons, we suggest replacing your confidential data with some dummy data, re-saving the file, and providing it to us. We will check your issue soon.

15

Thanks! Are you seeing a similar 10x slowdown with 24.6 compared with 24.5 on your test spreadsheets?

16

Thanks, however, it’s more that the formulas in the spreadsheet are considered proprietary, so I’ll look at some other way to reproduce the slowdown between 24.6 and 24.5 in a sample spreadhseet

17

Yes, the fix requires more complicated logics and checks for calculating array formulas.

Does that change anything in terms of the refreshDynamicArrayFormulas call? Our spreadhseet doesn’t use dynamic array formulas (at least I am pretty sure it does not, since we seem to get correct results without calling refreshDynamicArrayFormulas compared with actual Excel), so we aren’t calling refreshDynamicArrayFormulas at all so we can save time. Performance is important for this since we are sending many different sets of inputs into the open spreadsheet and fetching results after.

18

@twadzins,

Generally, we did not find a significant performance slowdown, such as 10 times, for formula calculations between the two versions based on our test cases. However, if possible, could you please try the latest version/fix, i.e., Aspose.Cells v24.8, to see if it makes any difference.

We are grateful for your efforts in creating a use case to share with us, which will enable us to replicate the issue on our end.

Certainly, the array formula calculations have been improved in recent versions. Now, we can examine other parts and options.

19

Using 24.8 did not make any difference in terms of speeding up compared with 24.6

20

@twadzins,

Alright, thank you for the confirmation. Please create and work on some test cases to be shared so we can replicate the issue on our end. This will allow us to evaluate the root cause of the issue precisely.