Support for TOCOL function in version 24.2.0.0

Aspose.Cells Product Family
1

Hi,
In my application, I am writing some value in my workbook and then using CalculateFormula() to recalculate the workbook because the written value can impact the values in other cells. But I am noticing the cells that have this formula “=TOCOL(IFS(SEQUENCE(,COUNTA(‘DR-EL_Values’!$B:$B)-2),OFFSET(‘DR-EL_Values’!$A$3,0,0,COUNTA(‘DR-EL_Values’!$A:$A)-2),1,FALSE))” start giving error “#NA”.
image (2).png (28.2 KB)

Though the list of supported formulas in Aspose documentation shows that “TOCOL” function is supported, I am wondering maybe it was not supported in the version that I am using or could it be some other function. Can you shed some light on the matter.

2

@kanbee

Could you please provide more details about the specific version of Aspose.Cells you are using and the context in which the TOCOL function is being applied?

3

The version is 24.2.0.0. and I have provided screenshot for context

4

@kanbee
Due to the formula you mentioned referencing the calculated values of other formulas. We need sample files to locate the issue. Would you like to provide your excel sample file and runnable test code? We will check it soon.

5

Scen_Par-DRvsEL.7z (19.9 KB)

I have attached the sample file. If you put a valid value (1,2,7,8) in cell A2 of sheet “DRvsEL” using the following code the expected behaviour for it is to update values in the same sheet. Currently like I told before, it results into errors.

                using (var mainWorkbook = AsposeOpController.OpenWorkBook(inputFileAbsolutePath))
                {
                    var sheet = mainWorkbook.Worksheets[SheetName];

		try

		{
			HandleCancellationRequest2(token);

			// indexing according to aspose
			// sheet.Cells[tag.TagRangeInfo.TagRowNumber, tag.TagRangeInfo.TagColumnNumber-1]
			sheet.Cells[1,0] // for cell A2
				.PutValue(kvp.Key);
			// added calculated formula for aspose
			mainWorkbook.CalculateFormula();
			mainWorkbook.Save(inputFileAbsolutePath);

		}
		catch (OperationCanceledException)
		{
			throw;
		}

                }

Additional information:- I also tried it with version 24.9 and it still does not work, I get the same error.

6

@kanbee,

Thanks for the template Excel file and further details.

After initial testing, I am able to reproduce the issue as you mentioned by using your template XLSX file and following sample code. I found formulas (involving TOCOL, SEQUENCE and COUNTA) are evaluated as “#NA”.

var mainWorkbook = new Aspose.Cells.Workbook("e:\\test2\\Scen_Par-DRvsEL.xlsx");
mainWorkbook.CalculateFormula();
mainWorkbook.Save("e:\\test2\\out1.xlsx");

We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-58347

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

7

Great!
Can you also point me in direction of where I can find the list of formulas that are supported in version 24.2.0.0?
Because the link that I found (Supported Excel Functions|Documentation) shows all the formulas, that probably only work with the latest verision.

8

@kanbee,

Kindly note that the Supported Excel Functions/Formulas document is always kept up-to-date and provides a comprehensive list of formulas supported by the latest version of the APIs. Unfortunately, we do not maintain a document specifying which formulas were supported in older versions. However, for your reference, the TOCOL and TOROW formulas were introduced and supported starting from Aspose.Cells v24.2 (please see the Release Notes document for further details). Additionally, other formulas, such as SEQUENCE, COUNTA, and OFFSET, were supported in earlier versions of Aspose.Cells prior to version v24.2.

9

@kanbee,

We are pleased to inform you that your issue (Ticket ID: “CELLSNET-58347”) has been resolved. The fix/enhancement will be included in our upcoming release (Aspose.Cells v25.6) that we plan to release in the first half of June 2025. You will be notified when the next version is published.

10

The issues you have found earlier (filed as CELLSNET-58347) have been fixed in this update. This message was posted using Bugs notification tool by leoluo