Unable to calculate named ranges

Aspose.Cells Product Family
1

CalculateFormula appears to break when calculating named ranges. For instance, if I have a formula that multiplies two named ranges together the result is a #VALUE!. Have you seen this before? If this is a bug, how long would it take for you to provide a fix?

2

Please try this attached version.

If it still doesn't work, could you post a simple sample file to show you problem? We will fix it in 1-2 days.

3

The attached file did not fix the issue. However, after looking into this issue more I believe I tracked down the problem. It looks like their is a specific named range that aspose.cells is breaking on.

When I have a named range called NPV_Discount_Rate and I use it in a calculation with another named range I receive the #Value! error. However, when I delete the NPV_Discount_Rate named range and using a different named range like NPV_Rate the problem goes away.

I've attached a sample file that I've been using for testing this issue.

4

Hi,

Thanks for the template file.

Well, I tried your template file with the attached version and it works fine. Following is my sample code.

Workbook workBook = new Workbook();
workBook.Open("d:\\test\\Financial Model Test.xls");
workBook.CalculateFormula();
Console.WriteLine(workBook.Worksheets[0].Cells["B1"].StringValue); //I got result: 1773712% same as MS Excel.

Could you give us the result for the above code using the attached version.

Thank you.

5

I see what the issue is... I'm inserting a string into the cell with the named range NPV_Discount_Rate before I run workbook.CalculateFormula(). This is causing an error in the calculation within excel because it's storing the value as text. However, I won't know what the datatype is ahead of time. Do you know a way around this?

Here is the code to run:

Workbook workBook = new Workbook();
workBook.Open("d:\\test\\Financial Model Test.xls");

Aspose.Cells.Range range = workbook.Worksheets.GetRangeByName("NPV_Discount_Rate");

range[0, 0].PutValue("12");
workBook.CalculateFormula();
Console.WriteLine(workBook.Worksheets[0].Cells["B1"].StringValue);

6

I actually ended up fixing this issue by setting the IsConverted parameter to true when calling the PutValue method.

Thanks for you help!