Getting #VALUE! on CalculatedValue on Formula.

Question

Hi,I'm generating a workbook dynamically and I got a Range with a formula that uses another calculated values. This is the Formula code rangeC.Formula = $"=MIN(COUNTIF(D49:CD59,\"<=\"&{rangeB.AddressLocal})-COUNTIF(D49:CD59,\"<\"&{rangeA.AddressLocal}))";The problem is that if I save the workbook, it takes like 14 minutes and maybe I got a timeout. But If I replace that formula with static values, it is saved instantly.I'm using a lot if this ranges with the same formula (iteration) to generate a Chart. If I remove the chart the workbook is saved quick too.I've tried to get the CalculatedValue of that cell but I got #VALUE!. and If I put that Formula into an XLS File works well.Is there any bug on the CountIF function? maybe with the charts?

Carlos Ariel · Accepted Answer

Hi Ramya,I think that the problem was caused because the formula should be

rangeC.Formula = $"=MIN(COUNTIF(D49:CD59;\"<=\"&{rangeB.AddressLocal})-COUNTIF(D49:CD59;\"<\"&{rangeA.AddressLocal}))";

and NOT

rangeC.Formula = $"=MIN(COUNTIF(D49:CD59,\"<=\"&{rangeB.AddressLocal})-COUNTIF(D49:CD59,\"<\"&{rangeA.AddressLocal}))";

The change was a ;

Ramya Sivakumar · Answer

Hi Carlos, 

Greetings from Syncfusion. 

The Execution time changed based on the number of cells which contains the formula, and the maximum number of iterations occurs during the calculation. 
Please the use the following code snippet to enhance the calculation in the CalcEngine. 
Code snippet: 




using (ExcelEngine excelEngine = new ExcelEngine()) 
{ 
    //Instantiate the Excel application object 
    IApplication application = excelEngine.Excel; 
 
    //Assigns default application version 
    application.DefaultVersion = ExcelVersion.Xlsx; 
 
    //Load the file into stream 
    FileStream inputStream = new FileStream("Formula.xlsx", FileMode.Open); 
 
    //Loads or open an existing workbook through Open method of IWorkbooks 
    IWorkbook workbook = application.Workbooks.Open(inputStream); 
 
    //Access first worksheet from the workbook 
    IWorksheet worksheet = workbook.Worksheets[0]; 
 
    if (worksheet.CalcEngine == null) 
    { 
         //Enables the calculation support. 
         worksheet.EnableSheetCalculations(); 
    } 
 
    //Specifies the maximum number of recursive calls that can be used to compute a cellvalue. 
    worksheet.CalcEngine.MaximumRecursiveCalls = 10000; 
 
    //maximum number of iterative calls that can be made on a cell. 
    worksheet.CalcEngine.IterationMaxCount = 10000; 
 
    //The CalcQuick will throw an exception when it detects a circular calculation. 
    worksheet.CalcEngine.ThrowCircularException = true; 
 
    //This is the number of recursive calls that can be made during calculations. 
    CalcEngine.MaxStackDepth = 10000; 
 
    //Dates can be used as operands in calculations 
    worksheet.CalcEngine.UseDatesInCalculations = true; 
 
    //Strings concatenated using the '&' operator should be returned inside double quote marks. 
    worksheet.CalcEngine.UseNoAmpersandQuotes = true; 
 
    //Formula returns its FormulaValue instead of repeated calculation 
    worksheet.CalcEngine.UseFormulaValues = true; 
 
    //IF function calculations should specifically avoid computing the non-used alternative. 
    worksheet.CalcEngine.AllowShortCircuitIFs = true; 
 
    FileStream stream = new FileStream("Formula_Output.xlsx", FileMode.Create, FileAccess.ReadWrite); 
    workbook.SaveAs(stream); 
    stream.Dispose(); 
} 
The #value issue may occur because some of the dependent cell formula returns the wrong value. So, we request you to share the corresponding Excel document which will be helpful for us in investigating the query and provide details. 

Regards, 
Ramya.

Carlos Ariel · Answer

Thanks,I've added the code you suggested, but is still taking forever to generated the document.I'm attaching one example of the file we are generating. The formula is on row 38, and we are using the data on row 42 to generate the Chart.Thanks.Attachment: Example_Report_7d258ecc.zip

Ramya Sivakumar · Answer

Hi Carlos,We deeply regret for the inconvenience caused.We are checking the query and will get back to you with details in 1 business days on February 14, 2022.We appreciate your patience.Regards,Ramya

Konduru Keerthi Konduru Ravichandra Raju · Answer

Hi Carlos, 

We appreciate your patience. 

We have tried to retrieve the CalculatedValue of cells in the rows 38 and 42. We found that some of the values are correct but some are wrong. But we did not come across #VALUE! error.  

We request you to share the cells in which you find this issue and also confirm us the Syncfusion XlsIO version you are using at your end. This will be helpful for us in investigating the query further and prompt a solution at the earliest. 

Regards, 
Keerthi.

Carlos Ariel · Answer

Thanks, I will continue reviewing it.We are using: Syncfusion.XlsIO.Net.Core (19.4.0.47)Syncfusion.XlsIORenderer.Net.Core

(19.4.0.47)

Ramya Sivakumar · Answer

Hi Carlos, 

We are unable to reproduce the #VALUE! Error in the v19.4.0.47 also. We request you to share the sample or code snippet which you are using your side, which will be helpful for us in investigating the query and provide details. And we request you to share the cells in which you find this issue. 

Please download the sample which we are tried at our end from the following link 
Sample link - https://www.syncfusion.com/downloads/support/directtrac/general/ze/F-172756-1903202072 

We appreciate your patience. 

Regards, 
Ramya.