Question about division by zero (DIV/0) error turning into number (NUM) error

Aspose.Cells Product Family
1

Hi,

I have an Excel workbook that uses the NPER function but it causes a #DIV/0! division by zero error in Microsoft Excel.
Here is an example workbook: TestCase08.zip (6.6 KB)

However, if I use Apose.Cells .NET version 22.3.0 to open the workbook and call CalculateFormula, Aspose changes the error from #DIV/0! to #NUM! a number error. For example:

var workbook = new Workbook("TestCase08.xlsx");
Worksheet worksheet = workbook.Worksheets["Sheet1"];

string s1 = worksheet.Cells["A3"].ToString();
workbook.CalculateFormula(); // Calculate formula.
string s2 = worksheet.Cells["A3"].ToString();

The value of s1 is Aspose.Cells.Cell [ A3; ValueType : IsError; Value : #DIV/0!; Formula=NPER(C3/12,D3,-E3,0,0) ]
The value of s2 is Aspose.Cells.Cell [ A3; ValueType : IsError; Value : #NUM!; Formula=NPER(C3/12,D3,-E3,0,0) ]

It seems Aspose.Cells did recognise the original error as a division by zero. I’m just wondering why the error type changed from #DIV/0! (matching what Microsoft Excel shows) into #NUM!? I feel it will be better if the error type did not change and just stay the same as what Excel shows.

Thanks for looking into this question.

2

@llawryy,

Please notice, I am able to reproduce the issue as you mentioned using your template file. I found the division by zero (#DIV/0! ) error is turned into number (#NUM!?) error by Aspose.Cells formula calculation engine as you pointed out. I have logged a ticket with an id “CELLSNET-50648” for your issue. We will look into it soon.

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

3

@llawryy,

This is to notify you that your issue has been resolved now. This fix will be included in the next release (Aspose.Cells for .NET v22.5) scheduled for the second week of May 2022. You will also be notified when the next version is released.

4

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

5

Thank you. I’ve tested Aspose.Cells v22.5.0 and confirm it has been resolved.

6

@llawryy,

Thanks for your feedback.

It is nice to know that Aspose.Cells for .NET v22.5 fixes your issue. Feel free to write us back if you have further queries or comments.