Invalid formula?! (Ceiling)


#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