Overview

The dynamic array is used to replace array formula. Any formula that has the potential to return multiple results can be referred to as a dynamic array formula. Formulas that are currently returning multiple results, and are successfully spilling, can be referred to as spilled array formulas.

In the following example, the value from C3 spills over to the adjacent cells. The value in C9 however will display the #SPILL! error as there is an existing value in the range preventing the spill.

Users can enable dynamic array functions in one of two ways: Spill means that a formula has returned multiple values and those values ‘spill’ into the neighboring cells. Dynamic Array has seven Functions: FILTER function: Filters a range of data based on criteria you defined. RANDARRAY function: Returns an array of random numbers. SEQUENCE function: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4. SORT function: Sorts the contents of a range or array. SORTBY function: Sorts the contents of a range or array based on the values in a corresponding range or array. UNIQUE function: Returns a list of unique values in a list or range. The implicit intersection operator @: Returns a single value using logic known as implicit intersection. The Spill Range Operator # can reference the entire spill range: When you're dealing with spilled array functions, it's possible to reference the entire spill range by placing the spilled range operator (#) after the range. #SPILL errors are returned when a formula returns multiple values that cannot display the results in the spreadsheet for any of the following reasons: Spill range for a spilled array formula isn't blank. Unable to determine the size of the spilled array because it's volatile, and resizes between calculation passes. The spilled array formula you're attempting to enter will extend beyond the worksheet's range. Spilled array formulas in tables. The spilled array formula you're attempting to enter has caused to run out of memory. Spilled array formulas spill into merged cells. The formula cannot recognize or cannot reconcile. #CALC! errors are retuned when any of the following results are true: Calculate an array within an array. Arrays can only contain numbers, strings, errors, Booleans, or linked data types. Range references aren't supported. Return an empty set. Calculation engine encounters an unspecified calculation error with an array.
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss")); initSpread(spread); }; function initSpread(spread) { spread.options.allowDynamicArray = true; var sheet = spread.getActiveSheet(); spread.suspendPaint(); spread.suspendCalcService(); sheet.setValue(1, 2, "=SEQUENCE(3,3)"); sheet.setFormula(2, 2, 'SEQUENCE(3,3)'); sheet.setValue(7, 2, "=SEQUENCE(3,3)"); sheet.setFormula(8, 2, 'SEQUENCE(3,3)'); sheet.setValue(9, 3, 3); bindEvent(spread, sheet); spread.resumeCalcService(); spread.resumePaint(); } function bindEvent (spread, sheet) { _getElementById("allowDynamicArray").addEventListener('change', function () { spread.options.allowDynamicArray = this.checked; sheet && sheet.recalcAll(true); }); } function _getElementById(id) { return document.getElementById(id); }
<!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 class="options-container"> <div class="option-row"> <input style="width: 20px;float: left;" type="checkbox" id="allowDynamicArray" checked="checked"/> <label for="allowDynamicArray">Allow DynamicArray</label> </div> </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; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } input { margin-bottom: 5px; padding: 2px 4px; width: 100%; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }