Unsupported function in formula calculation engine - code 83

Aspose.Cells Product Family
1

I am using the Workbook.CalculateFormula() method. It is throwing this exception:
Unsupported function in formula calculation engine - code 83. Error in calculating cell D8 in Worksheet xxx.

The formula in D8 is as follows:

=IF($B$8>0,(100*(SUMIF($CH15:$CH1000,1,$D15:$D1000)/$B$8)),0)

Both the IF and the SUMIF are supported. Why can't it calculate this formula?

Thanks.

2

Hi,

Well, I have tested with the attached version using your formula and it works fine.

Please try the attached version. If you still find the issue, kindly post your template excel file here, we will check your issue soon.

Thank you.

3

I'm sorry to say that that did not fix the problem.

I've attached a template file that shows the problem.


Thank you.

4

Hi,

Well, I can see one unsupported function i.e…, “TRANSPOSE” in Cell C15 in Aspose.Cells for .NET Formula calculation engine for calculations although you may set/read the formula (there might be some others too.). If you could check, Cell D8 (in worksheet “Data_HR-G-Ad-1”) formula depends on some cells including Cell B8 and D10:D1000 etc. The Cell D15 also depends on the formula in C15 cell and this cell (C15) has “TRANSPOSE” function.

For complete list of supported functions/formulas, see the document: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/supported-formulas-functions.html


We will further investigate if there are a few other formulas if these are not supported and we will try to support (if the formulas are found and feasible enough for support). We will get back to you soon. Your issue has been added to our internal issue tracking system with an issue id: CELLSNET-11476.

Thank you.

5

Thank you for your quick response.

I think, though, that I am going to encounter another problem even after the formulas are supported. I just read in another forum posting that, while you can calculate formulas using the Workbook.CalculateFormula method, those calculations do not get saved when you save the workbook. I need to have the calculations saved because the next use of my workbook is to use another application to access the results. It does not use Aspose or Excel, and the formulas cannot be calculated by this other application. It relies on the underlying data in the Excel workbook to contain the already-calculated values.

Is there another method I can use to tell Aspose to calculate the formulas and then save those results?

6

Hi,

Thank you for considering Aspose.

Well, your understanding is not right regarding Workbook.CalculateFormula(). When you use Workbook.CalculateFormula() method, it will update the formula results at runtime and also the results will get saved when you will save the workbook. So, I think Workbook.CalculateFormula() will meet your need after the formulas get supported.

Thank You & Best Regards,

7

Well, that's great!

Here's where I got the idea that it did not save the results:

The posting that I got that information from was in 2006. So, I guess that's just outdated. The posting said:

"CalculateFormula method only provides a way to retrieve data correctly at run time but it doesn't save the formula result into Excel file."

8

Hi,

Please try the attached version, we have supported the unsupported formula(s) in your workbook. I have tested using Workbook.CalculateFormula() method with your template file, it works fine now.

Thank you.

9

Excellent! It works perfectly. Thank you, again, for your quick response.