Complex Array Function Formula Not Working

Aspose.Cells Product Family
3

Thanks John, literally, all I do is open up the file, run the calculations and save it, nothing special. The workbook has all of the formulas already.

I can see it doesn’t work because when I open and recalculate the workbook in Excel, the data changes and goes back to what I expect it to be.

using (var workbook = new Workbook(inputStream))
{
    var worksheet = workbook.Worksheets["WFSOutput"];

    for (int i = 1; i <= 100; i++)
    {
        _logger.LogWarning($"{column}{i} Before = {worksheet.Cells[$"{column}{i}"].Value}");                    
    }

    workbook.RefreshDynamicArrayFormulas(true, new CalculationOptions());

    var executionStartTimeTicks = DateTime.UtcNow.Ticks;
    workbook.CalculateFormula();
    _logger.LogWarning($"Total Execution Runtime (2) = {new TimeSpan(DateTime.UtcNow.Ticks - executionStartTimeTicks).TotalSeconds} seconds.");

    for (int i = 1; i <= 100; i++)
    {
        _logger.LogWarning($"{column}{i} After = {worksheet.Cells[$"{column}{i}"].Value}");                    
    }

    workbook.Save(saveFilePath);

I also strongly suggest that you change out the COUNTA() formulas for ROWS() as I specified as that will show the other issue I highlighted.

4

@braddixonorigin
By using the following sample code for testing on the latest version v24.5, we found that the output results are consistent with the Excel calculation results. Please refer to the attachment. result.zip (1.4 MB)

The sample code as follows:

using (var workbook = new Workbook(filePath + "WFS Output Formula (Split Out).xlsx"))
{
    int column = 0;
    var worksheet = workbook.Worksheets["WFSOutput"];

    for (int i = 1; i <= 100; i++)
    {                    
        Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $" Before = {worksheet.Cells[i, column].Value}");
    }

    workbook.RefreshDynamicArrayFormulas(true, new CalculationOptions());

    var executionStartTimeTicks = DateTime.UtcNow.Ticks;
    workbook.CalculateFormula();
    Console.WriteLine($"Total Execution Runtime (2) = {new TimeSpan(DateTime.UtcNow.Ticks - executionStartTimeTicks).TotalSeconds} seconds.");

    for (int i = 1; i <= 100; i++)
    {
        Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $"  After = {worksheet.Cells[i, column].Value}");
    }

    workbook.Save(filePath + "out_net.xlsx");

If you still have any questions, please provide more detailed information. If you could take a screenshot and highlight the errors, it would be very helpful for us to locate the issue. We will check it soon.

5

Thanks for checking up on it John, I, unfortunately, do not get the same result.

I too am on 24.5 so we can rule that out. Check out the screenshots I have attached, one shows the result from Aspose, the other the result from Excel when I recalculate the worksheet after opening the workbook that Aspose calculated and saved.

Calculation Results.zip (267.1 KB)
Calculation Log.png (221.4 KB)

I’m not sure if it matters but this is running on .NET 8.0 through an Azure Function.

6

@braddixonorigin
Hi, I was able to reproduce your issue with version 24.5 (.NET 8). However, I found that the new version, 24.6, resolves the problem. Therefore, please use version 24.6.
24.6(.net8).7z (676.1 KB)

7

Ok, thanks, I’ll check it out.

8

Ok, so that has fixed the issue overall but it hasn’t fixed the use of ROWS vs COUNTA. COUNTA works, ROWS does not, so if you try this …

=LET(
COMMENT1, " --- The following formulas are used for initialisation purposes and set the scene for the remaining calculations ---",
  _dataMatrix, LSLCalculation[[#All],[VarianceWithAccrualBankWeeks]:[VarianceWithOSWBankHours]],
  _dataMatrixWoHeaders, DROP(_dataMatrix, 1),
COMMENT2, " --- We now need to select the datasets so we can unpivot the data by date, by variance group (i.e. pay code) ---",
  _allDates, B1 + SEQUENCE(B2, 1, 0, 1),
  _varianceGroups, TRANSPOSE(CHOOSEROWS(_dataMatrix, 1)),
  _numberOfExplodedRows, ROWS(_allDates) * ROWS(_varianceGroups),
COMMENT3, " --- This is where we start building out each column for the resulting data set ---",
COMMENT4, " --- Firstly, build up the unpivoted references by row and column for each resulting record ---",
  _allRowsByWorkDate, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, MOD(r - 1, ROWS(_allDates)) + 1)),
  _allRowsByVarianceColumns, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, ROUNDUP(r / ROWS(_allDates), 0))),
  _allRowsByLookupMatrix, HSTACK(_allRowsByWorkDate, _allRowsByVarianceColumns),
_allRowsByLookupMatrix)

… over this …

=LET(
COMMENT1, " --- The following formulas are used for initialisation purposes and set the scene for the remaining calculations ---",
  _dataMatrix, LSLCalculation[[#All],[VarianceWithAccrualBankWeeks]:[VarianceWithOSWBankHours]],
  _dataMatrixWoHeaders, DROP(_dataMatrix, 1),
COMMENT2, " --- We now need to select the datasets so we can unpivot the data by date, by variance group (i.e. pay code) ---",
  _allDates, B1 + SEQUENCE(B2, 1, 0, 1),
  _varianceGroups, TRANSPOSE(CHOOSEROWS(_dataMatrix, 1)),
  _numberOfExplodedRows, COUNTA(_allDates) * COUNTA(_varianceGroups),
COMMENT3, " --- This is where we start building out each column for the resulting data set ---",
COMMENT4, " --- Firstly, build up the unpivoted references by row and column for each resulting record ---",
  _allRowsByWorkDate, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, MOD(r - 1, COUNTA(_allDates)) + 1)),
  _allRowsByVarianceColumns, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, ROUNDUP(r / COUNTA(_allDates), 0))),
  _allRowsByLookupMatrix, HSTACK(_allRowsByWorkDate, _allRowsByVarianceColumns),
_allRowsByLookupMatrix)

… if it’s anything like I’m seeing, you’ll see differences.

The problem appears to be here …

_numberOfExplodedRows, ROWS(_allDates) * ROWS(_varianceGroups)`

I’ve attached an image when I use ROWS. You can see, it’s vastly incorrect.

Using ROWS.png (23.8 KB)

9

Sorry to be throwing all of this at you but if I extend on the formula again (because what I’ve given you thus far is not the entire outcome) then I get another issue.

Not sure why but I’m getting #NAME? on this formula …

=LET(
COMMENT1, " --- The following formulas are used for initialisation purposes and set the scene for the remaining calculations ---",
  _startDate, DATEVALUE(XLOOKUP("ExecutionDate",CurrentPersonAndWorkDetails[Key],CurrentPersonAndWorkDetails[Value],,0,-1)),
  _daysOut, DATEDIF(_startDate, MAX(LSLCalculation[Date]), "d") + 1,
  _employeeID, XLOOKUP("EmployeeID",CurrentPersonAndWorkDetails[Key],CurrentPersonAndWorkDetails[Value],,0,1),
  _dataMatrix, LSLCalculation[[#All],[VarianceWithAccrualBankWeeks]:[VarianceWithOSWBankHours]],
  _dataMatrixWoHeaders, DROP(_dataMatrix, 1),
COMMENT2, " --- We now need to select the datasets so we can unpivot the data by date, by variance group (i.e. pay code) ---",
  _allDates, _startDate + SEQUENCE(_daysOut, 1, 0, 1),
  _varianceGroups, TRANSPOSE(CHOOSEROWS(_dataMatrix, 1)),
  _numberOfExplodedRows, COUNTA(_allDates) * COUNTA(_varianceGroups),
COMMENT3, " --- This is where we start building out each column for the resulting data set ---",
COMMENT4, " --- Firstly, build up the unpivoted references by row and column for each resulting record ---",
  _allRowsByWorkDate, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, INDEX(_allDates, MOD(r - 1, COUNTA(_allDates)) + 1))),
  _allRowsByVarianceColumns, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, CHOOSEROWS(_varianceGroups, ROUNDUP(r / COUNTA(_allDates), 0)))),
  _allRowsByLookupMatrix, HSTACK(_allRowsByWorkDate, _allRowsByVarianceColumns),
  _allRowsByLookupMatrixRC, MAKEARRAY(_numberOfExplodedRows, 2, LAMBDA(r,c, IF(c = 1, MATCH(INDEX(_allRowsByLookupMatrix, r, 1), LSLCalculation[Date], 0), MATCH(INDEX(_allRowsByLookupMatrix, r, 2), _varianceGroups, 0)))),
COMMENT5, " --- Build out each column and then HSTACK them all up to produce the file structure for OFlow ---",
  _allRowsByBlankCell, IF(SEQUENCE(_numberOfExplodedRows, 1), ""),
  _allRowsByEmployeeID, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, _employeeID)),
  _allRowsHoursLookup, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, INDEX(_dataMatrixWoHeaders, INDEX(_allRowsByLookupMatrixRC, r, 1), INDEX(_allRowsByLookupMatrixRC, r, 2)))),
  _allRowsByPayCodes, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, XLOOKUP(CHOOSEROWS(_varianceGroups, ROUNDUP(r / COUNTA(_allDates), 0)), VarianceToAdjPayCodeLookup[VarianceColumnName], VarianceToAdjPayCodeLookup[AssociatedPayCode]))),
  _unfilteredResult, HSTACK(_allRowsByEmployeeID, _allRowsByWorkDate, _allRowsByPayCodes, _allRowsByBlankCell, _allRowsByBlankCell, _allRowsHoursLookup),
COMMENT6, " --- Filter the result to remove any rows where the variance is 0 ---",
  _result, FILTER(_unfilteredResult, ABS(INDEX(_unfilteredResult,, 6)) >= 0.000001),
_allRowsHoursLookup)

… that (at this stage) is the entire formula that will generate the outcome I am after.

This row …

_allRowsHoursLookup, MAKEARRAY(_numberOfExplodedRows, 1, LAMBDA(r,c, INDEX(_dataMatrixWoHeaders, INDEX(_allRowsByLookupMatrixRC, r, 1), INDEX(_allRowsByLookupMatrixRC, r, 2))))

… is the one giving me grief. Eventually, I need to get down to the point where _result is the final variable being returned from the LET() function.

10

@braddixonorigin
By using sample files and code for testing, we can reproduce the problem. When using ROWS to replace COUNTA, the calculation result is incorrect. Please refer to the attachment. result.zip (1.3 MB)

The sample code as follows:

using (var workbook = new Workbook(filePath + "WFS Output Formula (Split Out)-ROWS.xlsx"))
{
    int column = 0;
    var worksheet = workbook.Worksheets["WFSOutput"];

    for (int i = 1; i <= 100; i++)
    {
        Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $" Before = {worksheet.Cells[i, column].Value}");
    }

    workbook.RefreshDynamicArrayFormulas(true, new CalculationOptions());

    var executionStartTimeTicks = DateTime.UtcNow.Ticks;
    workbook.CalculateFormula();
    Console.WriteLine($"Total Execution Runtime (2) = {new TimeSpan(DateTime.UtcNow.Ticks - executionStartTimeTicks).TotalSeconds} seconds.");

    for (int i = 1; i <= 100; i++)
    {
        Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $"  After = {worksheet.Cells[i, column].Value}");
    }

    workbook.Save(filePath + "out_net.xlsx");
}

The output:

A2 Before = Days Out
A3 Before = Employee ID
A4 Before =
A5 Before =
A6 Before =
A7 Before =
A8 Before = ROW_IDX
A9 Before = 1
A10 Before = 2
A11 Before = 3
A12 Before = 4
A13 Before = 5
A14 Before = 6
A15 Before = 7
A16 Before = 8
A17 Before = 9
A18 Before = 10
A19 Before = 11
A20 Before = 12
A21 Before = 13
A22 Before = 14
A23 Before = 15
A24 Before = 16
A25 Before = 17
A26 Before = 18
A27 Before = 19
A28 Before = 20
A29 Before = 21
A30 Before = 22
A31 Before = 23
A32 Before = 24
A33 Before = 25
A34 Before = 26
A35 Before = 27
A36 Before = 28
A37 Before = 29
A38 Before = 30
A39 Before = 31
A40 Before = 32
A41 Before = 33
A42 Before = 34
A43 Before = 35
A44 Before = 36
A45 Before = 37
A46 Before = 38
A47 Before = 39
A48 Before = 40
A49 Before = 41
A50 Before = 42
A51 Before = 43
A52 Before = 44
A53 Before = 45
A54 Before = 46
A55 Before = 47
A56 Before = 48
A57 Before = 49
A58 Before = 50
A59 Before = 51
A60 Before = 52
A61 Before = 53
A62 Before = 54
A63 Before = 55
A64 Before = 56
A65 Before = 57
A66 Before = 58
A67 Before = 59
A68 Before = 60
A69 Before = 61
A70 Before = 62
A71 Before = 63
A72 Before = 64
A73 Before = 65
A74 Before = 66
A75 Before = 67
A76 Before = 68
A77 Before = 69
A78 Before = 70
A79 Before = 71
A80 Before = 72
A81 Before = 73
A82 Before = 74
A83 Before = 75
A84 Before = 76
A85 Before = 77
A86 Before = 78
A87 Before = 79
A88 Before = 80
A89 Before = 81
A90 Before = 82
A91 Before = 83
A92 Before = 84
A93 Before = 85
A94 Before = 86
A95 Before = 87
A96 Before = 88
A97 Before = 89
A98 Before = 90
A99 Before = 91
A100 Before = 92
A101 Before = 93
Total Execution Runtime (2) = 0.0468756 seconds.
A2  After = Days Out
A3  After = Employee ID
A4  After =
A5  After =
A6  After =
A7  After =
A8  After = ROW_IDX
A9  After = 1
A10  After = 2
A11  After = 3
A12  After = 4
A13  After = 5
A14  After = 6
A15  After =
A16  After =
A17  After =
A18  After =
A19  After =
A20  After =
A21  After =
A22  After =
A23  After =
A24  After =
A25  After =
A26  After =
A27  After =
A28  After =
A29  After =
A30  After =
A31  After =
A32  After =
A33  After =
A34  After =
A35  After =
A36  After =
A37  After =
A38  After =
A39  After =
A40  After =
A41  After =
A42  After =
A43  After =
A44  After =
A45  After =
A46  After =
A47  After =
A48  After =
A49  After =
A50  After =
A51  After =
A52  After =
A53  After =
A54  After =
A55  After =
A56  After =
A57  After =
A58  After =
A59  After =
A60  After =
A61  After =
A62  After =
A63  After =
A64  After =
A65  After =
A66  After =
A67  After =
A68  After =
A69  After =
A70  After =
A71  After =
A72  After =
A73  After =
A74  After =
A75  After =
A76  After =
A77  After =
A78  After =
A79  After =
A80  After =
A81  After =
A82  After =
A83  After =
A84  After =
A85  After =
A86  After =
A87  After =
A88  After =
A89  After =
A90  After =
A91  After =
A92  After =
A93  After =
A94  After =
A95  After =
A96  After =
A97  After =
A98  After =
A99  After =
A100  After =
A101  After =

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-55998

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.

Regarding obtaining #NAME? after calculation, We were unable to reproduce the issue. Would you like to share your test file? We will check it soon.

11

Yeah, sure … I should’ve done that already.

WFS_Aspose.zip (643.8 KB)

Also, it was #VALUE sorry, not #NAME

12

@braddixonorigin
By using sample files and code for testing, we can reproduce the issue. Found complex array function formula returns #VALUE when calling CalculateFormula method. Please refer to the attachment. result.png (9.8 KB)

The sample code as follows:

using (var workbook = new Workbook(filePath + "WFS_Aspose.xlsx"))
{
    int column = 0;
    var worksheet = workbook.Worksheets["WFSOutput"];

    for (int i = 1; i <= 100; i++)
    {
        Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $" Before = {worksheet.Cells[i, column].Value}");
    }

    workbook.RefreshDynamicArrayFormulas(true, new CalculationOptions());

    var executionStartTimeTicks = DateTime.UtcNow.Ticks;
    workbook.CalculateFormula();
    Console.WriteLine($"Total Execution Runtime (2) = {new TimeSpan(DateTime.UtcNow.Ticks - executionStartTimeTicks).TotalSeconds} seconds.");

    for (int i = 1; i <= 100; i++)
    {
        Console.WriteLine(CellsHelper.CellIndexToName(i, column) + $"  After = {worksheet.Cells[i, column].Value}");
    }

    workbook.Save(filePath + "out_net.xlsx");
}

The output:

A2 Before = DUMMY
A3 Before = DUMMY
A4 Before = DUMMY
A5 Before = DUMMY
A6 Before = DUMMY
A7 Before = DUMMY
A8 Before = DUMMY
A9 Before = DUMMY
A10 Before = DUMMY
A11 Before = DUMMY
A12 Before = DUMMY
A13 Before = DUMMY
A14 Before = DUMMY
A15 Before = DUMMY
A16 Before = DUMMY
A17 Before = DUMMY
A18 Before = DUMMY
A19 Before = DUMMY
A20 Before = DUMMY
A21 Before = DUMMY
A22 Before = DUMMY
A23 Before = DUMMY
A24 Before = DUMMY
A25 Before = DUMMY
A26 Before = DUMMY
A27 Before = DUMMY
A28 Before = DUMMY
A29 Before = DUMMY
A30 Before = DUMMY
A31 Before = DUMMY
A32 Before = DUMMY
A33 Before = DUMMY
A34 Before = DUMMY
A35 Before = DUMMY
A36 Before = DUMMY
A37 Before = DUMMY
A38 Before = DUMMY
A39 Before = DUMMY
A40 Before = DUMMY
A41 Before = DUMMY
A42 Before = DUMMY
A43 Before = DUMMY
A44 Before = DUMMY
A45 Before = DUMMY
A46 Before = DUMMY
A47 Before = DUMMY
A48 Before = DUMMY
A49 Before = DUMMY
A50 Before = DUMMY
A51 Before = DUMMY
A52 Before = DUMMY
A53 Before = DUMMY
A54 Before = DUMMY
A55 Before = DUMMY
A56 Before = DUMMY
A57 Before = DUMMY
A58 Before = DUMMY
A59 Before = DUMMY
A60 Before = DUMMY
A61 Before = DUMMY
A62 Before = DUMMY
A63 Before = DUMMY
A64 Before = DUMMY
A65 Before = DUMMY
A66 Before = DUMMY
A67 Before = DUMMY
A68 Before = DUMMY
A69 Before = DUMMY
A70 Before = DUMMY
A71 Before = DUMMY
A72 Before = DUMMY
A73 Before = DUMMY
A74 Before = DUMMY
A75 Before = DUMMY
A76 Before = DUMMY
A77 Before = DUMMY
A78 Before = DUMMY
A79 Before = DUMMY
A80 Before = DUMMY
A81 Before = DUMMY
A82 Before = DUMMY
A83 Before = DUMMY
A84 Before = DUMMY
A85 Before = DUMMY
A86 Before = DUMMY
A87 Before = DUMMY
A88 Before = DUMMY
A89 Before = DUMMY
A90 Before = DUMMY
A91 Before = DUMMY
A92 Before = DUMMY
A93 Before = DUMMY
A94 Before = DUMMY
A95 Before = DUMMY
A96 Before = DUMMY
A97 Before = DUMMY
A98 Before = DUMMY
A99 Before = DUMMY
A100 Before = DUMMY
A101 Before = DUMMY
Total Execution Runtime (2) = 0.1877724 seconds.
A2  After = #VALUE!
A3  After =
A4  After =
A5  After =
A6  After =
A7  After =
A8  After =
A9  After =
A10  After =
A11  After =
A12  After =
A13  After =
A14  After =
A15  After =
A16  After =
A17  After =
A18  After =
A19  After =
A20  After =
A21  After =
A22  After =
A23  After =
A24  After =
A25  After =
A26  After =
A27  After =
A28  After =
A29  After =
A30  After =
A31  After =
A32  After =
A33  After =
A34  After =
A35  After =
A36  After =
A37  After =
A38  After =
A39  After =
A40  After =
A41  After =
A42  After =
A43  After =
A44  After =
A45  After =
A46  After =
A47  After =
A48  After =
A49  After =
A50  After =
A51  After =
A52  After =
A53  After =
A54  After =
A55  After =
A56  After =
A57  After =
A58  After =
A59  After =
A60  After =
A61  After =
A62  After =
A63  After =
A64  After =
A65  After =
A66  After =
A67  After =
A68  After =
A69  After =
A70  After =
A71  After =
A72  After =
A73  After =
A74  After =
A75  After =
A76  After =
A77  After =
A78  After =
A79  After =
A80  After =
A81  After =
A82  After =
A83  After =
A84  After =
A85  After =
A86  After =
A87  After =
A88  After =
A89  After =
A90  After =
A91  After =
A92  After =
A93  After =
A94  After =
A95  After =
A96  After =
A97  After =
A98  After =
A99  After =
A100  After =
A101  After =

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-55999

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.

13

Thanks John, that’s a good outcome.

Based on the policies, I know there are no promises but any thoughts on when you think a fix will be supplied?

14

@braddixonorigin
Since your issue was logged today (just a few hours ago), please allow us some time to evaluate and analyze it in detail. If it is not complex, we should be able to fix it soon, and the fix may be included in our upcoming release (Aspose.Cells v24.7 - planned for publication in the second week of July next month). If the issue is complex, it may take a few more weeks or even a month to resolve.

Additionally, if you purchase paid support, we will prioritize your issue so that it can be resolved earlier.

15

Thanks John, I completely understand.

Question though, if you guys manage to work out the issue and get a fix prior to the official release of 24.7, is there any way to get a beta copy for testing purposes?

I get that paying for support would prioritise but regardless, we’re on a fairly tight project timeline and I need to cover all bases here. Thanks!

16

@braddixonorigin,

We cannot provide interim fixes/releases in normal (free) support here. You can obtain Paid Support Services if you need to support your issue on a priority basis and to get quick fix.

17

@braddixonorigin
The issue of calculating this complex array function is a bit complicated and we need some time to solve it. With free support we are afraid we cannot provide hot fix. However, if it is possible, you may provide us your cases(runnable project with required resources such as template files), once we fix the issue, we will test it and provide you the result for verification. Hope this way can help you to manage your timeline.

18

Yeah, ok, understood. Also, I’ve provided a workbook and the basic code so it’s really that simple.

There are other worksheets and 1000’s of other formulas that need to be resolved but I can’t provide that easily for a number of reasons. I think you guys have enough to work with for now so I’ll just wait to hear back when you have something to share.

19

@braddixonorigin
Thank you for your feedback. Once there are updates, we will notify you promptly.

1 Like
20

@braddixonorigin,

We are pleased to inform you that your issue (“CELLSNET-55999”) has been resolved. The fix/enhancement will be included in our upcoming release (Aspose.Cells v24.7) which is due in the second week of July this month. You will be notified when the next version is released.

21

@braddixonorigin,

We have resolved your reported issue (previously logged as “CELLSNET-55998”) as well. The fix/enhancement will be incorporated in the upcoming release (Aspose.Cells v24.7) scheduled for this week. You will receive a notification upon the release of the next version.

22

The issues you have found earlier (filed as CELLSNET-55998,CELLSNET-55999) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi