Aspose Cell Formula not evaluating

Aspose.Cells Product Family
1

I am using aspose cell(22.1.0.0), I have multiple worksheets and those sheets are containing two types of formula 1st type of formula is depends on another sheet and these formulas are evaluating successfully after calling worksheet.calculateformula(), Second type of formulas are depends on the first one and formulas are not evaluating.

sample of 1st type formula:
=IFERROR(IF(ISBLANK(VLOOKUP($B9&F$8&$X$5,MFM.Analytic.Data!$A:$E,5,FALSE)),“–”,VLOOKUP($B9&F$8&$X$5,MFM.Analytic.Data!$A:$E,5,FALSE)),“–”)
value evaluating properly.
sample of 2nd type formula:
=IF(F9=“–”,“–”,IFNA(PERCENTRANK(G:G,F9,)*100,PERCENTRANK((G:G,F9),F9,)*100))
value is not evaluating showing #NUM!

2

@tamalacherjee

It seems that you are encountering an issue with the evaluation of formulas in Aspose.Cells, specifically with the second type of formula that depends on the first one. The first type of formula evaluates correctly, while the second type returns a #NUM! error.

Here are a few steps you can take to troubleshoot and potentially resolve this issue:

  1. Check Dependencies: Ensure that the second type of formula is correctly referencing the cells that the first type of formula is calculating. If the first formula is not evaluated before the second one, it may lead to errors.

  2. Enable Calculation Chain: Make sure that the calculation chain is enabled in your workbook settings. This can be done by setting EnableCalculationChain to true. This ensures that dependent formulas are recalculated when their precedents change.

    Here’s a code snippet to enable the calculation chain:

    Workbook workbook = new Workbook("path_to_your_file.xlsx");
workbook.getSettings().getFormulaSettings().setEnableCalculationChain(true);
workbook.calculateFormula();

  3. Recalculate Formulas: After making changes to the cells that the first formula depends on, ensure that you recalculate the formulas in the workbook. You can call workbook.calculateFormula() again after updating any dependent values.

  4. Formula Syntax: Double-check the syntax of your second type of formula. The use of PERCENTRANK should be verified to ensure it is being used correctly. The formula should not have any syntax errors that could lead to evaluation issues.

  5. Debugging: If the issue persists, consider simplifying the second formula to isolate the problem. Start with a basic version of the formula and gradually add complexity to identify what might be causing the #NUM! error.

If you continue to experience issues, please provide more details about the specific values in the cells that the formulas are referencing, as this may help in diagnosing the problem further.

Feel free to reach out if you have any more questions or need further assistance!

Sources:
[1]: Data Analysis Excel Formulas | Aspose.Cells Java Excel Processing API
[2]: CalculatingFormulas.java

3

@tamalacherjee
Please provide a compressed test file for further analysis.Alternatively, update to the latest version for testing.
Thanks!

4

Please have a look on sheet “Control.of.corruption”
sample_file.zip (1.3 MB)

Thanks,
Tamal

5

@tamalacherjee,

Thanks for the template Excel file.

After initial testing, I was able to reproduce the issue as you mentioned by using your sample Excel file and following code snippet. I found formulas in Control.of.Corruption worksheet are not calculated fine. I also found your Excel file also contains external sources/links.

Workbook workbook = new Workbook("e:\\test2\\sample_file.xlsx");
workbook.CalculateFormula();
workbook.Save("e:\\test2\\out1.pdf");

We need to evaluate your issue in details. 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): CELLSNET-57131

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.