We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Bug in long formulas

code:

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet orders = workbook.Worksheets[0];

Worksheet hidden = workbook.Worksheets.Add("hidden");

orders.Name = "orders";

Range range = orders.Cells.CreateRange("A1", "A1");

range.Name = "OPENLEVEL";

orders.Cells["A1"].PutValue("enter 160 here");

hidden.Cells["A1"].Formula = "=ROUND(0.01 * ROUNDUP(ROUND(Max(OPENLEVEL, IF(Max(Max(IF(IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()) > IF(160.73 < IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()),160.73,NA()),IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()),NA()), IF(Max(160.662100651,OPENLEVEL + 0.34) > 159.65,Max(160.662100651,OPENLEVEL + 0.34),NA())), IF(Max(160.662100651,OPENLEVEL + 0.3408) > 159.65,Max(160.662100651,OPENLEVEL + 0.3408),NA())) > Max(Max(Max(159.019998888398, IF(160.73 < IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()),160.73,NA())), 159.65), 159.65),Max(Max(IF(IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()) > IF(160.73 < IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()),160.73,NA()),IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()),NA()), IF(Max(160.662100651,OPENLEVEL + 0.34) > 159.65,Max(160.662100651,OPENLEVEL + 0.34),NA())), IF(Max(160.662100651,OPENLEVEL + 0.3408) > 159.65,Max(160.662100651,OPENLEVEL + 0.3408),NA())),NA()))/0.01,9), 0),9)";

workbook.Save("test8.xls");

return;

and I get different result in my excel file:

=0.01 OPENLEVEL MAX(MAX(IF(IF(MAX(161.342035714,OPENLEVEL+1.006758621)>159.019998888398,MAX(161.342035714,OPENLEVEL+1.006758621),NA())>IF(160.73159.019998888398,MAX(161.342035714,OPENLEVEL+1.006758621),NA()),160.73,NA()),IF(MAX(161.342035714,OPENLEVEL+1.006758621)>159.019998888398,MAX(161.342035714,OPENLEVEL+1.006758621),NA()),NA()),IF(MAX(160.662100651,OPENLEVEL+0.34)>159.65,MAX(160.662100651,OPENLEVEL+0.34),NA())),IF(MAX(160.662100651,OPENLEVEL+0.3408)>159.65,MAX(160.662100651,OPENLEVEL+0.3408),NA()))>MAX(MAX(MAX(159.019998888398,IF(160.73159.019998888398,MAX(161.342035714,OPENLEVEL+1.006758621),NA()),160.73,NA())),159.65),159.65)0IF(MAX(161.342035714,OPENLEVEL+1.006758621)>159.019998888398,MAX(161.342035714,OPENLEVEL+1.006758621),NA())>IF(160.73159.019998888398,MAX(161.342035714,OPENLEVEL+1.006758621),NA()),160.73,NA())MAX(161.342035714,OPENLEVEL+1.006758621)>159.019998888

Hi,

Well, I think your formula is long enough that when i manually enter your formula in A1 Cell in Ms Excel "I got formula is too long" error. I think you should try to minimize it and then try it.

And we will also check why it is changed. We will get back to you soon.

Thank you.

Hi,

After checking this isse, we think it's the limitation of MS Excel. Please change your codes and you will find the formula you want to set in the file.

Workbook workbook = new Workbook();

Worksheet orders = workbook.Worksheets[0];

Worksheet hidden = workbook.Worksheets.Add("hidden");

orders.Name = "orders";

Range range = orders.Cells.CreateRange("A1", "A1");

range.Name = "OPENLEVEL";

orders.Cells["A1"].PutValue("enter 160 here");

hidden.Cells["A1"].Formula = "=ROUND(0.01 * ROUNDUP(ROUND(Max(OPENLEVEL, IF(Max(Max(IF(IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()) > IF(160.73 < IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()),160.73,NA()),IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()),NA()), IF(Max(160.662100651,OPENLEVEL + 0.34) > 159.65,Max(160.662100651,OPENLEVEL + 0.34),NA())), IF(Max(160.662100651,OPENLEVEL + 0.3408) > 159.65,Max(160.662100651,OPENLEVEL + 0.3408),NA())) > Max(Max(Max(159.019998888398, IF(160.73 < IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()),160.73,NA())), 159.65), 159.65),Max(Max(IF(IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()) > IF(160.73 < IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()),160.73,NA()),IF(Max(161.342035714,OPENLEVEL + 1.006758621) > 159.019998888398,Max(161.342035714,OPENLEVEL + 1.006758621),NA()),NA()), IF(Max(160.662100651,OPENLEVEL + 0.34) > 159.65,Max(160.662100651,OPENLEVEL + 0.34),NA())), IF(Max(160.662100651,OPENLEVEL + 0.3408) > 159.65,Max(160.662100651,OPENLEVEL + 0.3408),NA())),NA()))/0.01,9), 0),9)";

workbook.Save("test8.xls");

workbook.Open("test8.xls");

Console.WriteLine(workbook.Worksheets["hidden"].Cells["A1"].Formula);

Well, at least your code should say the same as Excel (formula too long) but not accept text and change it in some odd fashion.

A.

Hi,

Please check it yourself, such "if(formula.Length < 1024){cell.Formula = formula;}"

In MS 97-2003,the max length of the formula is 1,024 characters. In Excel 2007 , MS has removed this limitation.