AGGREGATE Functions

The AGGREGATE function supports 19 aggregate calculation and function options.

SpreadJS supports the AGGREGATE function to do aggregate operations. The syntax is same as Excel. The first argument is the function number (1-19) which indicate the function used. Function numberFunction 1AVERAGE 2COUNT 3COUNTA 4MAX 5MIN 6PRODUCT 7STDEV.S 8STDEV.P 9SUM 10VAR.S 11VAR.P 12MEDIAN 13MODE.SNGL 14LARGE 15SMALL 16PERCENTILE.INC 17QUARTILE.INC 18APERCENTILE.EXC 19QUARTILE.EXC The second argument specify the options to ignore values in the calculation. OptionBehavior 0 or omittedIgnore nested SUBTOTAL and AGGREGATE functions 1Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions 2Ignore error values, nested SUBTOTAL and AGGREGATE functions 3Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions 4Ignore nothing 5Ignore hidden rows 6Ignore error values 7Ignore hidden rows and error values With function number 1-13, the rest arguments is reference to a range of cells. Function number 14-19 needs an array formula or a reference to a range of cells as third argument, and k to specify the function's argument.
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { var salesData = [ ["Salesperson", "Birthdate", "Region", "SaleAmount", "Commission%", "CommissionAmount"], ["Joe", new Date("2000/01/23"), "North", 260, 0.1, 26], ["Robert", new Date("1988/08/21"), "South", 660, 0.15, 99], ["Michelle", new Date("1995/08/03"), "East", 940, 0.15, 141], ["Erich", new Date("1994/05/23"), "West", '', 0, 49.2], ["Dafna", new Date("1992/07/21"), "North", 800, 0.15, 120], ["Rob", new Date("1995/11/03"), "South", 900, 0.15, 135], ["Jonason", new Date("1987/02/11"), "West", 300, 0.17, 110], ["Enana", new Date("1997/04/01"), "West", 310, 0.16, 99.2], ["Robin", new Date("1991/12/28"), "East", 450, 0.18, 35], ["Dania", new Date("1997/02/15"), "North", 500, 0.10, 76] ]; var sheet = spread.getSheet(0); sheet.suspendPaint(); sheet.setArray(8, 1, salesData); sheet.setFormula(12, 6, "=E13*F13"); var filter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(9, 1, salesData.length - 1, salesData[0].length)); sheet.rowFilter(filter); var condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, { compareType: GC.Spread.Sheets.ConditionalFormatting.TextCompareType.contains, expected: "th" }); filter.addFilterItem(3, condition); filter.filter(3); sheet.setText(0, 2, "Filter the data and check the formula result changes."); sheet.setArray(2, 2, [["Ignore hidden rows", "SUM CommissionAmount"], ["", "MAX CommissionAmount"], ["", ""], ["Ignore error", "SUM CommissionAmount"], ["", "MAX CommissionAmount"]]); sheet.setFormula(2, 4, "=AGGREGATE(9,5,G10:G19)"); sheet.setFormula(3, 4, "=AGGREGATE(14,5,G10:G19, 1)"); sheet.setFormula(5, 4, "=AGGREGATE(9,6,G10:G19)"); sheet.setFormula(6, 4, "=AGGREGATE(14,6,G10:G19, 1)"); sheet.setFormula(2, 5, "=FORMULATEXT(E3)"); sheet.setFormula(3, 5, "=FORMULATEXT(E4)"); sheet.setFormula(5, 5, "=FORMULATEXT(E6)"); sheet.setFormula(6, 5, "=FORMULATEXT(E7)"); sheet.getRange(9, 5, 10, 1).formatter(new GC.Spread.Formatter.GeneralFormatter("0.00%")); sheet.getRange(8, 1, 1, 6).backColor('rgb(219,225,240)'); initStyle(sheet); sheet.resumePaint(); } function initStyle(sheet) { sheet.defaults.colWidth = 100; sheet.setColumnWidth(2, 140); sheet.getRange(9, 2, 10, 1).formatter("mm-dd-yyyy"); sheet.setColumnWidth(3, 160); sheet.setColumnWidth(5, 160); sheet.setColumnWidth(6, 160); sheet.addSpan(2, 2, 2, 1); sheet.addSpan(5, 2, 2, 1); var lineStyle = GC.Spread.Sheets.LineStyle.thin; var sheetArea = GC.Spread.Sheets.SheetArea.viewport; var lineBorder = new GC.Spread.Sheets.LineBorder('orange', lineStyle); sheet.getRange(0, 2, 1, 3).setBorder(lineBorder, { outline: true }, sheetArea); lineBorder = new GC.Spread.Sheets.LineBorder('red', lineStyle); sheet.getRange(2, 2, 2, 5).setBorder(lineBorder, { outline: true }, sheetArea); lineBorder = new GC.Spread.Sheets.LineBorder('green', lineStyle); sheet.getRange(5, 2, 2, 5).setBorder(lineBorder, { outline: true }, sheetArea); lineBorder = new GC.Spread.Sheets.LineBorder('blue', lineStyle); sheet.getRange(8, 1, 11, 6).setBorder(lineBorder, { outline: true }, sheetArea); }
<!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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/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></body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; border: 1px solid gray; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }