Incorrect formula processing - TOCOL and nested array multiplier with COLSCAN true

Aspose.Cells Product Family
1

Hi We are evaluating upgrading to the latest version of Aspose Cells to take advantage of the support for newer Excel functions. We have found an issue where the formula =TOCOL(SIMPLE_TABLE *(100-MULTIPLIERS),TRUE) returns incorrect values. Correct values are returned when the colscan option is set to false, or if the multiplier is omitted.
image.png (6.1 KB)

I have attached simplified code and excel to show the issue.

AsposeToColMultiplierIssue.zip (8.8 KB)

2

@LPyrah

Cause

The issue you’re experiencing with the formula =TOCOL(SIMPLE_TABLE * (100 - MULTIPLIERS), , TRUE) returning incorrect values is likely due to how the TOCOL function processes nested arrays when the COLSCAN option is set to true. This behavior can stem from the multiplication and array handling in the version of Aspose.Cells you are currently using.

Solution

To resolve this issue, please follow these steps:

  1. Update Aspose.Cells: Ensure you are using the latest version of Aspose.Cells, as updates often include bug fixes and enhancements related to formula processing.

  2. Check Formula Structure: Verify that the structure of your formula is correct and that the ranges used in SIMPLE_TABLE and MULTIPLIERS are properly defined.

  3. Test Without Multipliers: Since the formula works correctly when the multiplier is omitted, isolate the issue by testing the multiplication separately to confirm its behavior.

  4. Use Alternative Functions: If the issue persists, consider using alternative functions or breaking down the formula into smaller parts to identify where the error occurs.

Code Example

Here’s a simple example of how you might structure your code to test the formula:

// Create a workbook and access the first worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

// Define your data
worksheet.Cells["A1"].PutValue(1);
worksheet.Cells["A2"].PutValue(2);
worksheet.Cells["B1"].PutValue(10);
worksheet.Cells["B2"].PutValue(20);

// Define the formula
worksheet.Cells["C1"].Formula = "=TOCOL(A1:A2 * (100 - B1:B2), , TRUE)";

// Calculate the formula
workbook.CalculateFormula();

// Get the result
double result = worksheet.Cells["C1"].Value;

// Output the result
Console.WriteLine(result);

Next Steps

  1. Test the above code snippet with your data to see if it resolves the issue.
  2. If the problem continues, please reach out to Aspose support with a detailed description of your scenario and the specific version you are using.

I hope this helps! Please let me know if you need further assistance.

3

@LPyrah,

Thanks for the template Excel file and screenshot.

I reproduced the issue as you mentioned by using your template Excel file and sample code snippet. I found incorrect formula processing for TOCOL and nested array multiplier with COLSCAN true.

We require thorouhg 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-58719

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.

4

@LPyrah,

This is to inform you that your issue (Ticket ID: “CELLSNET-58719”) has been resolved. The fix/enhancement will be included in our upcoming release (Aspose.Cells v25.8) that we plan to release during this week. You will be notified when the new version is published.

5

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