Aspose Cells errors with CEILING function

Aspose.Cells Product Family
1

I would like to highlight recent issues I have been having with Aspose handling of the CEILING function with significance parameter of 0.12.
A client supplied a hugely complex spreadsheet which produced incorrect results when run through Aspose Cells when compared to running directly in Excel. The spreadsheet is client confidential so cannot be supplied.

I would be grateful to receive any feedback on these observations., thank you

The workbook had many worksheets including 720 formula including CEILING function with significance parameter of 0.12,

i.e. =CEILING(F90-IF(Inputs!F$192=“No”,0,‘Pen Deb PRE DOL LR’!F322),0.12)

Results with CEILING(xxx,0.12)

9371.16,10664.52,645.12,4514.88,0,0,0,0,NULL,0,0,0,0,125000

I modified the SS to use CEILING(xxx,0.01) everywhere as an alternative. Results:

8835.48,9986.03,603.08,4221.5,535.470000000001,678.220000000001,41.89,293.27,NULL,526.67,684.12,42.2900000000001,296.08,125000

I also modified the SS to use MROUND(0.05999 + xxx,0.12) which should be mathematically more or less equivalent. Results:

8835.6,9986.28,603.12,4221.6,535.439999999999,678.24,41.88,293.280000000001,NULL,526.68,684.12,42.24,296.04,125000

As you can see the results with CEILING(xxx,0.12) are considerably different to those using MROUND(0.05999 + xxx,0.12) , which are very close to the results obtained when running the original spreadsheet directly in Excel.

(This is tested using Aspose Cells 22.5)

2

@chris_savage,

To evaluate your issue precisely, we need your template Excel file, sample (runnable) code and output results either in an output file(s) or get results dynamically at runtime to evaluate the differences when comparing with MS Excel calculated results. I have made this thread private so only you and Aspose staff members could see or download your attachments, no other user can do that. So, you may safely attach your attachments (you may zip the files prior attaching). If you still have reservations, you may fill some dummy data to be replaced in your spreadsheet and provide us. Alternatively, you may create a smaller version of the file and devise a separate template file containing the data and formulas accordingly.

3

I have attached a zip of the problem spreadsheet together with test c# code which can be used to call it.
The test shows a result of zero when running the test in Excel results in a number.
As an additional test I have attached a modified spreadsheet where the “CEILING(” function has been replaced by "MROUND(0.05999 + ", which should be mathematically equivalent. This returns the correct calculation result.

Test Ceiling Issue.zip (3.3 MB)

4

@chris_savage,

I could not execute your test case properly as I am not sure about some of the variables/objects and methods used in the code which are not resolved. It would be better if you could create and provide a standalone console application (complete source code without compilation errors) to reproduce the issue on our end, so we could evaluate your issue precisely to consequently figure it out soon.

By the way, I simply tested Aspose.Cells’ formula calculation engine regarding CEILING formula using your problematic spreadsheet (I used our latest version of Aspose Cells for .NET v22.5) with the following sample code and it works fine and as expected:
e.g.
Sample code:

 var workbook = new Workbook("e:\\test2\\CeilingProblemSpreadsheet.xlsx");
            Worksheet worksheet = workbook.Worksheets["Late Ret"];
            Cell cell = worksheet.Cells["F443"];
            Console.WriteLine(cell.Value);//0

            workbook.CalculateFormula();
            Console.WriteLine(cell.Value);//0
5

Sorry I don’t know how to create console application for this.
The code was tested locally here on 22.5, and an earlier version 19.3 I think, with the same result.
The calculation relies upon inserting a long string of parameters into a specific cell, as set out in the test code, which then feeds through into the calculations.

6

@chris_savage,

See the document for your reference.

We do not entirely sure about for which specific range of cells and in which worksheet(s) you are updating data for the underlying cell having CEILING formula. So, if you cannot create and provide a sample simulation console application, at least, give us the definition code segments of the following objects and methods to know how you are creating those objects/methods:
AsposeWorkbook, AsposeWorkbook.Put(), AsposeWorkbook.Get(), etc.
After getting the above artifacts, we will be able to compile your code segment precisely to reproduce the issue.

7

Sorry I didn’t get a notification of your response.
To test the issue, programmatically write the long parameter string into the first sheet “Touch Inputs”, cell A1000, i.e.
workbook.Put( “1”, “1000”, “1”, parameter string…)
To check results, programmatically read from second sheet “Touch Outputs”, cell T59, i.e.
workbook.Get(“2”, “59”, “21”);

The parameter string to test with is:
“MR J TEST,13002557,10/03/2022,GHRQ,03/01/2021,R,61988,03/01/1959,WP102476C,03/01/2019,M,HGSU,YJ56,03/10/2029,Y,03/01/2021,11/09/1986,N,1073100.00,Early Retirement,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0,0,0,1083.28,125000.00,No,0,0,0,0,12742.69,265.47,796.42,5574.93,0,30529.37,0,0,0,0,0,0,0,0,0,No,0,0,150000,63713.32,0,47100.00,57000.00,58480.00,61880.00,61880.00,63880.00,65379.97,70040.97,70040.97,72114.68,73789.35,73789.35,01/04/2009,01/04/2010,01/04/2011,01/04/2012,01/04/2013,01/04/2014,01/04/2015,01/04/2016,01/04/2017,01/04/2018,01/04/2019,01/04/2020,50,No,0,0,0,0,0,0,0,0.00,0.00,03/10/2027,0,0,0.00,0.00,0,0,0,0,0,0,0,Staff,N,13043.02,63713.32,30585.30,13043.02,0,0,0,Male,JCSS,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,No,0,0,0,N,N,0,0,0,0,0,Y,N,0.00,0.00,0.00,0.00,0.00,0,Y,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0.00,0,0,Y,0.00,0.00,0,0,0,351.52,102.96,764.40,0.00,0.00,0.00,0.00,3542.08,516.48,5872.42,0.00,0.00,0.00,0.00,0.00,30529.37,0,0,0,0,0,0,-2.1 - -2.01,0,0,0,0,0,0,351.52,102.96,764.40,0.00,0.00,0.00,0.00,3542.08,516.48,5872.42,0.00,0.00,0.00,0.00,0.00,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0.00,47100.00,47100.00,57000.00,58480.00,61880.00,61880.00,63880.00,65379.97,70040.97,70040.97,72114.68,01/04/2007,01/04/2008,01/04/2009,01/04/2010,01/04/2011,01/04/2012,01/04/2013,01/04/2014,01/04/2015,01/04/2016,01/04/2017,01/04/2018,0,0,0,0,0,0,0,0,0,0,0,0,N,35090.00,16.0000,1.7,40000.00,20.0000,”

8

@chris_savage,

We are sorry but we still do not know about the definitions of the following APIs and methods:
AsposeWorkbook, AsposeWorkbook.Put(), AsposeWorkbook.Get(), etc.

It looks like you might have written some self hosting code snippets and using those custom methods with Aspose.Cells APIs. So, we need a working sample project to replicate the issue on our your end.

We again request you to create a standalone console application (complete source code without compilation errors) to reproduce the issue on our end, so we could evaluate your issue precisely and figure it out soon.

9

Sorry there was some wrapper code to handle converting string row/cols to numbers, but it’s fairly trivial stuff if you’ve ever used Aspose cells. I’m fairly certain this is a bug in your product I’m trying to show you.
Put…
var worksheet = workbook.Worksheets[worksheetNo];
var cell = worksheet.Cells[row, column]
cell.PutValue(value, true, true);

Get…
var worksheet = workbook.Worksheets[worksheetNo];
var cell = worksheet.Cells[row, column]
return cell.StringValue;

10

@chris_savage,

Thanks for providing further details.

I am able to reproduce the issue as you mentioned by using your template file and following sample code. I found an issue with with CEILING formula/function in Aspose.Cells’ formula calculation engine which gives incorrect results.
e.g.
Sample code:

var workbook = new Workbook("e:\\test2\\CeilingProblemSpreadsheet.xlsx");
Worksheet worksheet = workbook.Worksheets["Touch Inputs"];
Cell cell = worksheet.Cells["A1000"]; 
cell.PutValue("MR J TEST,13002557,10/03/2022,GHRQ,03/01/2021,R,61988,03/01/1959,WP102476C,03/01/2019,M,HGSU,YJ56,03/10/2029,Y,03/01/2021,11/09/1986,N,1073100.00,Early Retirement,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0,0,0,1083.28,125000.00,,No,0,0,0,0,12742.69,265.47,796.42,5574.93,0,30529.37,0,0,0,0,0,0,0,0,0,No,0,0,150000,63713.32,0,47100.00,57000.00,58480.00,61880.00,61880.00,63880.00,65379.97,70040.97,70040.97,72114.68,73789.35,73789.35,01/04/2009,01/04/2010,01/04/2011,01/04/2012,01/04/2013,01/04/2014,01/04/2015,01/04/2016,01/04/2017,01/04/2018,01/04/2019,01/04/2020,,50,,,No,0,0,0,0,0,0,0,0.00,0.00,03/10/2027,0,0,0.00,0.00,0,0,0,0,0,0,0,Staff,N,13043.02,63713.32,30585.30,,,,,,,,,,,,,,,,,,,,,,,,,13043.02,0,0,0,,Male,,,,JCSS,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,No,0,,,,0,0,N,N,0,0,0,0,0,Y,N,0.00,0.00,0.00,0.00,0.00,0,,Y,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,,0,0.00,0,0,Y,0.00,0.00,,0,0,0,351.52,102.96,764.40,0.00,0.00,0.00,0.00,3542.08,516.48,5872.42,0.00,0.00,0.00,0.00,0.00,30529.37,0,0,,0,,0,0,0,-2.1 - -2.01,,,,0,0,0,0,0,0,351.52,102.96,764.40,0.00,0.00,0.00,0.00,3542.08,516.48,5872.42,0.00,0.00,0.00,0.00,0.00,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,,,,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,,,0,0.00,47100.00,47100.00,57000.00,58480.00,61880.00,61880.00,63880.00,65379.97,70040.97,70040.97,72114.68,01/04/2007,01/04/2008,01/04/2009,01/04/2010,01/04/2011,01/04/2012,01/04/2013,01/04/2014,01/04/2015,01/04/2016,01/04/2017,01/04/2018,0,0,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,,,,N,35090.00,16.0000,1.7,40000.00,20.0000,");

Cell cell2 = workbook.Worksheets["Touch Outputs"].Cells["U59"]; 
Console.WriteLine(cell2.Value);//0 - ok

workbook.CalculateFormula();
Console.WriteLine(cell2.Value);//0 - Not ok, it should be 1549.08

I have logged a ticket with an id “CELLSNET-51186” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

11

Thank you for looking into it Amjad. I look forward to hearing further from you.

12

@chris_savage,

You are welcome. And, sure, we will keep you posted with updates or resolution (once available) on it.

13

@chris_savage,

This is to inform you that your issue (logged earlier as “CELLSNET-51186”) has been resolved. The fix will be included in the next release (Aspose.Cells for .NET v22.6) which is scheduled in the second week of June 2022. You will also be notified when the next version is published.

14

Thanks you for fixing this issue so quickly.
Chris Savage
Equiniti

15

@chris_savage,

You are welcome.

16

The issues you have found earlier (filed as CELLSNET-51186) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

17

The issues you have found earlier have been fixed in this update. If you have downloaded the prior one(22.6.0), please use this update to replace the downloaded one because 22.6 has a bug which sometimes may cause generated file corrupted.