A Dynamic Array formula is any formula that has the potential to return multiple results. There are several new functions in Excel for Office 365 that have this ability, including Sort, Filter, and Unique. In this post we will have a look at Sort and Filter.
The first thing to keep in mind is that the results from these Dynamic Array formulas will “spill” into adjacent cells; therefore, we need to plan for the possible spill range by allowing ample space in adjacent cells. This spilling is by design; and, in the event the spill range is not large enough for the results, Excel returns a #SPILL! error.
The second item to consider is that references used in Dynamic Arrays between workbooks need both workbooks to be open; Excel returns a #REF! if they are not.
The first thing to keep in mind is that the results from these Dynamic Array formulas will “spill” into adjacent cells; therefore, we need to plan for the possible spill range by allowing ample space in adjacent cells. This spilling is by design; and, in the event the spill range is not large enough for the results, Excel returns a #SPILL! error.
The second item to consider is that references used in Dynamic Arrays between workbooks need both workbooks to be open; Excel returns a #REF! if they are not.
Spill
To understand better what spill means and how dynamic arrays work, let’s start with the data below and create a formula to summarize the information.
The data is in a table named Payments. In cell G5, enter the formula =payments and press Enter. The data is now in G5:J22 using this single formula. While in the data range, you will notice an outline around the data indicating that it is part of a dynamic array. The formula is only in cell G5 and, therefore, all other cells in the array will show the formula as a light gray in the formula bar as shown below.
As shown below, in the event of other data residing in the range used by the dynamic array formula – for example, cells M12:O12 – completing the formula will result in the #SPILL! error. Keeping cell L5 highlighted, Excel will show where the data will spill, providing clear guidance on which data is causing the error. To resolve the problem, either move the dynamic array formula cell or clear the data in the spill area. Additionally, as shown below, Excel provides an error-checking tool and a quick way to Select Obstructing Cells if you want to move them outside the spill range. Once you clear the obstructing cells from the spill range, the dynamic array formula should complete, unless one of the following four issues arises.
- The spill range includes merged cells;
- The dynamic array formula is inside of a table;
- The system is out of memory; and
- The sequence is volatile, such as with a RANDBETWEEN sequence.
Spill Range Reference
With only the one cell in the spill range containing the formula, the question becomes how to refer to all of the data in the range. The answer is to use the spill range operator which is the # symbol. In the previous example, to refer to the entire spill range, the reference would be =G5#. This does have a limitation in that it is not supported when linking to an external workbook.
Sort
The process to sort by formula before Dynamic Arrays was very complicated and, therefore, seldom used. With the Dynamic Array function SORT, the process is simply to use the formula below.
=SORT(array,[sort_index], [sort_order],[by_col
Using similar sample data, as in the previous example, in cell G2, input the formula =SORT(sortdata,3,1). The data is in a table named sortdata; the number 3 is the column to use as the sort, and the number 1 is for ascending order.
If you want to sort by more than one index, you can use an array constant. For example, to sort the data by Account ascending and Amount descending, the formula would be =SORT(SortData,{3;4},{1;-1}). The results of these formula-based sorts are shown below.
If you want to sort by more than one index, you can use an array constant. For example, to sort the data by Account ascending and Amount descending, the formula would be =SORT(SortData,{3;4},{1;-1}). The results of these formula-based sorts are shown below.
SortBy
The SORTBY function allows you to sort the contents of an array based on the values in a corresponding array. The syntax for the function is =SORTBY(array, by_array1, sort_order1,….). For example, to do the same sort as we did with the array constant, the formula would be as follows.
=SORTBY(SortData,SortData[Account],1,SortData[Amount],-1)
Filter
The next dynamic array function is FILTER, which allows filtering of data into a new array. The syntax for the function is =FILTER(array, include, [if_empty]). To have more than one condition, use the plus sign between them for either and the multiply sign between them for both.
As an example, in Figure 18, we want to retrieve all rows from the table named FilterData that have the item stamps. The formula would be as shown below.
As an example, in Figure 18, we want to retrieve all rows from the table named FilterData that have the item stamps. The formula would be as shown below.
=FILTER(FilterData,FilterData[Item]=I1,"Item not found")
To retrieve the information related to stamps in the sales department the formula is as follows.
=FILTER(FilterData,(FilterData[Item]=I1)*(FilterData[Department]=I2),""
Dynamic Arrays at the time of creating this post is only available in the Office 365 Monthly Channel (Targeted)
| dynamic_arrays.xlsx |
RSS Feed