I have many formulae in the cells of a workbook with the IFNA function.
An example of one is:
=IFNA(VLOOKUP(DA7,rngIndexSNData,2,FALSE),DA7)
When I write the line (in C#)
Workbook.CalculateFormula(true);
It calculates the formulae but NOT those with IFNA. Instead there is an error.
I have tried all of the usual fixes for formulae but they make no difference.
Hi,
Thanks for using Aspose.Cells.
Please provide the excel file with this formula so that we could test your provided file with the latest version. In case, this formula is not supported, we will add a ticket for it in our database to support it in our future versions.
Hi
Here is the file.
Please check the sheet Combined data for chart.
You will see #NAME?
The formula does not update when clicking the evaluate formula button or when using the C# code line as described above.
But if you click into the formula right after ‘IFNA’ but before the first ‘(’ and then press the fx (insert function button) it calculates the function as we would expect.
Please help.
Hi Jason,
Thank you for sharing the sample spreadsheet. I have evaluated the presented scenario while using the latest version of Aspose.Cells for .NET 9.0.1, and I am not able to observe the said problem. Please check the attached resultant spreadsheet as well as my test code as follow.
Please give the latest version a try on your side as well, and let us know your feedback.
C#
var book = new Workbook(dir + “Copy+of+COMBINED_.xlsm”);
book.CalculateFormula(true);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV6”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV7”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV8”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV9”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV10”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV11”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV12”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV13”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV14”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV15”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV16”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV17”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV18”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV19”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV20”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV21”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV22”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV23”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV24”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV25”].Value);
book.Save(dir + “output.xlsm”);
book.CalculateFormula(true);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV6”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV7”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV8”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV9”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV10”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV11”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV12”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV13”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV14”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV15”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV16”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV17”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV18”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV19”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV20”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV21”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV22”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV23”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV24”].Value);
Console.WriteLine(book.Worksheets[“Combined data for chart”].Cells[“EV25”].Value);
book.Save(dir + “output.xlsm”);
I get the same error. I have a worksheet with a function
=IFNA(N8,“na”)
I read the worksheet, call CalculateFormula(true) then save the worksheet as a new name. When I open the worksheet in excel it has the “#NAME?” in the cell. Testing on aspose 17.8. If I retype the function exactly as it is in excel the function works.
In your code above try saving the file as a new name and reopen in excel.
A work around is to use =IFERROR(N8,“na”) Seem to work
Please share your Excel file containing your mentioned formulas for our evaluation. We will look into it and in case of bug we will log this issue in our database for investigation and for a fix.