#NUM! error for long formulas

Hi

I’m getting #NUM! excel errors when creating very long formulas. For example,

=AD14+AI14+AN14+AS14+AX14+BC14+BH14+BM14+BR14+BW14+CB14+CG14+CL14+CQ14+CV14+DA14+DF14+DK14+DP14+DU14+DZ14+EE14+EJ14+EO14+ET14+EY14+FD14+FI14+FN14+FS14+FX14+GC14+GH14+GM14+GR14+GW14+HG14+HL14+HV14+IA14+IF14

would give me an error once the spreadsheet has been generated. However, if I hit F2 in cell with the #NUM! error afterwards, the error is gone and everything is fine.

I’m currently using 1.6.2 and I’m not sure if this has been fixed for later versions. This version is currently in production so I would prefer not to upgrade the production version unless it is absolutely necessary.

Thanks,

Steve

Hi Steve,

I will fix this problem ASAP. Thanks for your patience.

@Steve,
Aspose.Excel is deprecated now and no more development work is done. It is replaced with highly efficient and feature-rich product Aspose.Cells which supports all the old and latest features available in MS Excel. All the latest features related to the usage of functions are supported as shown in the code snippet below:

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();

// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];

// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);

// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);

// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
            
// Adding a value to B1
worksheet.Cells["B1"].PutValue(4);

// Adding a value to "B2" cell
worksheet.Cells["B2"].PutValue(5);

// Adding a value to "B3" cell
worksheet.Cells["B3"].PutValue(6);

// Adding a value to C1
worksheet.Cells["C1"].PutValue(7);

// Adding a value to "C2" cell
worksheet.Cells["C2"].PutValue(8);

// Adding a value to "C3" cell
worksheet.Cells["C3"].PutValue(9);

// Adding a SUM formula to "A4" cell
worksheet.Cells["A6"].SetArrayFormula("=LINEST(A1:A3,B1:C3,TRUE,TRUE)", 5, 3);

// Calculating the results of formulas
workbook.CalculateFormula();

// Get the calculated value of the cell
string value = worksheet.Cells["A6"].Value.ToString();

// Saving the Excel file
workbook.Save(dataDir + "output.xls");// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();

// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];

// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);

// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);

// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
            
// Adding a value to B1
worksheet.Cells["B1"].PutValue(4);

// Adding a value to "B2" cell
worksheet.Cells["B2"].PutValue(5);

// Adding a value to "B3" cell
worksheet.Cells["B3"].PutValue(6);

// Adding a value to C1
worksheet.Cells["C1"].PutValue(7);

// Adding a value to "C2" cell
worksheet.Cells["C2"].PutValue(8);

// Adding a value to "C3" cell
worksheet.Cells["C3"].PutValue(9);

// Adding a SUM formula to "A4" cell
worksheet.Cells["A6"].SetArrayFormula("=LINEST(A1:A3,B1:C3,TRUE,TRUE)", 5, 3);

// Calculating the results of formulas
workbook.CalculateFormula();

// Get the calculated value of the cell
string value = worksheet.Cells["A6"].Value.ToString();

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

A detailed section containing features related to the formula is available here:
Formulas

Latest version of the product is available here:
Aspose.Cells for .NET (Latest Version)

We have created a complete running solution which demonstrates hundreds of features related to Aspose.Cells. It is available here.