Formula Calculation Problem

Aspose.Cells Product Family
1

I’m getting the formula calculation error experienced with other users.

Basically, generating a sheet with the following cells:
A1 = 1
B1 = =IF(ISERR(VALUE(A1)),“Warning - Field needs to be numeric”,IF(VALUE(A1)<1,“Warning - Value cannot be below 1”,""))

causes the formula error to show #NAME? when loaded in Excel. However, pressing F2 on the field and enter to save the formula (without changes) causes it to be accepted OK.

I’ve tried calling workbook.CalculateFormula() before saving the sheet but Aspose throws an Exception.

We seem to be using quite an old version (Aspose.Excel - v3.6.1.0). Is there a workaround or version upgrade we can get that fixes this problem without moving to the rebranded Aspose.Cells ??

Thanks,

2

Hi,

Thanks for considering Aspose.

Well, since you are using some older version which might show this error, so, I 'm afraid you have to upgrade to the latest Aspose.Cells. The new Aspose.Cells is more reliable, stable and prosperous with enhanced and advanced functionality. Aspose.Cells's new calculation engine supports a variety of fomulas / functions for your need. I think there is not much labour involved upgrading to the newer Aspose.Cells. However, when you upgrade to the newer Aspose.Cells,

Please note:

Some APIs (classes, struct, enumerations) are added based on some advanced features that we now offer, the previous classes (with their members) are retained and optimised but a few classes are renamed, some additional members (properties, methods etc.) are also included of the classes with a very few excluded.

Following is the classes list which are renamed:

1. Excel class is replaced by Workbook class.

2. ExcelDesigner class is replaced by WorkbookDesigner class.

3. ExcelHelper class is replaced by CellsHelper class.

For more info, please check the Aspose.Cells Documentation with APIs: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/index.html

Hope, it will help you. You may use the latest version i.e. 4.4.2.0 downloading @: http://www.aspose.com/community/files/51/file-format-components/aspose.cells/entry121168.aspx

Moreover, I have tested your formula using the following code and it works fine.

Sample code:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Aspose.Cells.Cell cell = worksheet.Cells[0, 0];
cell.PutValue(1);
cell = worksheet.Cells[0, 1];
cell.Formula = "=IF(ISERR(VALUE(A1)),\"Warning - Field needs to be numeric\",IF(VALUE(A1)<1,\"Warning - Value cannot be below 1\",\"\"))";
workbook.CalculateFormula();
MessageBox.Show(cell.StringValue);
workbook.Save("d:\\test\\new_outcalcBook.xls");

Thank you.

3

Alternatively, use ISERROR instead of ISERR - formula now works OK !!

We will still upgrade though…

Thanks.