#NUM! inside FV(NPER()) formula causes evaluation error

Aspose.Cells Product Family
1

Hi,

I have an example worksheet with the following formula at cell A1:

=FV(0.05/12,NPER($A$2/12,$B$2,-$C$2-IF(FALSE,$C$2,0),0,0),0,-400000,0)

The NPER(…) part evaluates to #NUM! in Excel, resulting in the entire formula evaluating to #NUM!.

However, in Aspose.Cells version 18.6.0, the entire formula evaluates to 0. Can you please have a look.

You can try this code.

var workbook = new Workbook(@"C:\Test.xlsx");
workbook.CalculateFormula();
var sheet = workbook.Worksheets[0];
Cell cell = sheet.Cells["A1"];
var value = cell.ToString();
// value is "Aspose.Cells.Cell [ A1; ValueType : IsNumeric; Value : 0; Formula:=FV(0.05/12,NPER($A$2/12,$B$2,-$C$2-IF(FALSE,$C$2,0),0,0),0,-400000,0) ]"
// But in Excel, it is #NUM!.

Thanks!

Test.zip (6.1 KB)

2

@llawryy,

Thanks for the template file and sample code.

After an initial test, I am able to reproduce the issue as you have mentioned. I found Aspose.Cells evaluates the formula as “0” whereas MS Excel evaluates it as “#NUM!”. I have logged a ticket with an id “CELLSNET-46217” for your issue. We will look into it soon.

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

3

@llawryy,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46217”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

4

@llawryy

Thanks for using Aspose APIs.

Please download and try the following fix and let us know your feedback.

5

Great! Thanks for the extremely quick response. I will test it out and let you know.

6

@llawryy,

Please take your time to evaluate your test cases with the new fix and in case of any issue or query, feel free to contact us any time, we will be happy to assist you soon.

7

The issues you have found earlier (filed as CELLSNET-46217) have been fixed in Aspose.Cells for .NET v18.7. Please also check the document/article for your reference: Install Aspose Cells through NuGet|Documentation