| Syntax |
| =DROP(array,rows,[columns]) |
| Purpose / Description |
| Returns a reference to a range whose dimensions are reduced by the given number of rows and/or columns, or an array of values from an array whose dimensions are reduced by the given number of rows and/or columns. |
| Arguments |
| Name |
Type |
Description |
| array |
reference or array of any |
A reference or nested expression that returns a valid reference, a constant array of any values or a nested expression that returns an array of any values. |
| rows |
number |
A reference to a range, a constant or nested expression that provides the number of rows to be dropped. If rows is positive, the given number of rows is dropped from the top of the range or array. If rows is negative, the given number of rows is dropped from the bottom f the range or array. If the value is zero or omitted, no rows are dropped. |
| [columns] |
number |
A reference to a range, a constant or nested expression that provides the number of columns to be dropped. If columns is positive, the given number of columns is dropped from the left of the range or array. If columns is negative, the given number of columns is dropped from the right of the range or array. If the value is zero or omitted, no columns are dropped. |
| Return Values |
| Type |
Specific Value |
Description |
| reference |
|
If the array argument is a reference, the function returns a reference with reduced dimensions. |
| array of any |
|
If the array argument is an array of values, the function returns an array with reduced dimensions. |
| error |
#CALC! |
If the absolute value passed to rows or columns is greater than the number of rows or columns respectively in the range or array referred to by the array argument. |
| error |
#NUM! |
If the array referenced by the array argument is too large. |
| error |
|
Any errors referenced by arguments are returned due to propagation of error values. |
| Notes / Comments / Advice / Warnings |
| Comment: |
This is an extremely useful addition to Excel, along with the TAKE, EXPAND, CHOOSEROWS and CHOOSECOLS functions, that allow you to manipulate the contents of ranges and arrays for advanced analytical calculations in dynamic array formulas. |
| Examples: |
Open / download sample workbook: DROP, EXPAND, TAKE.xlsx |
| |