How to Work with Array Formulas

• Jun 21, 2022
• 3 minutes to read

The Spreadsheet and Report Designer controls provide the capability to perform simultaneous operations with multiple data sets using the array versions of common arithmetic functions. Additionally, there are specialized built-in functions designed to work only with cell data arrays. Refer to the Array Formulas topic for more information on array formulas.

To learn how to work with array formulas, refer to one of the following task-related topics:

• How to Create Array Formulas Programmatically;

• End-User Array Formula Management Options.

How to Create Array Formulas Programmatically

Call the Table View worksheet’s AddArrayFormula procedure instead of the cell object’s SetText procedure to enter an array formula into a worksheet. The AddArrayFormula procedure requires specifying a cell range (as a TRect value) in which the calculation’s result is stored.

The following code example randomly generates two identically-sized two-dimensional arrays and uses them as arguments for the MMULT function:

Delphi

var
  ASheet: TdxSpreadSheetTableView;
  ACell: TdxSpreadSheetCell;
  AArrayResultArea: TRect;
  I, J: Integer;  // Cycle counters
//...
  ASheet := dxSpreadSheet1.ActiveSheetAsTable;
  AArrayResultArea := Rect(4, 0, 6, 2);  // Defines the cell range for storing an array of calculation results
//...
  Randomize();
  for I := 0 to 5 do  // Cycles through rows
    begin
      for J := 0 to 2 do  // Cycles through columns
        begin
          ACell := ASheet.CreateCell(I, J);  // Creates a cell object
          ACell.AsVariant := random(1000);  // Assigns a random value between 0 and 1000 to a newly created cell object
        end;
    end;
// Assigns the array formula to the resulting cell range
  ASheet.AddArrayFormula('=MMULT(A1:C3, A4:C6'), AArrayResultArea);

C++

TdxSpreadSheetTableView *ASheet;
  TdxSpreadSheetCell *ACell;
  TRect AArrayResultArea;
//...
  ASheet = dxSpreadSheet1->ActiveSheetAsTable;
  AArrayResultArea = Rect(4, 0, 6, 2);  // Defines the cell range for storing an array of calculation results
//...
  Randomize();
  for(int I = 0; I < 6; I++)  // Cycles through rows
  {
    for(int J = 0; J < 3; J++)  // Cycles through columns
    {
      ACell = ASheet->CreateCell(I, J);  // Creates a cell object
      ACell->AsVariant = random(1000);  // Assigns a random value between 0 and 1000 to a newly created cell object
    }
  }
// Assigns the array formula to the resulting cell range
  ASheet.AddArrayFormula("=MMULT(A1:C3, A4:C6"), AArrayResultArea);

The code execution result is shown here:

Pass the worksheet’s Selection.Area property value as the AddArrayFormula procedure’s AArea parameter to use the selected cell range as the array formula result area instead of the calculated or predefined area.

End-User Array Formula Management Options

Entering a single result array formula into a cell is very similar to using a usual formula. To do so, type the required formula expression within an in-place cell editor and confirm the entry by pressing the Ctrl+Shift+Enter key combination instead of the Enter key.

Select the cell range for storing the result array to enter an array formula returning multiple results, type the required formula expression into the master cell within the selection, and confirm the entry by pressing the Ctrl+Shift+Enter key combination.

Pressing the Enter key instead of the Ctrl+Shift+Enter key combination results in entering a single result instead of a multi-cell array formula.

To modify or delete multi-cell array formulas, invoke the master cell’s in-place editor, then change or erase its content, and finish editing by pressing the Ctrl+Shift+Enter key combination. Pressing the Enter key instead raises the EdxSpreadSheetCannotChangePartOfArrayError exception: