AGGREGATE Functions

The AGGREGATE function supports 19 aggregate calculation and function options.

<p>SpreadJS supports the AGGREGATE function to do aggregate operations. The syntax is same as Excel.</p> <pre><code class="hljs js language-js"> <span class="hljs-comment">//Returns the sum of the values contained in the range A1:C5.</span> =AGGREGATE(<span class="hljs-number">9</span>, <span class="hljs-number">4</span>, <span class="hljs-attr">A1</span>:C5) <span class="hljs-comment">//Returns the max value in the range A1:C5.</span> =AGGREGATE(<span class="hljs-number">4</span>, <span class="hljs-number">3</span>, <span class="hljs-attr">A1</span>:C5) <span class="hljs-comment">//Returns the second largest value in the range A1:C5.</span> =AGGREGATE(<span class="hljs-number">14</span>, <span class="hljs-number">3</span>, <span class="hljs-attr">A1</span>:C5, <span class="hljs-number">2</span>) </code></pre> <p><strong>The first argument is the function number (1-19) which indicate the function used.</strong></p> <table border="1" cellspacing="0" class='table-style'> <tr><th class = 'table-th'>Function number</th><th class = 'table-th'>Function</th></tr> <tr><td class = 'table-td'>1</td><td class = 'table-td'>AVERAGE</td></tr> <tr><td class = 'table-td'>2</td><td class = 'table-td'>COUNT</td></tr> <tr><td class = 'table-td'>3</td><td class = 'table-td'>COUNTA</td></tr> <tr><td class = 'table-td'>4</td><td class = 'table-td'>MAX</td></tr> <tr><td class = 'table-td'>5</td><td class = 'table-td'>MIN</td></tr> <tr><td class = 'table-td'>6</td><td class = 'table-td'>PRODUCT</td></tr> <tr><td class = 'table-td'>7</td><td class = 'table-td'>STDEV.S</td></tr> <tr><td class = 'table-td'>8</td><td class = 'table-td'>STDEV.P</td></tr> <tr><td class = 'table-td'>9</td><td class = 'table-td'>SUM</td></tr> <tr><td class = 'table-td'>10</td><td class = 'table-td'>VAR.S</td></tr> <tr><td class = 'table-td'>11</td><td class = 'table-td'>VAR.P</td></tr> <tr><td class = 'table-td'>12</td><td class = 'table-td'>MEDIAN</td></tr> <tr><td class = 'table-td'>13</td><td class = 'table-td'>MODE.SNGL</td></tr> <tr><td class = 'table-td'>14</td><td class = 'table-td'>LARGE</td></tr> <tr><td class = 'table-td'>15</td><td class = 'table-td'>SMALL</td></tr> <tr><td class = 'table-td'>16</td><td class = 'table-td'>PERCENTILE.INC</td></tr> <tr><td class = 'table-td'>17</td><td class = 'table-td'>QUARTILE.INC</td></tr> <tr><td class = 'table-td'>18</td><td class = 'table-td'>APERCENTILE.EXC</td></tr> <tr><td class = 'table-td'>19</td><td class = 'table-td'>QUARTILE.EXC</td></tr> </table> <p><strong>The second argument specify the options to ignore values in the calculation.</strong></p> <table border="1" cellspacing="0" class='table-style'> <tr><th class = 'table-th'>Option</th><th class = 'table-th'>Behavior</th></tr> <tr><td class = 'table-td'>0 or omitted</td><td class = 'table-td'>Ignore nested SUBTOTAL and AGGREGATE functions</td></tr> <tr><td class = 'table-td'>1</td><td class = 'table-td'>Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions</td></tr> <tr><td class = 'table-td'>2</td><td class = 'table-td'>Ignore error values, nested SUBTOTAL and AGGREGATE functions</td></tr> <tr><td class = 'table-td'>3</td><td class = 'table-td'>Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions</td></tr> <tr><td class = 'table-td'>4</td><td class = 'table-td'>Ignore nothing</td></tr> <tr><td class = 'table-td'>5</td><td class = 'table-td'>Ignore hidden rows</td></tr> <tr><td class = 'table-td'>6</td><td class = 'table-td'>Ignore error values</td></tr> <tr><td class = 'table-td'>7</td><td class = 'table-td'>Ignore hidden rows and error values</td></tr> </table> <p>With function number 1-13, the rest arguments is reference to a range of cells.</p> <p>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.</p> <pre><code class="hljs js language-js"> =AGGREGATE(function_num, options, ref1, [ref2], ...) <span class="hljs-comment">// function_num 1-13</span> =AGGREGATE(function_num, options, array, k) <span class="hljs-comment">// function_num 14-19</span> </code></pre>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { var salesData = [ ["SalesPers", "Birth", "Region", "SaleAmt", "ComPct", "ComAmt"], ["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 ComAmt"], ["", "MAX ComAmt"], ["", ""], ["Ignore error", "SUM ComAmt"], ["", "MAX ComAmt"]]); 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)"); initStyle(sheet); sheet.resumePaint(); }; function initStyle(sheet) { sheet.defaults.colWidth = 100; sheet.setColumnWidth(2, 140); sheet.getRange(9, 2, 10, 1).formatter("yyyy/mm/dd"); 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/@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></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; }