Spread Windows Forms 18 Product Documentation

Using the Array Formula

You can use array formulas in Spread.

An array formula is a formula that can perform multiple calculations on one or more items in an array. Array formulas can return multiple results or a single result.

Use Ctrl + Shift + Enter to create an array formula after entering the formula at run time if the users are allowed to create formulas, or you can use the SetFormulaArray method.

The following image displays an array formula in the Total column.

Example of Array Formula

Using Code

The following example creates an array formula.

fpSpread1.ActiveSheet.ColumnHeader.Cells[0, 0].Text = "Units";
fpSpread1.ActiveSheet.ColumnHeader.Cells[0, 1].Text = "Price";
fpSpread1.ActiveSheet.ColumnHeader.Cells[0, 2].Text = "Total";
fpSpread1.ActiveSheet.Cells[0, 0].Value = 2;
fpSpread1.ActiveSheet.Cells[1, 0].Value = 5;
fpSpread1.ActiveSheet.Cells[2, 0].Value = 1;
fpSpread1.ActiveSheet.Cells[3, 0].Value = 9;
fpSpread1.ActiveSheet.Cells[0, 1].Value = 40;
fpSpread1.ActiveSheet.Cells[1, 1].Value = 100;
fpSpread1.ActiveSheet.Cells[2, 1].Value = 25;
fpSpread1.ActiveSheet.Cells[3, 1].Value = 80;
fpSpread1.AllowUserFormulas = true;
fpSpread1.ActiveSheet.Cells[0, 2, 3, 2].FormulaArray = "A1:A4*B1:B4";

fpSpread1.ActiveSheet.ColumnHeader.Cells(0, 0).Text = "Units"
fpSpread1.ActiveSheet.ColumnHeader.Cells(0, 1).Text = "Price"
fpSpread1.ActiveSheet.ColumnHeader.Cells(0, 2).Text = "Total"
fpSpread1.ActiveSheet.Cells(0, 0).Value = 2
fpSpread1.ActiveSheet.Cells(1, 0).Value = 5
fpSpread1.ActiveSheet.Cells(2, 0).Value = 1
fpSpread1.ActiveSheet.Cells(3, 0).Value = 9
fpSpread1.ActiveSheet.Cells(0, 1).Value = 40
fpSpread1.ActiveSheet.Cells(1, 1).Value = 100
fpSpread1.ActiveSheet.Cells(2, 1).Value = 25
fpSpread1.ActiveSheet.Cells(3, 1).Value = 80
fpSpread1.AllowUserFormulas = True
fpSpread1.ActiveSheet.Cells[0, 2, 3, 2].FormulaArray = "A1:A4*B1:B4"

Using the Array Formula

Using Code

See Also