Array formula in Excel

In Excel, you can write a formula like this =MEDIAN(IF(‘Data’!B2:B20=A2, ‘Data’!C2:C20))



and do a Shirt+Ctrl+Enter and it will make it an array but putting {} around the formla. Can this be done in Aspose.Cells? or an equivalent to it?



Additional information. The formula is stored in database and writing to Excel.



I’m familiar with the cell.formula = someformula and I also saw the cell.setarrayformula(someformula, row, col). Now, I only want to write the array formula in the first cell and then use the sharedformula to copy them across a range. The function I’ve created is generic and doesn’t know if the formula is an array or not. If I write the formula above using the cell.formula, can I use the cell.isarray to see if it’s an array formula? or do I need to enclose them in {} when I assign the cell.formula?

Hi,


Well, you may use Cell.SetArrayFormula() method to specify an array formula, see the document for your reference:
http://www.aspose.com/docs/display/cellsnet/Using+Formulas+or+Functions+to+Process+Data#UsingFormulasorFunctionstoProcessData-array

PS. Also, you may use Cell.IsInArray boolean attribute to know if a formula is an array formula or not.

Thank you.

When I tried that, it copied the same formula across all the cell. I’m trying to use the sharedformula with the array.

Hi,


It should work the same way as MS Excel does (when you manually perform the task). If you find anything different, kindly provide sample Excel file and paste your sample code (runnable) to reproduce the issue on our end, we will check it soon.

Thank you.

private static void WriteFormula(Workbook ExcelWK, string wsName, string Startcell, string Endcell, string ExcelFormula, bool isArray)

{

if (ExcelWK != null)

{

try

{

Worksheet ws = ExcelWK.Worksheets[wsName];

if (ws != null)

{

int startrow = 0;

int startcol = 0;

CellsHelper.CellNameToIndex(Startcell, out startrow, out startcol);

Cell cell = ws.Cells[Startcell];

cell.Formula = ExcelFormula;

if(isArray)

cell.SetArrayFormula(ExcelFormula, 1, 1);

int row = 0;

int col = 0;

CellsHelper.CellNameToIndex(Endcell, out row, out col);

int totalrows = 0;

int totalcols = 0;

if (startcol == col)

totalcols = 1;

else

totalcols = col - startcol + 1;

totalrows = row - startrow + 1;



ws.Cells[cell.Name].SetSharedFormula(cell.Formula, totalrows, totalcols);

ExcelWK.CalculateFormula();



}

}

When I do the above, the formula that gets copy across the range isn’t the array version and just the plan standard formula. I think if there’s an overload to specify the cell object, instead of the formula to be copy across the cell, then it should keep the actual formula whether it’s standard or array.

Hi,


Did you try to extend the rowNumber and colNumber parameters accordingly in the SetArrayFormula() method to directly accomplish your task:
e.g
Sample code:

cell.SetArrayFormula(ExcelFormula, totalrows, totalcols);

Thank you.

I did and it didn’t update the reference in the formula like the sharedformula would have done.

Hi,


Thanks for the details.

We observed the issue, the SetArrayFormula sets the static formulas (without updating references in the formula cells) for the range when using your mentioned formula via Aspose.Cells. We will look into it and get back to you soon.

Thank you.
Hi,

We still think Aspose.Cells does work the same way as MS Excel regarding ArrayFormulas. Could you create an Excel file in which you may set your desired array formula set in a range of cells manually, save the file and post us here to evaluate your issue on our end.

Thank you.

Here’s a sample of the chart.

Hi,


Thanks for the template file.

After an initial test, I observed the issue while testing your scenario/ case, the Cell.SetArrayFormula() sets the static formulas (without updating references in the formula cells). I used the following sample code with your sample file:
e.g
Sample code:

Workbook excelWorkbook = new Workbook(“e:\test2\Median_CT.xlsx”);
excelWorkbook.Worksheets[1].Cells[“B16”].SetArrayFormula("=MEDIAN(IF(Sheet1!$A:$A = $A16, Sheet1!$C:$C))", 20, 1);
excelWorkbook.Save(“e:\test2\out1.xlsx”);

It sets constant “=MEDIAN(IF(Sheet1!$A:$A=$A16,Sheet1!$C:$C)” array formula to all the cells in the range.

I have logged a ticket with an id “CELLSNET-42746” for your issue. We will look into your issue soon.

Once we have any update on it, we will let you know here.

Thank you.

Hi,

We have evaluated your issue further.

  1. Well, MS Excel will pop-up a message that “Mutli-Cell array formulas are not allowed in the tables.” if you set the array formulas to the range of cells. We will look into whether Aspose.Cells should throw such exception too when setting the array formulas to a range of cells in the tables.

  2. The reference in the array formulas must be fixed to figure it out. In your template file, each cell in the range e.g. B2:B15 contains different array formula in your template file.

If you want to get the same results as MS Excel, please try the following codes:

e.g.

Sample code:

Workbook excelWorkbook = new Workbook("e:\\test2\\Median_CT.xlsx");

excelWorkbook.Worksheets[1].Cells["B16"].SetArrayFormula("=MEDIAN(IF(Sheet1!$A:$A = $A16, Sheet1!$C:$C))", 1, 1);

for (int i = 1; i < 20; i++)

{

excelWorkbook.Worksheets[1].Cells[15 + i, 1].Copy(excelWorkbook.Worksheets[1].Cells["B16"]);

}

excelWorkbook.Save("e:\\test2\\out1.xlsx");

I have tested using Copying cells, it works fine here.

Thank you.

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan