We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Can't copy column with NA() in formula

Hello,

I am trying to copy a series of columns that contain a formula:

=IF(ISERROR(AVERAGE(L10:O10)), NA(), AVERAGE(L10:O10))

When I perform the copy, I get a runtime error:

Error in Cell: O18-Invalid formula:"=ISERROR(AVERAGE(L10:O10))IF(NA,(),AVERAGE(L10:O10))".

If you notice, the error message has interted a comma after the "NA" in the formula. Does this imply that Aspose.Cells does not support the NA() formula? If Aspose.Cells does not support the NA() formula, do you have another mechanism that I can use to indicate that a cell containing a formula calculates to a blank cell? (I am trying to plot a chart for which I must show gaps if there is no data. To do this successfully, I must be able to indicate to Excel that, although the cell contains a formula, it needs to be calculated as "blank" so that a gap will be seen in the chart data series.)

I am using Aspose.Cells version 4.4.0.28.

Thanks,

Pam

Hi Pam,

Thanks for considering Aspose.

Well, NA formula might not be supported with the older version you are using, but it is supported now, check the supported formula list: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/supported-formulas-functions.html

So, please use 4.5 or greater versions. I have attached the latest fix for you, kindly try it. I have also tested your scenario and it works fine, the formula containing NA function is copied fine.

Thank you.

Amjad,

Thanks for the info. However...I have attempted to use the dll that you provided, but I have run into additional issues. Our support has run out and I am not licensed for 4.5. I'm working on this and will have new licenses soon. Despite that, I have run the dll in evaluation mode and the results are not good. In my template, there are 11 charts that are populated based upon data extracted from database and manipulated within Excel. Each chart is fully setup within the template and, at runtime, I insert the requisite number of columns into the ranges that provide data to the charts. Of the eleven charts, 4 of them have issues resulting from incorrect formulas being created after I perform the CopyColumn function. Since I've just run your new dll without much time to investigate, I'm not yet able to tell you exactly where the issues are--only that I wasn't having issues with version 4.4.0.28.

Additionally, I am receiving a "File error: data may have been lost" message when initially opening the spreadsheet. If you check your forum records, you will see that we worked through a long series of issues with this error not too long after your version 4.4 was released. (Please see this thread...http://www.aspose.com/community/forums/2/106609/cells.copycolumn-bug/showthread.aspx). Your team resolved this issue at one point, indicating to me that it had something to do with auto-detecting a file format.

So...I'm kind of stuck. I can't stay at 4.4.0.28 because the function is not supported, but I can't upgrade because the performance of the dll is severely degraded. I'll continue to investigate the issues with the broken charts and provide more info later today.

Thanks,

Pam

Amjad,

After more investigation, the errors in the charts appear to fairly consistent. The formulas in the cells providing data to the charts are showing up as #REF after the template has been processed through Aspose.Cells. The formulas are fairly simple--sums and averages, with a small amount of IF and IFERROR used. Here are a few representative examples of the issues I am seeing:

Issue 1: The template has the following formula SUM(L3:P3) in cell Q3. After populating the source data tab in the workbook, the contents of cell Q3 is #REF.

Issue 2: The template has the formula AVERAGE(L10:O10) in cell O18. After populating the source data tab in the workbook, the contents of cell O18 is #REF.

Issue 3: The template has the formula =IF(ISERROR(AVERAGE(L10:O10)),NA(),AVERAGE(L10:O10)) in cell O18. After populating the source data inthe workbook, the contents of cell O18 is #REF.

Thanks,

Pam

Hi,

Could you post your template excel file with sample code here to reproduce the issues you have mentioned, we will check it soon.

Thank you.

Amjad,

I cannot provide production code because it depends on numerous interactions with local database but I have attached a sample showing one of the problems that I'm seeing. In this sample, the "data may be lost" problem occurs when I insert columns using the InsertColumn function with "update reference" set to TRUE. I need to have the "Update reference" parameter set to TRUE because if I leave off this parameter in my production code, there are several formulas whose references are not updated appropriately when data is added to the Source data tab.

I'm going to continue to troubleshoot on my end, but will appreciate any assistance you can give.

Thanks,

Pam

Hi Pam,

Thanks for considering Aspose and providing us the sample project.

Yes, we found the issue you have mentioned. We will figure it out soon.

Thank you.

Hi Pam,

Please try this fix.

We have fixed this bug.

Thanks for your project.