Invalid formula?! (Ceiling)

Aspose.Cells Product Family
1

Hi Laurence,

I get “Invalid formula” exception when trying to write formulas like following:

"=CEILING((IF(J4-E6<=0; 0; J4-E6));10)"

Does Aspose.Excel have no support for these formulas or where is this error coming from?

Tanks one more time,

Stefan

2

Hi Stefan,

Yes. Currently Aspose.Excel doesn’t support the “CEILING” function. All supported function is listed at Cell.Formula.

I will add it in the next release. It will be availabe at the start of next week.

3

Hi Laurence,

wanted to do a workarround but got same more strange problems.

1. First of all I wanted to set “0,99999999” into the bottom formula -> Invalid formula exception
2. Replaced the “,” with “.” -> this means my number was “0.99999999”. But Excel has cutted the “0.” away -> in excel my number was “99999999"
3. Used my workarround 99999999/100000000 -> works fine!

But now excel shows wrong calculated values in my cells. But the strange thing is when clicking into the cell which contains the formula and wrong value and now commit (exit the cell) with ENTER, the formula calculates right?!?!?!??!

Any ideas?

My formula:
”=INT(IF(J4-E6<0, 0, J4-E6)/10+99999999/100000000)*10"

Regards, Stefan

4

Hi Stefan,

1. Yes. It’s an invalid formula.
2. I test the following forumla as your description and it works fine.

excel.Worksheets[0].Cells[“A1”].Formula = “=INT(IF(J4-E6<0, 0, J4-E6)/10 + 0.99999999)*10” ;


I also find that the result is wrong. I will check and fix it ASAP.

5

Hi Stefan,

The problem is fixed. Please download the latest hotfix.

6

Hi Laurence,

works fine, thanks for this quick support!

“Ceiling” is no more relevant for me, my workaround works fine for me!

-Stefan

7

@WebJumper,
Aspose.Cells has replaced Aspose.Excel which is no more under development now. Aspose.Cells supports all the latest features in MS Excel and is much better in performance as well. There are lot of new formulas which are supported in Aspose.Cells. Here is an example which demonstrates the usage of formulas with the new product.

// 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[0];

// 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 SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";

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

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

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

Here is a document section that provides detailed information about working with formulas using this new product:
Formulas

You can download the latest version of this new product here:
Aspose.Cells for .NET (Latest Version)

You may download a ready to run solution here which can be used to test the product features.