Using Array Formulas

Use an array formula to make your worksheet more powerful and simplify it at the same time. The following examples illustrate how you might use array formulas.

An array formula can do calculations on rows and columns of cells where you might otherwise need to use several formulas. For example: You can use an array formula to help search range items. For example: An array formula can help collect statistics information from related range items. For example:
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 3 }); initSpread(spread); }; function initSpread(spread) { var spreadNS = GC.Spread.Sheets; var fbx = new spreadNS.FormulaTextBox.FormulaTextBox(document.getElementById('formulaBar')); fbx.workbook(spread); var oldArrayRange; var arrayFormulaStyle = new spreadNS.Style(); arrayFormulaStyle.backColor = "#E0E0FF"; for (var i = 0; i < spread.sheets.length; i++) { (function (sheet1) { sheet1.bind(spreadNS.Events.SelectionChanged, null, function (args) { sheet1.suspendPaint(); var formulaBar = document.getElementById("formulaBar"); var formulaInfo = sheet1.getFormulaInformation(sheet1.getActiveRowIndex(), sheet1.getActiveColumnIndex()); if (oldArrayRange && (!formulaInfo || !formulaInfo.isArrayFormula || !formulaInfo.baseRange.equals(oldArrayRange))) { for (var r = oldArrayRange.row; r < oldArrayRange.row + oldArrayRange.rowCount; r++) { for (var c = oldArrayRange.col; c < oldArrayRange.col + oldArrayRange.colCount; c++) { sheet1.setStyle(r, c, null); } } oldArrayRange = null; } if (formulaInfo && formulaInfo.hasFormula) { var formula = "=" + formulaInfo.formula; formula = formulaInfo.isArrayFormula ? "{" + formula + "}" : formula; formulaBar.value=formula; if (formulaInfo.isArrayFormula && !formulaInfo.baseRange.equals(oldArrayRange)) { oldArrayRange = formulaInfo.baseRange; for (var r = oldArrayRange.row; r < oldArrayRange.row + oldArrayRange.rowCount; r++) { for (var c = oldArrayRange.col; c < oldArrayRange.col + oldArrayRange.colCount; c++) { sheet1.setStyle(r, c, arrayFormulaStyle); } } } } else { formulaBar.value=sheet1.getValue(sheet1.getActiveRowIndex(), sheet1.getActiveColumnIndex()); } sheet1.resumePaint(); }); })(spread.sheets[i]); } setCalculation(spread.sheets[0]); setSearch(spread.sheets[1]); setStatistics(spread.sheets[2]); } function setCalculation(sheet) { sheet.name("Calculation"); sheet.setArray(0, 0, [ ["", "Match", "Physical", "Chemistry"], ["Alice", 97, 61, 53], ["Jon", 65, 76, 65], ["Bob", 55, 70, 64], ["Jack", 89, 77, 73], ["Tom", 83, 62, 78], ["Robert", 86, 98, 57] ]); sheet.addSpan(0, 5, 1, 4); sheet.setValue(0, 5, "Calculate every body's total score"); sheet.setArrayFormula(1, 5, 6, 1, "=SUBTOTAL(9,OFFSET($B$2,ROW($A$1:$A$6)-ROW($A$1),,1,3))"); } function setSearch(sheet) { sheet.name("Search"); sheet.setArray(0, 0, [ ["apple", "apple"], ["banana", "strawberry"], ["pea", "potato"], ["tomato", "potato"], ["potato", "dumpling"], ["cake"], ["noodle"], ["rice"], ["soup"] ]); sheet.addSpan(0, 3, 1, 8); sheet.setValue(0, 3, "Find out the first value on B1:B5 that does not contains on A1:A9"); sheet.setColumnWidth(3, 80); sheet.addSpan(2, 3, 1, 2); sheet.setValue(2, 3, "ArrayFormula Result:"); sheet.setArrayFormula(2, 5, 1, 1, "=INDEX(B1:B5,MATCH(TRUE,ISNA(MATCH(B1:B5,A1:A9,0)),0))"); sheet.getCell(2, 5).backColor("Accent 5 80"); sheet.addSpan(3, 3, 1, 2); sheet.setValue(3, 3, "NormalFormula Result:"); sheet.setFormula(3, 5, "=INDEX(B1:B5,MATCH(TRUE,ISNA(MATCH(B1:B5,A1:A9,0)),0))"); sheet.getCell(3, 5).backColor("Accent 6 80"); } function setStatistics(sheet) { sheet.name("Statistics"); sheet.options.allowCellOverflow = true; sheet.setArray(0, 0, [ ["Product", "Salesman", "Units Sold"], ["Fax", "Brown", 1], ["Phone", "Smith", 10], ["Fax", "Jones", 20], ["Fax", "Smith", 30], ["Phone", "Jones", 40], ["PC", "Smith", 50], ["Fax", "Brown", 60], ["Phone", "Davis", 70], ["PC", "Jones", 80] ]); sheet.setValue(1, 5, "Summing Sales: Faxes Sold By Brown"); sheet.setArrayFormula(2, 5, 1, 1, "=SUM((A2:A10=\"Fax\")*(B2:B10=\"Brown\")*(C2:C10))"); sheet.setValue(4, 5, "Logical AND (Faxes And Brown)"); sheet.setArrayFormula(5, 5, 1, 1, "=SUM((A2:A10=\"Fax\")*(B2:B10=\"Brown\"))"); sheet.setValue(7, 5, "Logical OR (Faxes Or Jones)"); sheet.setArrayFormula(8, 5, 1, 1, "=SUM(IF((A2:A10=\"Fax\")+(B2:B10=\"Jones\"),1,0))"); sheet.setValue(10, 5, "Logical XOR (Fax Or Jones but not both)"); sheet.setArrayFormula(11, 5, 1, 1, "=SUM(IF(MOD((A2:A10=\"Fax\")+(B2:B10=\"Jones\"),2),1,0))"); sheet.setValue(13, 5, "Logical AND (All Sales Except Fax And Jones)"); sheet.setArrayFormula(14, 5, 1, 1, "=SUM(IF((A2:A10=\"Fax\")+(B2:B10=\"Jones\")<>2,1,0))"); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets" ></div> <div class="options-container"> <textarea id="formulaBar" rows="10" readonly></textarea> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } textarea { width: 100%; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }