|
SLICES Function
Returns an array composed of one or more slices of columns and rows from a Range or Array or Spill reference. Values are returned from the intersections of the given rows and columns.
If the rows and columns define a single contiguous area then SLICES will return a reference, otherwise SLICES will return an array with the non-contiguous areas stacked together into a single array.
Slices can use alphabetic labels to lookup both rows and columns and will return all the results found that match both conditions.
SLICES is a multi-threaded, non-volatile array function.
SLICES Syntax
SLICES (Range_Array, [SliceRows], [ SliceColumns])
Range_Array
Array, Table, Range or Spill reference to take the slices from.
SliceRows (Optional, default 0)
Parameter giving the rows to return. A value of zero (default if SliceRows is omitted) means all the rows.
Rows can be derived from SliceRows in 3 different ways:
1.If Range_Array is a Range then SliceRows can be one or more ranges within Range_Array. Multiple disjoint ranges must be enclosed in ( ) – for example (A1:A2,A4) results in rows 1,2 and 4.
2.Alphabetic labels are used as lookup values in the first column of Range_Array to find the rows. These labels can be supplied in an array or a non-interseting range.
3.Relative row numbers are used directly.
These row numbers can be supplied in an array or non-intersecting range.
For example {2,4} gets the second and fourth row of the Range_Array.
Negative numbers work from the bottom upwards so {-1,-2} gets the last two rows of the Range_Array.
Zero acts as an include operator.
{1,0,5} results in rows 1 through 5 inclusive
{2,0,-1} results in row 2 through to the second-to-last row.
{1,5} results in row 1 and row 5 only
{5,0} results in row 5 and all rows after row 5
{0,5} results in the first 5 rows
SliceColumns (Optional, default 0)
Parameter giving the columns to return. A value of zero (default if SliceColumns is omitted) means all the columns.
Columns can be derived from SliceColumns in 3 different ways:
1.If Range_Array is a Range then SliceColumns can be one or more ranges within Range_Array. Multiple disjoint ranges must be enclosed in ( ) – for example (A1:B1,D1) results in columns 1,2 and 4.
2.Alphabetic labels are used as lookup values in the first row of Range_Array to find the columns. These labels can be supplied in an array or a range.
3.Relative column numbers are used directly. These column numbers can be supplied in an array or range. For example {2,4} gets the second and fourth column of the Range_Array. Negative numbers work from right to left so {-1, -2} gets the last two columns of the Range_Array.
Zero acts as an include operator for columns in the same way as for rows.
SLICES Examples:
SLICES can use alphabetic labels as row and column lookups.
In this example SLICES looks in the Monthly table to find all rows that have a row label of “Pear” and the columns that have a header label of “Month” and “Revenue” and then returns an array of all the data from the intersections of these rows and columns.
=SLICES(Monthly[#All],"Pear",{"Month","Revenue"})
You could also use these formulas to get the same results.
=SLICES(Monthly[#All],"Pear",(B4,E4)) – range reference to the column labels
=SLICES(Monthly[#All],"Pear",{2,5}) - array of column numbers
=SLICES(Monthly[#All],{3,7,11,15,19},{2,5}) - arrays of row and column numbers
=SLICES(Monthly[#All],"Pear",(B3:B6,E3:E7)) - using ranges that intersect the header labels
.png)
Given this range of data
.png)
SLICES(A1:C4,{2,3},{1,3}) will return this array
.png)
SLICES(A1:C4,0,{2,3}) will return this array
.png)
To calculate Yield (total revenue /total sales) from floating totals use -1 to access the last row of the dynamic array:
=SLICES(K9#,-1,6)/SLICES(K9#,-1,4)
.png)
|
