SUMPRODUCT(INDEX(...):INDEX(...)-..) formula returns #VALUE! in XLSB format

Aspose.Cells Product Family
1
Hello,

This formula works when it is saved in the .xlsx format, but returns #VALUE! when saved in the .xlsb format:
SUMPRODUCT(--(INDEX(A1:A3,1,1):INDEX(A1:A3,3,1)), B1:B3, C1:C3)

It looks like INDEX(A1:A3,1,1):INDEX(A1:A3,3,1) returns #VALUE! only in SUMPRODUCT(), but it works ok in SUMIF().


If I open the Aspose created .xlsb file in Excel, highlight the cell with the error, click the formula bar, and hit enter without changing the formula then the error goes away.

Aspose is able to open and save an Excel created .xlsb file with that formula without introducing the #VALUE! error. So it seem the problem only happens when the formula is set from within Aspose.

I have tested this with the latest version of Aspose (7.4.1).

Thanks,
Brian

Here is some sample code:

Workbook w = new Workbook();
var cells = w.Worksheets[0].Cells;

// Returns #VALUE! in xlsb
// Works in xlsx
cells[0, 5].Formula = "=SUMPRODUCT(--(INDEX(A1:A3,1,1):INDEX(A1:A3,3,1)), B1:B3, C1:C3)";

// Works in xlsb
// Works in xlsx
cells[1, 5].Formula = "=SUMPRODUCT(--(A1:A3), B1:B3, C1:C3)";

// Works in xlsb
// Works in xlsx
cells[2, 5].Formula = "=SUMIF(INDEX(A1:A3,1,1):INDEX(A1:A3,3,1), TRUE, B1:B3)";

cells[0, 0].Value = true;
cells[1, 0].Value = false;

cells[0, 1].Value = 1;
cells[1, 1].Value = 2;

cells[0, 2].Value = 6;
cells[1, 2].Value = 7;

w.CalculateFormula();

// SUMPRODUCT() formulas get calculated correctly by Aspose xlsx and xlsb
cells[0, 6].Value = cells[0, 5].Value;
cells[1, 6].Value = cells[1, 5].Value;
cells[2, 6].Value = cells[2, 5].Value;


// *** INDEX():INDEX in SUMPRODUCT() causes "#VALUE!" ***
// B1:B3 in SUMPRODUCT() works
// INDEX():INDEX in SUMIF() works
w.Save("test.xlsb", SaveFormat.Xlsb);

// INDEX():INDEX in SUMPRODUCT() works
// B1:B3 in SUMPRODUCT() works
// INDEX():INDEX in SUMIF() works
w.Save("test.xlsx", SaveFormat.Xlsx);
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We were able to replicate this issue using the latest version. We will look into this issue and fix it.

Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41443.

3

Hi Shakeel,


Thanks for the quick response.

Do you have an idea of when a fix will be available?
Unfortunately we are on a tight deadline.

Thanks,
Brian
4

Hi,

Thanks for your posting and using Aspose.Cells.

Please spare us some time. Once, the issue is fixed, we will let you know asap.

5

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have fixed the issue.

Please download and try this fix: Aspose.Cells for .NET v7.4.1.1 and let us know your feedback.

6

Thanks for creating a new version so quickly!


I see the bug showing up in sample formula I provided has been fixed.

I simplified that sample formula down to what I thought was the root bug, but I am using a more complex formula in my project. It appears that there are additional issues with SUMPRODUCT in an XSLB file.

As with the first bug, none of these errors occur when I save as an XSLX file, and if I highlight any of the formulas producing an error and then hit enter key the error goes away.

Below are the issues I found broken down to the simplest cases I could reproduce them with.
In the attached c# file I’ve also included the full formula I am using in my project - which works correctly in XSLX.


The INDEX():INDEX() bug has been fixed, but only when it’s the 1st parameter in a SUMPRODUCT(). If the INDEX():INDEX() is the 2nd or later parameter it results in “#VALUE!“
cells[“A3”].Formula = “=SUMPRODUCT(” +
“–(INDEX(RangeA,1,1):INDEX(RangeA,3,1)),” +
“INDEX(RangeB,1,1):INDEX(RangeB,3,1),” +
“RangeC)”;

The COLUMN() and ROW() functions in an INDEX() in SUMPRODUCT() results in “#VALUE!”
cells[“A2”].Formula = “=SUMPRODUCT(” +
“–(INDEX(RangeA,COLUMN(),1):INDEX(RangeA,3,1)),” +
“RangeB,” +
“RangeC)”;

Using the formula (which was fixed in the new version) works when setting the Formula property, but when it is used in SetSharedFormula it results in “#VALUE!”
cells[“A2”].SetSharedFormula(”=SUMPRODUCT(” +
“–(INDEX(J1:J3,1,1):INDEX(J1:J3,3,1)),” +
“K1:K3,” +
“L1:L3)”,
5, 1);

IFERROR() results in #NAME?
cells[“A2”].Formula = @"="“It’s turtles all the way”" & IFERROR(1/0, “” down"")";


Thanks,
Brian
7

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

The code you provided is quite complex. Please provide us simple sample runnable console project to investigate this issue further.

We will look into it and help you asap.

8

Hi Mshakeel,


I’ve attached a zip file with a runnable project in it, and I’ve included the output of the program in the “Sample Output” folder.

The only code included in the previous file I sent, and the code attached to this message, is directly related to demonstrating the 4 bugs I found. Each bug is in its own method to help organize the examples.

The example formulas needed to demonstrate the bugs require a certain amount of data population and named ranges to reproduce them. Much of the source code is dedicated to setting up the inputs to the formulas demonstrating the bugs. That is why those lines are included in the source code.

Since I included multiple instances of the bugs occurring, I put a comment in the “B” column describing the occurrence, and what that particular instance is attempting to demonstrate.

The fix in version 7.4.1.1 did not resolve all cases of the bug I first reported, so I’ve included an example case of where the bug was fixed and an example case of where the bug is still occurring. If possible for each additional bug I found, I also included more than 1 example of the bug occurring to try to help in fixing different occurrences of the bug.

For example, the fix in 7.4.1.1 resolved the original bug when an INDEX() is inside a SUMPRODUCT() when the the INDEX() is the first parameter of the SUMPRODUCT(). But, the bug still occurs when INDEX() is the 2nd, 3rd, … parameter of the SUMPRODUCT().

To try to show this in a clear way in my example code I included 2 formulas with comments indicating what the example is showing.

For example:

// Fixed
// INDEX():INDEX() in 1st parameter
cells[“A1”].Formula = “=SUMPRODUCT(” +
“–(INDEX(RangeA,1,1):INDEX(RangeA,3,1)),” +
“RangeB,” +
“RangeC)”;

// *** Error ***
// INDEX():INDEX() in 2st parameter
cells[“A2”].Formula = “=SUMPRODUCT(” +
“–(RangeA),” +
“INDEX(RangeB,1,1):INDEX(RangeB,3,1),” +
“RangeC)”;

I thought it was helpful to show the case where the bug was fixed, and a case where it wasn’t fixed.

For each bug I found I tried to narrow the example down to the simplest formula that would reproduce the bug. Since some of the simplest formulas I could come up with to demonstrate the bugs were still moderately complex, I put an example of a formula which worked correctly, and tried to find the minimal change I could make to the formula to trigger the bug.

If you would like to remove the example formulas that show cases that work correctly and remove the population of the “B” column with comments describing each example case, please uncomment this line at the top of the file: #define ONLY_POPULATE_ERRORS

I included on the last tab one of the larger formulas I am using in my project. The reason I included the entire formula is to provide an indicator as to if the bugs I submitted were fixed in a more complex case. I am trying to avoid a back and forth of cases where the bugs are still occurring that might not have been triggered in my simplified examples.

Please let me know if you have any other questions.

Thanks,
Brian
9

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We were able to find this issue after running your project. We have logged this issue in our database. Once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41458.

10

Hi,

Please try the attached fix/version: Aspose.Cells for .NET v7.4.1.2,

And, please create the workbook instance with the following
codes:

Workbook wb = new Workbook(FileFormatType.Xlsx);

Thank you.

11
Version 7.4.1.2 looks like it solved all the issues I was having

Thanks for posting a fix so quickly!

Brian
12

Hi,


Thanks for your feedback.

Good to know that your issues are resolved now.
We have closed your issues.

Feel free to contact us any time if you have further queries or issues, we will be happy to assist you.

Thank you.

13

The issues you have found earlier (filed as CELLSNET-41443) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.